从子查询返回多个聚合列

Question

我试图通过聚合来自另一个表的一些行来扩展现有的查询。当我像这样只返回一列时，它可以工作:

Select DISTINCT 
Contracts.id, 
Contracts.beginTime, 
Contracts.endTime, 
Suppliers.name
(SELECT COUNT(p.id) from production as p where p.id_contract = Contracts.id)
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract

然后我尝试为聚合卷添加另一列:

Select DISTINCT 
Contracts.id, 
Contracts.beginTime, 
Contracts.endTime, 
Suppliers.name
(SELECT COUNT(p.id), SUM(p.volume) from production as p where p.id_contract = Contracts.id)
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract

但是，这会返回以下错误:

当子查询没有引入EXISTS时，只能在选择列表中指定一个表达式。

我用EXISTS关键字做了一点实验，但不知道如何使它工作。我也不确定这是否适合我的情况。

期望的输出是这样的:

contract1Id, supplierInfoContract1, nrItemsContract1, sumVolumeContract1
contract2Id, supplierInfoContract2, nrItemsContract2, sumVolumeContract2

Answer 1

不使用DISTINCT和子查询，使用GROUP BY和普通连接得到集合。经常使用别名，这会让你的生活更轻松:

SELECT
c.id,
c.beginTime,
c.endTime,
s.name,
COUNT(p.id) prod_count,
SUM(p.volume) prod_vol
FROM Contracts c
LEFT JOIN production p on p.id_contract = c.id
LEFT JOIN Suppliers s on c.id = s.id_contract
GROUP BY c.id, c.beginTime, c.endTime, s.name;

另一个选项是以APPLY分组子查询:

SELECT DISTINCT
c.id,
c.beginTime,
c.endTime,
s.name,
p.prod_count,
p.prod_vol
FROM Contracts c
LEFT JOIN Suppliers s on c.id = s.id_contract
OUTER APPLY (
SELECT
COUNT(p.id) prod_count,
SUM(p.volume) prod_vol
FROM production p WHERE p.id_contract = c.id
GROUP BY ()
) p;

您也可以使用CROSS APPLY而不使用GROUP BY ()，这使用标量聚合并返回0而不是没有行的null。

最后一点:连接查询中的DISTINCT有点代码味，它通常表明查询编写器没有过多考虑连接表返回的内容，而只是想摆脱重复的行。

Answer 2

你应该这样使用:

Select DISTINCT Contracts.id, Contracts.beginTime, Contracts.endTime, Suppliers.name
(SELECT COUNT(p.id) from production as p where p.id_contract = Contracts.id) as CNT,
(SELECT SUM(p.volume) from production as p where p.id_contract = Contracts.id) as VOLUME
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract

Answer 3

我想你可以尝试重新工作你的查询

SELECT X.ID,X.beginTime,X.endTime,X.name,CR.CNTT,CR.TOTAL_VOLUME
FROM
(
Select DISTINCT Contracts.id, Contracts.beginTime, Contracts.endTime,      Suppliers.name
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract
)X
CROSS APPLY
(
SELECT COUNT(p.id)AS CNTT,SUM(p.volume) AS TOTAL_VOLUME
from production as p where p.id_contract = X.id
)CR

Answer 4

我通过分离子查询稍微修改了您的查询。

Select DISTINCT 
Contracts.id, 
Contracts.beginTime, 
Contracts.endTime, 
Suppliers.name,
(SELECT COUNT(p.id) from production as p where p.id_contract = Contracts.id) count_id,
(SELECT SUM(p.volume) from production as p where p.id_contract = Contracts.id) sum_volume
FROM Contracts 
LEFT JOIN Suppliers on Contracts.id = Suppliers.id_contract