在sql中找到重复计数4

I tried two methods of trying to find a duplicate count of 4 in the player column in sql. They both produce the same results. I specifically want duplicates of the player column. When I look through my column tab of player none of them have a single duplicate. What am I doing wrong or not understanding?

我尝试了两种尝试在sql的播放器列中找到重复计数4的方法。它们都产生相同的结果。我特别想要玩家专栏的重复。当我查看播放器的列标签时，没有一个重复。我做错了什么或不理解？

SELECT tournament,player,prize_money,position FROM earnings where position = 1 group by player having count(player) > 3;
+------------+--------+-------------+----------+
| tournament | player | prize_money | position |
+------------+--------+-------------+----------+
|        797 |      1 |       50000 |        1 |
|       4969 |      2 |         100 |        1 |
|         79 |      3 |       45231 |        1 |
|         74 |      4 |         921 |        1 |
|        799 |      5 |        5000 |        1 |
|       1801 |      6 |       10663 |        1 |
|       3082 |      8 |         652 |        1 |
|       2157 |     10 |       30000 |        1 |
|       1366 |     11 |       30000 |        1 |
|        322 |     12 |        1375 |        1 |
|        167 |     13 |       42690 |        1 |


SELECT * FROM earnings where position = 1 group by player having count(*) > 3;
+------------+--------+-------------+----------+
| tournament | player | prize_money | position |
+------------+--------+-------------+----------+
|        797 |      1 |       50000 |        1 |
|       4969 |      2 |         100 |        1 |
|         79 |      3 |       45231 |        1 |
|         74 |      4 |         921 |        1 |
|        799 |      5 |        5000 |        1 |
|       1801 |      6 |       10663 |        1 |
|       3082 |      8 |         652 |        1 |
|       2157 |     10 |       30000 |        1 |
|       1366 |     11 |       30000 |        1 |
|        322 |     12 |        1375 |        1 |
|        167 |     13 |       42690 |        1 |

Here is the big join I have.

这是我的大联合。

SELECT players.player_id, players.tag, players.game_race,
       earnings.tournament, earnings.player, earnings.position,
       tournaments.tournament_id, tournaments.region
  FROM players
  JOIN earnings ON players.player_id = earnings.player 
  JOIN tournaments ON earnings.tournament = tournaments.tournament_id
  where position = 1
ORDER BY players.player_id;

I would like to combine it with Md. Suman Kabir answer below that was done for me.

我想将它与Md.Suman Kabir的答案结合起来，这是为我做的。

SELECT tournament, earnings.player, prize_money, position 
FROM earnings 
    join (
        SELECT player FROM earnings 
        where position = 1 group by player having count(player) > 3) 
    as DupPlayer
on earnings.player=DupPlayer.player
where position = 1

2 个解决方案

#1

SQL DEMO

SQL DEMO

Use this :

用这个 ：

Note: This will work if you want to get duplicate records based on the column player only.

注意：如果您想仅根据列播放器获取重复记录，则此方法有效。

SELECT tournament, earnings.player, prize_money, position 
FROM earnings 
    join (
        SELECT player FROM earnings 
        where position = 1 group by player having count(player) > 3) 
    as DupPlayer
on earnings.player=DupPlayer.player
where position = 1

If you want to get the duplicate records based on all the four columns, you can use this query :

如果要基于所有四列获取重复记录，可以使用此查询：

SQL DEMO

SQL DEMO

SELECT tournament, player, prize_money, position 
FROM earnings 
where position = 1 
group by tournament, player, prize_money, position 
having count(player) > 3;

#2

Try this answer:

试试这个答案：

SELECT tournament,player,prize_money,position 
FROM earnings 
where position = 1 AND player in(
    SELECT player
    FROM earnings 
    where position = 1 group by player having count(player) > 3
    )