sqlite - 在SQLite 3中合并行

我有以下SQLite 3表架构：

CREATE TABLE rolestats (player TEXT, role TEXT, teamwins SMALLINT, individualwins SMALLINT, totalgames SMALLINT, UNIQUE(player, role));

一些样本数据：

sqlite> SELECT * FROM rolestats WHERE player LIKE "%rika%" LIMIT 10;
rika|jester|0|3|4
rika|guardian angel|0|0|1
rika|clone|0|1|1
rika|village elder|1|0|1
rika|village drunk|7|5|10
rika|cultist|5|0|15
rika|detective|3|2|4
rika|wolf cub|7|3|11
Rika|wolf|0|0|1
Rika|shaman|2|1|2

如您所见，名称的大小写不同。它使用IRC帐户名，但是可以更改。在某些情况下，它们之间的差异不仅限于情况。对于这种特殊情况，不区分大小写是计划好的，但我们还没有计划。

这是我想出的：

SELECT role, SUM(teamwins), SUM(individualwins), SUM(totalgames) FROM rolestats WHERE player LIKE "%rika%" GROUP BY role;

我之前已经做过一次，但是现在我不知道如何将查询结果插入player = "rika"并删除其他玩家名称变体。

最佳答案

评论后，关于名称中包含的常见模式，这将有所帮助：

INSERT OR REPLACE INTO rolestats
SELECT  'rika',
        role,
        SUM(teamwins) as teamwins,
        SUM(individualwins) as individualwins,
        SUM(totalgames) as totalgames
FROM rolestats WHERE player LIKE '%rika%' group by role;

DELETE FROM rolestats WHERE player != 'rika' AND player LIKE '%rika%';

我不确定您的方法是否正确，这意味着如果您拥有“ MoreRika”和“ rika”之类的用户名，那么它们都将被汇总到您的统计信息中（因为是“％”）。

我认为您想在转换之前规范化用户名（例如，将其转换为小写或大写）。不幸的是，SQLite不支持子查询的更新，但是可以使用REPLACE作为替代。

我认为您要解决的问题可以这样解决：

-- we convert all non-normalized names to their normalized format
INSERT OR REPLACE INTO rolestats
SELECT  LOWER(player),
        role,
        SUM(teamwins) as teamwins,
        SUM(individualwins) as individualwins,
        SUM(totalgames) as totalgames
FROM rolestats group by LOWER(player), role;

-- now we can delete all non-normalized player names
DELETE from rolestats WHERE player NOT IN (
  SELECT LOWER(rs.player) FROM rolestats rs
);

查看此SQL提琴：http://sqlfiddle.com/#!5/3d721/2

请注意，此查询将转换所有用户，如果您只想转换特定用户，则必须添加WHERE子句：

INSERT OR REPLACE INTO rolestats
SELECT  LOWER(player),
        role,
        SUM(teamwins) as teamwins,
        SUM(individualwins) as individualwins,
        SUM(totalgames) as totalgames
FROM rolestats WHERE LOWER(player) = 'rika' group by role;

DELETE from rolestats WHERE LOWER(player) = 'rika' AND player != 'rika';

像在此SQL Fiddle中一样：http://sqlfiddle.com/#!5/3d721/8