比较SQL Server中与组by的行

Question

我有一个下面的结果集/表。

Id       CustName     ItemId         OrderDate  
-------- ---------- --------------   ------------
1        AB         1                2020-01-02 
2        AB         2                2020-01-02
3        BB         2                2020-01-02  
4        BB         2                2020-01-02  

现在我想找出哪个客户在同一天订购了相同的商品超过1次，所以在查询之后，结果应该如下所示。

Id       CustName     ItemId         OrderDate  
-------- ---------- --------------   ------------
3        BB         2                2020-01-02  
4        BB         2                2020-01-02  

我已经尝试过自我加入，但不工作，提前感谢!

Answer 1

HAVING可与GROUP BY配合使用。

SELECT CustName, ItemId, OrderDate
, COUNT(*) AS Total
FROM yourtable
GROUP BY CustName, ItemId, OrderDate
HAVING COUNT(*) > 1

Answer 2

您可以使用EXISTS:

SELECT *
FROM t
WHERE EXISTS (
-- an order by same customer for same item and date but different order
SELECT *
FROM t AS x
WHERE x.CustName  = t.CustName
AND   x.ItemId    = t.ItemId
AND   x.OrderDate = t.OrderDate
AND   x.id        <> t.id
)