SQL Server 2014中统计两班组共通设备故障总次数的问题

核心需求实现：直接获取共通设备总故障次数 #

无需复杂语法，先筛选出同时在A、B班组出现故障的设备，再统计这些设备的总故障次数：

declare @start datetime = DATEADD(day, -30, getutcdate())
declare @end datetime = getutcdate()

SELECT COUNT(Equipment) AS NumIncidents, Equipment
FROM tbl_PrestartRdyScoreboard_test
WHERE ShiftStart > @start 
  AND ShiftStart < @end 
  AND PrestartMaintTeam <> 'U' 
  AND Equipment <> ''
  -- 仅保留同时在A、B班组出现故障的设备
  AND Equipment IN (
      SELECT Equipment
      FROM tbl_PrestartRdyScoreboard_test
      WHERE ShiftStart > @start 
        AND ShiftStart < @end 
        AND PrestartMaintTeam IN ('A', 'B')
        AND Equipment <> ''
      GROUP BY Equipment
      HAVING COUNT(DISTINCT PrestartMaintTeam) = 2
  )
GROUP BY Equipment
ORDER BY Equipment

执行后将直接返回你需要的结果：

基于现有CTE的优化方案 #

如果你想沿用原有的CTE思路，只需修改JOIN后的列选择，对两个班组的故障次数求和，并避免重复列名：

declare @start datetime = DATEADD(day, -30, getutcdate())
declare @end datetime = getutcdate()

With 
t1 AS 
    (
    -- 去掉多余的DISTINCT，GROUP BY已保证唯一性
    SELECT Equipment, count(Equipment) as NumIncidentsA
    FROM tbl_PrestartRdyScoreboard_test
    WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'A' and Equipment <> ''
    GROUP BY Equipment
),
t2 AS
    (
    SELECT Equipment, count(Equipment) as NumIncidentsB
    FROM tbl_PrestartRdyScoreboard_test
    WHERE ShiftStart > @start and ShiftStart < @end and PrestartMaintTeam = 'B' and Equipment <> ''
    GROUP BY Equipment
)
-- 明确指定列名，避免重复的Equipment列
SELECT (t1.NumIncidentsA + t2.NumIncidentsB) AS NumIncidents, t1.Equipment
FROM t1 INNER JOIN t2
ON t1.Equipment = t2.Equipment
ORDER BY t1.Equipment

PIVOT语法的列名冲突解决 #

如果确实需要使用PIVOT，需先整理数据结构，避免重复列名，同时实现总次数统计：

declare @start datetime = DATEADD(day, -30, getutcdate())
declare @end datetime = getutcdate()

WITH ShiftEquipmentCounts AS (
    SELECT 
        Equipment,
        PrestartMaintTeam,
        COUNT(Equipment) AS IncidentCount
    FROM tbl_PrestartRdyScoreboard_test
    WHERE ShiftStart > @start 
      AND ShiftStart < @end 
      AND PrestartMaintTeam IN ('A', 'B')
      AND Equipment <> ''
    GROUP BY Equipment, PrestartMaintTeam
),
CommonEquipment AS (
    SELECT Equipment
    FROM ShiftEquipmentCounts
    GROUP BY Equipment
    HAVING COUNT(DISTINCT PrestartMaintTeam) = 2
),
PivotedData AS (
    SELECT 
        Equipment,
        ISNULL([A], 0) AS A_Incidents,
        ISNULL([B], 0) AS B_Incidents
    FROM ShiftEquipmentCounts
    PIVOT (
        SUM(IncidentCount)
        FOR PrestartMaintTeam IN ([A], [B])
    ) AS PivotTable
    WHERE Equipment IN (SELECT Equipment FROM CommonEquipment)
)
-- 计算总次数并展示各班组明细
SELECT 
    Equipment,
    A_Incidents + B_Incidents AS NumIncidents,
    A_Incidents,
    B_Incidents
FROM PivotedData
ORDER BY Equipment

执行结果：

关键说明 #

1. 原查询中的DISTINCT属于冗余：GROUP BY已完成分组去重，无需额外添加。
2. 重复列名错误根源：原CTE的SELECT *会同时返回t1.Equipment和t2.Equipment，明确指定列名即可解决。
3. 共通设备筛选逻辑：通过HAVING COUNT(DISTINCT PrestartMaintTeam) = 2确保设备同时出现在两个班组中。

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