解决SELECT列表字段未在聚合/GROUP BY中的错误并获取两表最小EndDate

错误原因 #

原SQL中的子查询未关联到当前分组的客户，会返回全局所有EndDate的最小值，且该子查询既不属于聚合函数，也未出现在GROUP BY子句中，因此触发错误：SELECT列表中的列无效，因为它未包含在聚合函数或GROUP BY子句中。

修复方案 #

我们需要为每个客户单独计算其对应的ServerLicenseDetails和SiteLicenseDetails表中的最小结束日期，以下提供两种可行方案：

方案一：关联子查询 #

通过在子查询中添加客户ID关联条件，确保每个分组仅计算当前客户的最小结束日期：

SELECT  C.ID
 ,C.Name
 ,C.Email
 ,COUNT(DISTINCT SE.ID) 'Servers'
 ,COUNT(DISTINCT SI.ID) 'Sites'
 ,COALESCE(SUM(DISTINCT SU.Users), 0) 'User'
 ,(SELECT MIN(EndDate)
   FROM (
       -- 取当前客户的ServerLicenseDetails结束日期
       SELECT SLD.EndDate 
       FROM ServerLicenseDetails SLD 
       WHERE SLD.CustomerID = C.ID
       UNION ALL
       -- 取当前客户关联的SiteLicenseDetails结束日期
       SELECT SILD.EndDate 
       FROM SiteLicenseDetails SILD 
       JOIN Site SI ON SI.ID = SILD.ID
       JOIN Server SE ON SE.ID = SI.ServerID
       JOIN ServerLicenseDetails SLD ON SLD.ServerID = SE.ID
       WHERE SLD.CustomerID = C.ID
   ) AS CombinedEndDates) AS EndDate
FROM Customer AS C
LEFT JOIN ServerLicenseDetails AS SLD ON C.ID = SLD.CustomerID
LEFT JOIN Server AS SE ON SLD.ServerID = SE.ID
LEFT JOIN Site AS SI ON SE.ID = SI.ServerID 
LEFT JOIN SiteUsers AS SU ON SI.ID = SU.SiteID
LEFT JOIN SiteLicenseDetails AS SILD ON SI.ID = SILD.ID
GROUP BY C.ID, C.Name, C.Email

方案二：提前计算客户的最小结束日期（CTE方式） #

先通过CTE计算每个客户在两个表中的最小结束日期，再关联到主查询，逻辑更清晰：

WITH CustomerEndDates AS (
    SELECT 
        C.ID,
        -- 取当前客户ServerLicenseDetails的最小结束日期
        MIN(SLD.EndDate) AS SLD_Min_EndDate,
        -- 取当前客户关联SiteLicenseDetails的最小结束日期
        MIN(SILD.EndDate) AS SILD_Min_EndDate
    FROM Customer C
    LEFT JOIN ServerLicenseDetails SLD ON C.ID = SLD.CustomerID
    LEFT JOIN SiteLicenseDetails SILD 
        ON SILD.ID = (SELECT SI.ID FROM Site SI JOIN Server SE ON SE.ID = SI.ServerID WHERE SE.ID = SLD.ServerID)
    GROUP BY C.ID
)
SELECT 
    C.ID,
    C.Name,
    C.Email,
    COUNT(DISTINCT SE.ID) 'Servers',
    COUNT(DISTINCT SI.ID) 'Sites',
    COALESCE(SUM(DISTINCT SU.Users), 0) 'User',
    -- 对比两个最小日期，取更小的那个
    CASE 
        WHEN SLD_Min_EndDate IS NULL AND SILD_Min_EndDate IS NULL THEN NULL
        WHEN SLD_Min_EndDate IS NULL THEN SILD_Min_EndDate
        WHEN SILD_Min_EndDate IS NULL THEN SLD_Min_EndDate
        ELSE LEAST(SLD_Min_EndDate, SILD_Min_EndDate)
    END AS EndDate
FROM Customer AS C
LEFT JOIN ServerLicenseDetails AS SLD ON C.ID = SLD.CustomerID
LEFT JOIN Server AS SE ON SLD.ServerID = SE.ID
LEFT JOIN Site AS SI ON SE.ID = SI.ServerID 
LEFT JOIN SiteUsers AS SU ON SI.ID = SU.SiteID
LEFT JOIN SiteLicenseDetails AS SILD ON SI.ID = SILD.ID
LEFT JOIN CustomerEndDates CED ON C.ID = CED.ID
GROUP BY C.ID, C.Name, C.Email, CED.SLD_Min_EndDate, CED.SILD_Min_EndDate

关键说明 #

• 使用UNION ALL而非UNION可以避免去重带来的性能损耗，因为我们只需要取最小值，重复值不影响结果。
• 通过关联条件确保子查询只针对当前客户的日期数据，符合GROUP BY的分组逻辑，解决报错问题。

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