GROUP BY和HAVING并非难事。该查询产生摘要:
SELECT date, account, amount, COUNT(1) AS num
FROM "transactions"
GROUP BY date, account, amount
HAVING num > 1
就像是:
date account amount num
2011-02-07 580416690 -6.4 2
2011-07-19 -50.0 2
2011-08-29 2445588 -22.0 2
2011-12-16 265113334 -0.1 3
但我不要摘要(4条记录)。我想要所有相关记录(因此2 + 2 + 2 + 3 = 9条记录)。如果GROUP BY位于1列上,那也不难,但是只有3列...
如何获得具有这些值的实际记录?1个查询必须是可能的。我需要3个子查询吗?
一种方法是重新加入 transactions
SELECT *
FROM transactions t JOIN
(
SELECT date, account, amount
FROM transactions
GROUP BY date, account, amount
HAVING COUNT(*) > 1
) d
ON (t.date = d.date
AND t.account = d.account
AND t.amount = d.amount) OR
(t.date = d.date
AND t.account IS NULL AND d.account IS NULL
AND t.amount = d.amount)
这是一个SQLFiddle演示
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句