SQL:即使不是全部都被执行，我怎样才能确保所有 SELECT Cases 都被表示出来？

Question

这是我的代码

SELECT
  CASE
       WHEN Money >= 20000 THEN '$ 20,000 + '
       WHEN Money BETWEEN 10000 AND 19999 THEN '$ 10,000 - $ 19,999'
       WHEN Money BETWEEN  5000 AND  9999 THEN '$  5,000 - $  9,999'
       WHEN Money BETWEEN     1 AND  4999 THEN '$      1 - $  4,999'
       ELSE '$      0'
   END AS [MONEY],
       COUNT(*) AS [#],
       MAX(Money) AS [MAX]
  FROM MyTable
 WHERE MoneyType = 'Type A'
 GROUP BY 
  CASE
       WHEN Money >= 20000 THEN '$ 20,000 + '
       WHEN Money BETWEEN 10000 AND 19999 THEN '$ 10,000 - $ 19,999'
       WHEN Money BETWEEN  5000 AND  9999 THEN '$  5,000 - $  9,999'
       WHEN Money BETWEEN     1 AND  4999 THEN '$      1 - $  4,999'
       ELSE '$      0'
   END
 ORDER BY MAX DESC 

现在我的问题是我希望所有案例都在我的结果集中显示一行，但是，因为我没有任何介于 1 和 4999 之间的值，该行不会显示。我仍然希望该行显示出来，并且它的列只包含 0(当然第一列除外)。任何人都可以告诉我如何修改代码来实现这一点？也许我需要以不同的方式来做...谢谢!

我正在寻找的结果集示例...

  |  [MONEY]              |   [#]   |    [MAX]    |
  |  $ 20,000+            |   2     |    30,000   |
  |  $ 10,000 - $ 19,999  |   8     |    19,000   |
  |  $  5,000 - $  9,999  |   4     |     8,000   |
  |  $      1 - $  4,999  |   0     |     0       |     <-- Row currently doesn't show
  |  $      0             |   12    |     0       |

Answer 1

您可以使用 CTE 构建一个查找表，然后使用它来分组而不是 Case 语句。您需要进行其他三项更改

COUNT 必须是 COUNT(t.Money) 否则当你期望一个零时你会得到一个 1。

您可能想要合并您的 MAX(Money)，但我不确定当它为 NULL 时您希望它是什么

您实际上不能按 MAX(MONEY) 订购，因为它可能为空。所以最好也用CTE来控制顺序

WITH Ranges AS
( SELECT 1 id , '$ 20,000 +'  description
  UNION SELECT 2 , '$ 10,000 - $19,999'
  UNION SELECT 3, '$  5,000 - $ 9,999'
  UNION SELECT 4, '$      1 - $ 4,999'
  UNION SELECT 5, '$      0')


SELECT
       r.Description as money,
       COUNT(t.Money) AS [#],
       MAX(Money) AS [MAX]
  FROM 
       Ranges r
       LEFT JOIN
        MyTable t
       ON r.ID =   CASE
                    WHEN Money >= 20000 THEN 1
                    WHEN Money BETWEEN 10000 AND 19999 THEN 2
                    WHEN Money BETWEEN  5000 AND  9999 THEN 3
                    WHEN Money BETWEEN     1 AND  4999 THEN 4
                    ELSE 5
                 END 
        AND  MoneyType = 'Type A' 
 GROUP BY 
  r.id,
  r.Description
 ORDER BY r.id asc

LIVE DEMO