在SQLite中选择“第一个”(由ORDER BY确定)行FROM近重复行(由GRO

示例表worker,Columns：

* ID: integer,auto-incrementing
* ColA: integer
* ColB: varchar(20)
* UserType: varchar(20)
* LoadMe: Boolean

(是的,SQLite的数据类型是名义上的)

我的数据表,Workers,一开始看起来像：

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     0
2   1     b     Beta      0
3   2     a     Alpha     0
4   2     a     Beta      0
5   2     b     Delta     0
6   2     b     Alpha     0
7   1     a     Delta     0
8   1     b     Epsilon   0 
9   1     c     Gamma     0
10  4     b     Delta     0
11  5     a     Alpha     0
12  5     a     Beta      0
13  5     b     Gamma     0
14  5     a     Alpha     0

我想在新工厂装载到卡车上,使所有在ColA和ColB之间具有独特组合的工人.对于那些复制品(双胞胎,三胞胎等,也许是通过Bokanovsky的过程),ColA和ColB的独特组合有多个工人,我想从每组重复中只选择一个.为了使问题更难,我想另外能够在某种形式的ORDER BY中基于UserType从每组重复中选择一个.我可能希望选择UserType为“Alpha”的第一个“复制”来处理一个非常聪明的问题,或者ORDER BY UserType DESC,我可以为最低级别的工人发出黑色长袍订单.

您可以看到ID 9,10和13具有ColA和ColB的唯一组合,并且最容易识别.然而,1-a,1-b,2-a,2-b和5-a组合在其中具有重复.

我目前的流程,目前为止：

0)每个人都有一个唯一的ID号.这是在出生时完成的.

1)将所有Worker设置为LoadMe = 1.

UPDATE Workers
SET LoadMe = 1

2)根据两列中的相似性(GROUP BY ColA,ColB)查找我的副本：

SELECT Wk1.*
FROM Workers AS Wk1
INNER JOIN (
    SELECT ColA,ColB
    FROM Workers
    GROUP BY ColA,ColB
    HAVING COUNT(*) > 1
) AS Wk2
ON Wk1.ColA = Wk2.ColA
AND Wk1.ColB = Wk2.ColB
ORDER BY ColA,ColB

3)将我的所有重复项设置为LoadMe = 0.

UPDATE Workers
SET LoadMe = 0
WHERE ID IN (
    SELECT Wk1.ID
    FROM Workers AS Wk1
    INNER JOIN (
        SELECT ColA,ColB
        FROM Workers
        GROUP BY ColA,ColB
        HAVING COUNT(*) > 1
    ) AS Wk2
    ON Wk1.ColA = Wk2.ColA
    AND Wk1.ColB = Wk2.ColB
)

4)对于GROUP BY,ORDERed BY UserType中的每组重复项,SELECT中只有一个(列表中的第一个)将LoadMe SET设置为1.

这个表看起来像：

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     1
2   1     b     Beta      1
3   2     a     Alpha     1
4   2     a     Beta      0
5   2     b     Delta     0
6   2     b     Alpha     1
7   1     a     Delta     0
8   1     b     Epsilon   0
9   1     c     Gamma     1
10  4     b     Delta     1
11  5     a     Alpha     1
12  5     a     Beta      0
13  5     b     Gamma     1
14  5     a     Alpha     0

按照ColA,ColB,UserType,然后ID进行排序,然后按GROUP BY列分类(为了清晰起见,最后间隔),相同的数据可能如下所示：

ID  ColA  ColB  UserType  LoadMe
1   1     a     Alpha     1
7   1     a     Delta     0

2   1     b     Beta      1
8   1     b     Epsilon   0

9   1     c     Gamma     1

3   2     a     Alpha     1
4   2     a     Beta      0

6   2     b     Alpha     1
5   2     b     Delta     0

10  4     b     Delta     1

11  5     a     Alpha     1
14  5     a     Alpha     0
12  5     a     Beta      0

13  5     b     Gamma     1

我对最后一步感到困惑,感觉像是一个Epsilon-minus半傻瓜.我以前一直把数据库中的副本拖到程序空间并在Python中工作,但是这种情况很少发生,我想更永久地解决这个问题.

解决方法

SELECT ColA,ColB FROM Workers GROUP BY ColA,ColB

现在,对于这些对中的每一对,您都希望找到最高优先级的记录.连接将不起作用,因为您最终会为每个唯一对结束多个记录,但子查询将起作用：

SELECT ColA,(SELECT id FROM Workers w1 
    WHERE w1.ColA=w2.ColA AND w1.ColB=w2.ColB 
    ORDER BY UserType LIMIT 1) AS id
FROM Workers w2 GROUP BY ColA,ColB;

您可以更改子查询中的ORDER BY子句以控制优先级. LIMIT 1确保每个子查询只有一条记录(否则sqlite将返回与WHERE子句匹配的最后一条记录,尽管我不确定这是否有保证).

此查询的结果是要使用ColA,id加载的记录列表.我可能会直接从那里工作并摆脱LoadMe,但如果你想保留它,你可以这样做：

BEGIN TRANSACTION;
UPDATE Workers SET LoadMe=0;
UPDATE Workers SET LoadMe=1
WHERE id IN (SELECT 
    (SELECT id FROM Workers w1 
    WHERE w1.ColA=w2.ColA AND w1.ColB=w2.ColB 
    ORDER BY UserType LIMIT 1) AS id
    FROM Workers w2 GROUP BY ColA,ColB);
COMMIT;

清除LoadMe标志,然后为我们上次查询返回的每条记录将其设置为1.事务保证这一切都作为一个步骤发生或失败,并且永远不会使您的LoadMe字段处于不一致状态.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!