SQL查询如何为Employees列按统计值匹配乘数，能否用IF或CASE函数实现？

完全可以通过条件分支函数直接改造原有Employees列的计算逻辑，无需额外嵌套外层查询。

通用实现（兼容所有SQL版本） #

使用CASE WHEN条件判断直接包裹原统计逻辑，适配所有SQL Server版本，写法清晰易维护：

SELECT  
  b.masch_nr, 
  SUM(b.ruest_zeit) as Rüstzeit, 
  SUM(b.bearb_zeit) as Bearbzeit, 
  SUM(s.bmk_07) as Istrüstzeit,
  SUM(s.bmk_11) as Istbearbzeit,
  SUM(b.user_n_15) as Sollanzahl,
  -- 原有Istanzahl子查询保持不变
  (
    select SUM(p.user_n_07) 
    FROM [hydra1].[hydadm].[ade_protokoll] p
    JOIN [hydra1].[hydadm].[auftrags_bestand] b
      ON p.auftrag_nr = b.auftrag_nr
    WHERE b.masch_nr='QTA5VERP'
      AND p.anmeld_dat = DATEADD(day, 0, convert(date, GETDATE()))
      AND p.schichtnr = '1'
      AND p.satz_art = 'T'
  ) as Istanzahl,
  -- 改造后的Employees列
  (
    SELECT 
      CASE COUNT(DISTINCT p.person_nr1)
        WHEN 3 THEN COUNT(DISTINCT p.person_nr1) * 1.4
        WHEN 4 THEN COUNT(DISTINCT p.person_nr1) * 1.6
        WHEN 5 THEN COUNT(DISTINCT p.person_nr1) * 1.8
        ELSE COUNT(DISTINCT p.person_nr1) -- 其他统计值默认返回原计数，可按需调整
      END
    FROM [hydra1].[hydadm].[ade_protokoll] p
    WHERE p.masch_nr IN ('QTA5','QTA5VERP')
      AND p.anmeld_dat = DATEADD(day, 0, convert(date, GETDATE()))
      AND p.schichtnr = '1'
  ) as Employees
FROM [hydra1].[hydadm].[auftrags_bestand] b
JOIN [hydra1].[hydadm].[auftrag_status] s
  ON b.auftrag_nr = s.auftrag_nr
WHERE b.masch_nr = 'QTA5'
  AND s.prot_dat = DATEADD(day, 0, convert(date, GETDATE()))
  AND s.anmeldzeit BETWEEN '21600' AND '50400'
  AND s.a_status IN ('E','L')
GROUP BY b.masch_nr

IF风格实现（SQL Server 2012及以上版本） #

如果偏好类IF的写法，可以使用内置IIF函数嵌套实现，效果和CASE WHEN完全一致，把上述改造后的Employees子查询替换为：

(
  SELECT 
    IIF(COUNT(DISTINCT p.person_nr1) = 3, COUNT(DISTINCT p.person_nr1)*1.4,
      IIF(COUNT(DISTINCT p.person_nr1) = 4, COUNT(DISTINCT p.person_nr1)*1.6,
        IIF(COUNT(DISTINCT p.person_nr1) = 5, COUNT(DISTINCT p.person_nr1)*1.8,
          COUNT(DISTINCT p.person_nr1)
        )
      )
    )
  FROM [hydra1].[hydadm].[ade_protokoll] p
  WHERE p.masch_nr IN ('QTA5','QTA5VERP')
    AND p.anmeld_dat = DATEADD(day, 0, convert(date, GETDATE()))
    AND p.schichtnr = '1'
) as Employees

性能优化建议 #

如果统计量级较大，避免重复计算多次COUNT(DISTINCT p.person_nr1)，可以通过内层子查询先算出计数和对应乘数，再相乘得到结果，仅计算一次去重统计：

-- 改造后的Employees子查询优化版
(
  SELECT emp_count * multiplier
  FROM (
    SELECT 
      COUNT(DISTINCT p.person_nr1) as emp_count,
      CASE COUNT(DISTINCT p.person_nr1)
        WHEN 3 THEN 1.4
        WHEN 4 THEN 1.6
        WHEN 5 THEN 1.8
        ELSE 1
      END as multiplier
    FROM [hydra1].[hydadm].[ade_protokoll] p
    WHERE p.masch_nr IN ('QTA5','QTA5VERP')
      AND p.anmeld_dat = DATEADD(day, 0, convert(date, GETDATE()))
      AND p.schichtnr = '1'
  ) t
) as Employees

注：原写法里DATEADD(day, -0, ...)的-0是冗余参数，不影响执行结果，可直接简化为DATEADD(day, 0, ...)。

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