如何保存开关案例结果在不同的列

Question

我目前有以下查询:

SELECT  name, id, 
  CASE 
    WHEN( status = 'Missing' AND severity = 'Optional' ) AND ( id=123 ) 
    THEN COALESCE(count(patchid),0)
    ELSE 0
  END AS missingoptional,
  CASE 
    WHEN( status = 'Missing' AND severity = 'Important' ) AND ( id=123 ) 
    THEN COALESCE(count(patchid),0)
    ELSE 0
  END  as missingimportant
FROM tablename
GROUP BY  name, id, status, severity
ORDER BY id

当前结果:

name            id          missingoptional     missingimportant
abc             123         10                  0
abc             123         0                   20

预期结果:

name            id          missingoptional     missingimportant
abc             123         10                  20

请让我知道我必须在上述查询中做哪些更改才能获得预期的输出。

Answer 1

也许这就是你想要的:

SELECT name, id, 
  SUM(CASE 
      WHEN( status = 'Missing' AND severity = 'Optional' ) AND ( id=123 ) 
          THEN 1
      ELSE 0
      END) AS missingoptional,
  SUM(CASE 
      WHEN( status = 'Missing' AND severity = 'Important' ) AND ( id=123 ) 
          THEN 1
      ELSE 0
      END) as missingimportant
    FROM tablename
GROUP BY name, id
ORDER BY id

我删除了GROUP BY的状态和严重性，而将SUM()放在选择列表中进行聚合。

Answer 2

使用MAX()聚合函数

SELECT  name, id, 
  MAX(CASE 
    WHEN( status = 'Missing' AND severity = 'Optional' ) AND ( id=123 ) 
    THEN COALESCE(count(patchid),0)
    ELSE 0
  END) AS missingoptional,
  MAX(CASE 
    WHEN( status = 'Missing' AND severity = 'Important' ) AND ( id=123 ) 
    THEN COALESCE(count(patchid),0)
    ELSE 0
  END)  as missingimportant
FROM tablename
GROUP BY  name, id
ORDER BY id