用内连接分组和计数

Question

我有一个订单表和一个材料表(1:N)。我想知道某个生产订单和国家的材料表中有多少订单有屏幕。

底部的查询给出了每个订单的每个订单的屏幕数量，而不是预期的结果5:

6
1 
6
2
2 
SELECT Count(ORD.ORDERNUMMER)
FROM        Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'
Group By ORD.Ordernummer HAVING (COUNT(*) >= 1)

我只想让5返回。

提前感谢，

迈克

Answer 1

你需要计算每一个不同的顺序，如果它至少有一行的材料是屏幕

SELECT Count(DISTINCT ORD.ORDERNUMMER)
FROM        Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'

Answer 2

如果需要全部的结果，就不要把结果分组!

SELECT Count(ORD.ORDERNUMMER) as totalScreens
FROM Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'
HAVING (totalScreens >= 1)