T-SQL生成单行五数汇总表遇报错，求正确实现方案

错误原因 #

你的SQL报错是因为同时混用了聚合函数（MIN/MAX）和窗口函数（PERCENTILE_CONT）：窗口函数会为CTE中的每一行返回相同的汇总值，而聚合函数是对整个数据集做汇总，SQL无法直接解析这种混合逻辑，导致列不在GROUP BY或聚合函数的错误提示。

修正后的SQL方案 #

方案一：统一使用窗口函数 + DISTINCT #

将MIN/MAX也改为窗口函数（OVER()），此时每一行都会返回相同的汇总结果，通过DISTINCT去重得到单行表：

WITH CTE AS 
(
    SELECT
        CustomerID,
        AVG(DaysBetweenPurchases) AS Avg_days_between_purchase
    FROM 
        -- 先去重订单头数据，避免同一订单的多行明细重复计算间隔
        (SELECT DISTINCT
             CustomerID,
             DATEDIFF(day, LAG(orderdate) OVER (PARTITION BY CustomerID ORDER BY orderdate), orderdate) AS DaysBetweenPurchases
         FROM
             Sales.SalesOrderHeader sh (NOLOCK)
        ) AS subquery
    GROUP BY CustomerID
)
SELECT DISTINCT
    MIN(Avg_days_between_purchase) OVER () AS Min,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Q3,
    MAX(Avg_days_between_purchase) OVER () AS Max
FROM CTE;

方案二：子查询计算后取单行结果 #

先通过子查询生成所有汇总值（此时每一行结果相同），再取TOP 1得到单行表：

WITH CTE AS 
(
    SELECT
        CustomerID,
        AVG(DaysBetweenPurchases) AS Avg_days_between_purchase
    FROM 
        (SELECT DISTINCT
             CustomerID,
             DATEDIFF(day, LAG(orderdate) OVER (PARTITION BY CustomerID ORDER BY orderdate), orderdate) AS DaysBetweenPurchases
         FROM
             Sales.SalesOrderHeader sh (NOLOCK)
        ) AS subquery
    GROUP BY CustomerID
),
SummaryCTE AS (
    SELECT
        MIN(Avg_days_between_purchase) AS Min,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Q1,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Median,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Avg_days_between_purchase) OVER () AS Q3,
        MAX(Avg_days_between_purchase) AS Max
    FROM CTE
)
SELECT TOP 1 Min, Q1, Median, Q3, Max FROM SummaryCTE;

额外优化说明 #

原SQL中关联SalesOrderHeader和SalesOrderDetail会导致同一订单的多行明细重复输出，进而让DaysBetweenPurchases重复计算。建议直接从SalesOrderHeader取去重后的订单数据，避免不必要的重复计算，提升查询效率。

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