时间戳

首页 > 解决方案 > 在 SQLite 中选择查询重复记录

sql - 在 SQLite 中选择查询重复记录

问题描述

这个问题是这个问题的概括。与其查找所有特定玩家与其他人对战的游戏，我希望能够找到相同玩家互相对战的所有游戏。

这是示例数据：

1,ChrisEveret,1
1,BillieJeanKing,1
1,RogerFederer,0
1,TomasMuster,0
2,RogerFederer,1
2,SallieMae,1
2,NovakDjokovic,0
2,JimCourier,0
3,ChrisEveret,0
3,BillieJeanKing,0
3,RogerFederer,1
3,TomasMuster,1

所需的输出是

1,ChrisEveret,1
1,BillieJeanKing,1
1,RogerFederer,0
1,TomasMuster,0
3,ChrisEveret,0
3,BillieJeanKing,0
3,RogerFederer,1
3,TomasMuster,1

实际数据只有大约两千行，所以性能不是问题。我想出了以下非常复杂且不精确的部分解决方案：

CREATE TABLE sets (gameid int, player text ,winloss int);
  .import data.csv sets
  select *  from sets where gameid in
   (select gameid from (select gameid,mo from
    (select gameid,mo,count(*) from
     (select gameid,group_concat(player) as mo from
      (select gameid,player from sets order by gameid,player)
     group by gameid)
   group by gameid)
  where mo in 
   (select mo from (select gameid,mo,count(*) from
    (select gameid,group_concat(player) as mo from
     (select gameid,player from sets order by gameid,player)
     group by gameid)
     group by mo
     having count(*)>1))));

这将返回相同的四个人一起参加的所有比赛，但不一定是球队相同的比赛。我不知道这个问题是否有不涉及使用 group_concat() 的解决方案。然而，这是我能够在这方面取得有限进展的唯一方法。我也不确定用于对 group_concat 结果排序以进行聚合的方法是否始终有效。

标签： sqlsqlite

解决方案

SQLite 不保证排序使用group_concat()-- 并且没有办法控制它。所以你必须使用更繁琐的方法。

您可以使用以下方法获得同一玩家的游戏对：

with s as (
       select s.*, count(*) over (partition by gameid) as num_players
       from sets s
      )
select s1.gameid, s2.gameid
from s s1 join
     s s2
     on s1.player = s2.player and s1.num_players = s2.num_players
group by s1.gameid = s2.gameid
having count(*) = max(s1.num_players);

然后，如果您想在每场比赛中获得玩家（或仅用于此目的group_concat()），则可以使用此逻辑。

编辑：

SQLite 版本 3.28 中引入了窗口函数。在早期版本中，试试这个：

with s as (
       select s.*, ss.num_players
       from sets s join
            (select gameid, count(*) as num_players
             from sets s
             group by gameid
            ) ss
            on ss.gameid = s.gameid
      )
select s1.gameid, s2.gameid
from s s1 join
     s s2
     on s1.player = s2.player and s1.num_players = s2.num_players
group by s1.gameid = s2.gameid
having count(*) = max(s1.num_players);

这是一个 db<>fiddle，它显示了所有具有相同玩家的游戏对（请注意，这包括每个团队本身）。

推荐阅读