如何对SQL中的子选定列进行分组依据

可以使用子查询方法

SELECT MONTH(StatusDate), sum(w.Number) num
FROM
(
  SELECT 
     W.UserID, 
     W.Number,
     (SELECT StatusDate FROM WStatuses AS WS WHERE WS.Number = W.NUmber AND WS.StatusType = 'CRFI') AS StatusDate
     W.Model
  FROM WorkOrder AS W
) T
GROUP BY MONTH(StatusDate)

替代方法(如果您确定只有一个记录将从带有筛选器的wstatus返回 CRFI )

SELECT 
   avg(W.Number),
   MONTH(StatusDate)
FROM WorkOrder AS W
INNER JOIN Wstatuses 
  on WS.Number = W.NUmber AND WS.StatusType = 'CRFI'
GROUP BY MONTH(StatusDate)

考虑避免相关子查询(计算 每一个 row)提高效率 JOIN . 此外,包括中列出的所有非聚合列 SELECT 条款纳入 GROUP BY 条款 对的 按照ANSI标准运行聚合查询的方法。并运行所需的聚合,如下面的调整所示。

SELECT 
   W.UserID, 
   W.Number,
   MONTH(S.StatusDate) AS StatusMonth
   W.Model,
   COUNT(...) AS CountAgg,
   SUM(...) AS SumAgg,
   AVG(...) AS AvgAgg,
   ...
FROM WorkOrder AS W
INNER JOIN WStatuses AS S
  ON S.Number = W.NUmber AND S.StatusType = 'CRFI'

GROUP BY W.UserID, 
         W.Number,      
         MONTH(S.StatusDate)
         W.Model