MySQL SUM over CASE 语句

Question

有人可以帮我获取下面 SQL 中 A_VAL 的 SUM

SELECT 
  CASE
   WHEN COUNT(t.A_INS_NAM) = 10 THEN   1
   WHEN COUNT(t.A_INS_NAM) = 20 THEN   2
   WHEN COUNT(t.A_INS_NAM) BETWEEN 30 AND 50 THEN  3
   WHEN COUNT(t.A_INS_NAM)  > 50 THEN  5
   ELSE 'some value' 
  END AS A_VAL
FROM
  TABLE1 t
  JOIN IWD t1 ON(t.A_INS_NAM = t1.A_INS_NAM)
  JOIN TIM t2 ON(t1.IWD = t2.IM_ID)
  JOIN TWS t3 ON(t2.IM_ID = t3.T_ID )
WHERE t3.TN = 'abced'
  AND t.A_DATE BETWEEN '2014-01-01' AND '2015-05-01' 
  AND t.A_INS_NAM NOT LIKE '%pk%'

Answer 1

您可以使用子选择来计算总和，请注意，您使用的是没有分组依据的聚合函数，它将结果为单行

SELECT SUM(A_VAL)
FROM (
    SELECT 
      CASE
       WHEN COUNT(t.A_INS_NAM) = 10 THEN   1
       WHEN COUNT(t.A_INS_NAM) = 20 THEN   2
       WHEN COUNT(t.A_INS_NAM) BETWEEN 30 AND 50 THEN  3
       WHEN COUNT(t.A_INS_NAM)  > 50 THEN  5
       ELSE 'some value' 
      END AS A_VAL
    FROM
      TABLE1 t
      JOIN IWD t1 ON(t.A_INS_NAM = t1.A_INS_NAM)
      JOIN TIM t2 ON(t1.IWD = t2.IM_ID)
      JOIN TWS t3 ON(t2.IM_ID = t3.T_ID )
    WHERE t3.TN = 'abced'
      AND t.A_DATE BETWEEN '2014-01-01' AND '2015-05-01' 
      AND t.A_INS_NAM NOT LIKE '%pk%'
) a