SQL:如何合并不区分大小写的重复项

Question

在将记录合并为一条记录时删除重复项的最佳方法是什么？

我有这样一种情况，表格会像这样跟踪玩家姓名和他们的记录:

stats
-------------------------------
nick     totalgames     wins   ...
John     100            40
john     200            97
Whistle  50             47
wHiStLe  75             72
...

我需要合并 nick 重复的行(忽略大小写时)并将记录合并为一个，如下所示:

    stats
    -------------------------------
    nick     totalgames     wins   ...
    john     300            137
    whistle  125            119
    ...

我在 Postgres 中这样做。执行此操作的最佳方法是什么？

我知道我可以通过这样做获得存在重复的名称:

select lower(nick) as nick, totalgames, count(*) 
from stats 
group by lower(nick), totalgames
having count(*) > 1;

我想到了这样的事情:

update stats
set totalgames = totalgames + s.totalgames
from (that query up there) s
where lower(nick) = s.nick

除此之外无法正常工作。而且我似乎仍然无法删除包含重复名称的其他重复行。我能做些什么？有什么建议吗？

Answer 1

SQL Fiddle

这是您的更新:

 UPDATE stats
 SET totalgames = x.games, wins = x.wins
 FROM (SELECT LOWER(nick) AS nick, SUM(totalgames) AS games, SUM(wins) AS wins
     FROM stats
      GROUP BY LOWER(nick) ) AS x
 WHERE LOWER(stats.nick) = x.nick;

下面是去除重复行的删除:

 DELETE FROM stats USING stats s2
 WHERE lower(stats.nick) = lower(s2.nick) AND stats.nick < s2.nick;

(请注意，'update...from' 和 'delete...using' 语法是 Postgres 特有的，并且是从 this answer 和 this answer 中无耻地偷来的。)

您可能还想运行此命令以将所有名称小写:

 UPDATE STATS SET nick = lower(nick);

Aaaand 在 'nick' 的小写版本上添加一个唯一索引(或向该列添加一个约束以禁止非小写值):

CREATE UNIQUE INDEX ON stats (LOWER(nick));