在sql中找到重复的4

debugcn 发表于 Dev

noobprogrammer1987

我尝试了两种尝试在 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 |

这是我的大连接。

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;

我想将它与下面为我所做的 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

医学博士苏曼卡比尔 |

SQL 演示

用这个：

注意：如果您只想根据列获取重复记录，这将起作用player。

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

如果要根据所有四列获取重复记录，可以使用以下查询：

SQL 演示

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。