我想查询一列，并在第一个player_id列中显示计数大于9的所有记录。只要第一列中的玩家玩过9次以上的玩家。对手可以是任何人，只要大于9次即可。
这是我首先尝试的。
SELECT p1.player_id, p2.player_id,
m.date, m.scoreA, m.scoreB, mem1.player, mem2.player, team1.name, team2.name
FROM matches m
LEFT
JOIN `members` mem1
ON mem1.player = m.playerA
AND mem1.start_date <= m.date
AND ( mem1.end_date > m.date OR mem1.end_date IS NULL )
LEFT
JOIN `members` mem2
ON mem2.player = m.playerB
AND mem2.start_date <= m.date
AND ( mem2.end_date > m.date OR mem2.end_date IS NULL )
JOIN players p1 ON p1.player_id = m.playerA
JOIN players p2 ON p2.player_id = m.playerB
JOIN teams team1 ON team1.team_id = mem1.team
JOIN teams team2 ON team2.team_id = mem2.team
where team2.name = "Prime" and team1.name = "FXOpen e-Sports Korea"
Group By p1.player_id
having count(p1.player_id) > 9
order by p1.player_id, p2.player_id, m.date;
这只给了两个记录。两条计数大于9的记录。我想在第一个player_id列中查看计数大于9的所有记录。只要第一列中的玩家玩过9次以上的玩家。对手可以是任何人，只要大于9次即可。
然后我尝试了这个。我认为在查询中执行查询会起作用。
SELECT p1.player_id, p2.player_id,
m.date, m.scoreA, m.scoreB, mem1.player, mem2.player, team1.name, team2.name
FROM matches m
LEFT
JOIN `members` mem1
ON mem1.player = m.playerA
AND mem1.start_date <= m.date
AND ( mem1.end_date > m.date OR mem1.end_date IS NULL )
LEFT
JOIN `members` mem2
ON mem2.player = m.playerB
AND mem2.start_date <= m.date
AND ( mem2.end_date > m.date OR mem2.end_date IS NULL )
JOIN players p1 ON p1.player_id = m.playerA
JOIN players p2 ON p2.player_id = m.playerB
JOIN teams team1 ON team1.team_id = mem1.team
JOIN teams team2 ON team2.team_id = mem2.team
where team2.name = "Prime" and team1.name = "FXOpen e-Sports Korea" and
p1.player_id in( select p1.player_id from players p1
Group By p1.player_id
having count(p1.player_id) > 9
)
order by p1.player_id, p2.player_id, m.date;
不幸的是，这只是一个空白。
这就是我的数据不计其数的样子。
+-----------+-----------+------------+--------+--------+--------+--------+-----------------------+-------+
| player_id | player_id | date | scoreA | scoreB | player | player | name | name |
+-----------+-----------+------------+--------+--------+--------+--------+-----------------------+-------+
| 1 | 2 | 2012-01-18 | 1 | 0 | 1 | 2 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2011-08-22 | 1 | 3 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-01-02 | 1 | 2 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-01-18 | 0 | 1 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-06-22 | 2 | 1 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-11-09 | 1 | 0 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 47 | 2012-11-09 | 1 | 0 | 1 | 47 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2012-03-18 | 2 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2012-11-09 | 1 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2013-05-28 | 1 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 157 | 2012-11-09 | 1 | 0 | 1 | 157 | FXOpen e-Sports Korea | Prime |
| 1 | 225 | 2011-08-12 | 2 | 0 | 1 | 225 | FXOpen e-Sports Korea | Prime |
| 21 | 2 | 2013-06-03 | 2 | 1 | 21 | 2 | FXOpen e-Sports Korea | Prime |
| 21 | 49 | 2013-06-03 | 3 | 1 | 21 | 49 | FXOpen e-Sports Korea | Prime |
| 44 | 2 | 2012-01-18 | 0 | 1 | 44 | 2 | FXOpen e-Sports Korea | Prime |
| 44 | 2 | 2012-03-18 | 1 | 2 | 44 | 2 | FXOpen e-Sports Korea | Prime |
| 44 | 2 | 2012-11-09 | 0 | 1 | 44 | 2 | FXOpen e-Sports Korea | Prime |
| 44 | 2 | 2013-06-05 | 0 | 1 | 44 | 2 | FXOpen e-Sports Korea | Prime |
预期成绩
+-----------+-----------+------------+--------+--------+--------+--------+-----------------------+-------+
| player_id | player_id | date | scoreA | scoreB | player | player | name | name |
+-----------+-----------+------------+--------+--------+--------+--------+-----------------------+-------+
| 1 | 2 | 2012-01-18 | 1 | 0 | 1 | 2 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2011-08-22 | 1 | 3 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-01-02 | 1 | 2 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-01-18 | 0 | 1 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-06-22 | 2 | 1 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 14 | 2012-11-09 | 1 | 0 | 1 | 14 | FXOpen e-Sports Korea | Prime |
| 1 | 47 | 2012-11-09 | 1 | 0 | 1 | 47 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2012-03-18 | 2 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2012-11-09 | 1 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 49 | 2013-05-28 | 1 | 0 | 1 | 49 | FXOpen e-Sports Korea | Prime |
| 1 | 157 | 2012-11-09 | 1 | 0 | 1 | 157 | FXOpen e-Sports Korea | Prime |
| 1 | 225 | 2011-08-12 | 2 | 0 | 1 | 225 | FXOpen e-Sports Korea | Prime |
玩家表
+-----------+-----------+-----------------+-------------+------------+-----------+
| player_id | tag | real_name | nationality | birthday | game_race |
+-----------+-----------+-----------------+-------------+------------+-----------+
| 1 | Leenock | Dong Nyoung Lee | KR | 1995-04-01 | Z |
| 2 | Creator | Hyun Woo Jang | KR | 1996-08-19 | P |
| 3 | Life | Seung Hyun Lee | KR | 1997-01-11 | Z |
| 4 | DongRaeGu | Soo Ho Park | KR | 1991-06-03 | Z |
| 5 | PartinG | Lee Sak Won | KR | 1994-08-24 | P |
| 6 | TaeJa | Young Suh Yoon | KR | 1995-01-01 | T |
| 7 | Rain | Yoon Jong Jung | KR | 1992-08-14 | P |
| 8 | viOLet | Dong Hwan Kim | KR | 1990-12-05 | Z |
| 9 | Sniper | Tae Hoon Kwon | KR | 1995-01-22 | Z |
| 10 | Stephano | Ilyes Satouri | FR | 1993-03-12 | Z |
+-----------+-----------+-----------------+-------------+------------+-----------+
比赛表
+----------+------------+------------+---------+---------+--------+--------+---------+
| match_id | date | tournament | playerA | playerB | scoreA | scoreB | offline |
+----------+------------+------------+---------+---------+--------+--------+---------+
| 1 | 2012-12-04 | 799 | 4 | 55 | 1 | 3 | 0 |
| 2 | 2012-12-03 | 11921 | 2 | 41 | 2 | 0 | 0 |
| 3 | 2012-12-03 | 11921 | 21 | 41 | 0 | 2 | 0 |
| 4 | 2012-12-03 | 11921 | 3 | 2 | 2 | 1 | 0 |
| 5 | 2012-12-03 | 11921 | 41 | 2 | 1 | 2 | 0 |
| 6 | 2012-12-03 | 11921 | 21 | 3 | 1 | 2 | 0 |
| 7 | 2012-12-03 | 11924 | 1 | 8 | 2 | 1 | 1 |
| 8 | 2012-12-03 | 11924 | 1 | 8 | 2 | 3 | 1 |
| 9 | 2012-12-03 | 11924 | 8 | 19 | 3 | 2 | 1 |
| 10 | 2012-12-03 | 11924 | 19 | 12 | 2 | 1 | 1 |
+----------+------------+------------+---------+---------+--------+--------+---------+
会员表
+--------+------+------------+------------+
| player | team | start_date | end_date |
+--------+------+------------+------------+
| 1 | 88 | 2011-02-23 | 2011-07-18 |
| 1 | 4 | 2011-07-18 | NULL |
| 2 | 3 | 2011-01-12 | NULL |
| 3 | 65 | 2010-10-19 | 2012-07-13 |
| 3 | 1 | 2012-07-13 | NULL |
| 4 | 7 | 2011-02-20 | NULL |
| 5 | 88 | 2010-10-29 | 2011-07-18 |
| 5 | 1 | 2012-10-21 | 2013-01-03 |
| 5 | 12 | 2013-01-14 | NULL |
| 6 | 65 | 2011-03-01 | 2011-05-01 |
+--------+------+------------+------------+
团队表
+---------+-------------------------------+------------+------------+
| team_id | name | founded | disbanded |
+---------+-------------------------------+------------+------------+
| 1 | StarTale | 2010-09-15 | NULL |
| 2 | Incredible Miracle | 2010-10-01 | NULL |
| 3 | Prime | 2010-10-13 | NULL |
| 4 | FXOpen e-Sports Korea | 2011-07-18 | NULL |
| 5 | FXOpen e-Sports Europe | 2012-03-03 | 2013-04-09 |
| 6 | FXOpen e-Sports North America | 2012-10-28 | 2013-08-07 |
| 7 | MVP | 2011-02-20 | NULL |
| 8 | New Star HoSeo | 2011-02-26 | 2013-07-31 |
| 9 | CJ Entus | 2000-05-09 | NULL |
| 10 | KT Rolster | 2000-04-04 | NULL |
+---------+-------------------------------+------------+------------+
最佳答案
使用inner join和子查询在第一个playerA列中计数大于9
SELECT p1.player_id, p1.real_name, p2.player_id, p2.real_name,
m.scoreA, m.scoreB, mem1.player, mem2.player
FROM matches m
INNER JOIN (
select playerA
from matches
group by playerA
having count(*) > 9
) c9 on m.playerA = c9.playerA
JOIN players p1 ON p1.player_id = m.playerA
JOIN members mem1 ON mem1.player = p1.player_id
JOIN players p2 ON p2.player_id = m.playerB
JOIN members mem2 ON mem2.player = p2.player_id
;
或使用
IN()和子查询SELECT p1.player_id, p1.real_name, p2.player_id, p2.real_name,
m.scoreA, m.scoreB, mem1.player, mem2.player
FROM matches m
JOIN players p1 ON p1.player_id = m.playerA
JOIN members mem1 ON mem1.player = p1.player_id
JOIN players p2 ON p2.player_id = m.playerB
JOIN members mem2 ON mem2.player = p2.player_id
WHERE m.playerA IN (
select playerA
from matches
group by playerA
having count(*) > 9
)
;
关于mysql - sql查询一列并显示所有记录的计数大于，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47529454/