主查询关联SystemUser的子查询报错，能否如此使用该关联？

原SQL语句 #

SELECT 
SU.FullName as Salesperson,
COUNT(DS.new_dealsheetid) as Units,
SUM(SO.New_profits_sales_totaldealprofit) as TDP,
SUM(SO.New_profits_sales_totaldealprofit) / COUNT(DS.new_dealsheetid) as PPU,

-- opportunities subquery
(SELECT COUNT(*) FROM Opportunity O
LEFT JOIN Account A ON O.AccountId = A.AccountId
WHERE A.OwnerId = SU.SystemUserId AND
YEAR(O.CreatedOn) = 2022)
-- /opportunities subquery

FROM New_dealsheet DS
LEFT JOIN SalesOrder SO ON DS.New_DSheetId = SO.SalesOrderId
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId
LEFT JOIN SystemUser SU ON SP.New_SystemUserId = SU.SystemUserId
WHERE 
YEAR(SO.New_purchaseordersenddate) = 2022 AND
SP.New_SalesGroupIdName = 'LO'
GROUP BY SU.FullName

报错信息 #

列'SystemUser.SystemUserId'在选择列表中无效，因为它未包含在聚合函数或GROUP BY子句中。

问题解答 #

可以通过这种方式关联主表字段，但当前报错的核心原因是：GROUP BY仅指定了SU.FullName，而子查询中引用的SU.SystemUserId既不在GROUP BY列表里，也没有被聚合函数包裹。SQL语法要求，SELECT列表中的非聚合字段必须全部包含在GROUP BY中，或者能被GROUP BY的字段唯一确定。

解决方法一：扩展GROUP BY字段 #

直接把SU.SystemUserId加入GROUP BY列表。由于SystemUserId是用户表的主键，每个FullName对应唯一的SystemUserId，因此不会改变分组逻辑：

SELECT 
SU.FullName as Salesperson,
COUNT(DS.new_dealsheetid) as Units,
SUM(SO.New_profits_sales_totaldealprofit) as TDP,
SUM(SO.New_profits_sales_totaldealprofit) / COUNT(DS.new_dealsheetid) as PPU,
(SELECT COUNT(*) FROM Opportunity O
LEFT JOIN Account A ON O.AccountId = A.AccountId
WHERE A.OwnerId = SU.SystemUserId AND
YEAR(O.CreatedOn) = 2022) AS OpportunityCount

FROM New_dealsheet DS
LEFT JOIN SalesOrder SO ON DS.New_DSheetId = SO.SalesOrderId
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId
LEFT JOIN SystemUser SU ON SP.New_SystemUserId = SU.SystemUserId
WHERE 
YEAR(SO.New_purchaseordersenddate) = 2022 AND
SP.New_SalesGroupIdName = 'LO'
GROUP BY SU.FullName, SU.SystemUserId

解决方法二：改用JOIN+预聚合替代子查询 #

将子查询的聚合逻辑提前完成，再通过JOIN关联到主查询，这种方式在数据量较大时性能更优：

SELECT 
SU.FullName as Salesperson,
COUNT(DS.new_dealsheetid) as Units,
SUM(SO.New_profits_sales_totaldealprofit) as TDP,
SUM(SO.New_profits_sales_totaldealprofit) / COUNT(DS.new_dealsheetid) as PPU,
COALESCE(OppCount.TotalOpportunities, 0) AS OpportunityCount
FROM New_dealsheet DS
LEFT JOIN SalesOrder SO ON DS.New_DSheetId = SO.SalesOrderId
LEFT JOIN New_salespeople SP ON DS.New_SalespersonId = SP.New_salespeopleId
LEFT JOIN SystemUser SU ON SP.New_SystemUserId = SU.SystemUserId
LEFT JOIN (
    SELECT A.OwnerId, COUNT(*) AS TotalOpportunities
    FROM Opportunity O
    LEFT JOIN Account A ON O.AccountId = A.AccountId
    WHERE YEAR(O.CreatedOn) = 2022
    GROUP BY A.OwnerId
) OppCount ON OppCount.OwnerId = SU.SystemUserId
WHERE 
YEAR(SO.New_purchaseordersenddate) = 2022 AND
SP.New_SalesGroupIdName = 'LO'
GROUP BY SU.FullName, SU.SystemUserId, OppCount.TotalOpportunities

内容的提问来源于stack exchange，提问作者Ben