从子查询返回多个聚合列

不使用 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,s.name;

另一种选择是APPLY分组子查询：

SELECT DISTINCT
    c.id,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 ()、this uses a scalar aggregate 并返回 0 而不是 null 表示没有行。

最后一点：连接查询中的 DISTINCT 是 a bit of a code smell，这通常表明查询编写者没有认真考虑连接表返回的内容，并且只是想摆脱重复的行。

你应该像下面这样使用它：

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

我想您可以尝试将查询改写为

 SELECT X.ID,X.beginTime,X.endTime,X.name,CR.CNTT,CR.TOTAL_VOLUME
 FROM
 (
     Select DISTINCT Contracts.id,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

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

Select DISTINCT 
   Contracts.id,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