示例表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字段处于不一致状态.
