填写查询结果中的漏洞

Question

我的查询返回：

+------+------+------+------+------+------+------+-------+------+------+------+------+-----+
| Jan  | Feb  | Mar  | Apr  | May  | Jun  | Jul  |  Aug  | Sep  | Oct  | Nov  | Dec  | Bla |
+------+------+------+------+------+------+------+-------+------+------+------+------+-----+
|    0 |    0 |    0 |    0 |    0 |    0 |    0 |     0 |    0 |    0 |    2 |    0 |  13 |
|    1 |    0 |    0 |    0 |    0 |    0 |    0 |     0 |    0 |    2 |    0 |    0 |  14 |
|    0 |    0 |    0 |    0 |    0 |    9 |    0 |     0 |    0 |    0 |    8 |   37 |  29 |
|    0 |    0 |    0 |    0 |    0 |    0 |    0 |     0 |    0 |    1 |    0 |  374 |  30 |
|    0 |    0 |    1 |    0 |   78 |    2 |    4 |     8 |   57 |  169 |  116 |  602 |  31 |
|  156 |  255 |   79 |   75 |  684 |  325 |  289 |   194 |  407 |  171 |  584 |  443 |  32 |
| 1561 | 2852 | 2056 |  796 | 2004 | 1755 |  879 |  1052 | 1490 | 1683 | 2532 | 2381 |  33 |
| 4167 | 3841 | 4798 | 3399 | 4132 | 5849 | 3157 |  4381 | 4424 | 4487 | 4178 | 5343 |  34 |
| 5472 | 5939 | 5768 | 4150 | 7483 | 6836 | 6346 |  6288 | 6850 | 7155 | 5706 | 5231 |  35 |
| 5749 | 4741 | 5264 | 4045 | 6544 | 7405 | 7524 |  6625 | 6344 | 5508 | 6513 | 3854 |  36 |
| 5464 | 6323 | 7074 | 4861 | 7244 | 6768 | 6632 |  7389 | 8077 | 8745 | 6738 | 5039 |  37 |
| 5731 | 7205 | 7476 | 5734 | 9103 | 9244 | 7339 |  8970 | 9726 | 9089 | 6328 | 5512 |  38 |
| 7262 | 6149 | 8231 | 6654 | 9886 | 9834 | 9306 | 10065 | 9983 | 9984 | 6738 | 5806 |  39 |
| 5886 | 6934 | 7137 | 6978 | 9034 | 9155 | 7389 |  9437 | 9711 | 8665 | 6593 | 5337 |  40 |
+------+------+------+------+------+------+------+-------+------+------+------+------+-----+

如您所见，BLA 列从 13 开始。 我希望它从 1 开始，然后是 2，然后是 3 等......我不希望数据有任何差距。之所以存在差距，是因为该特定bla的所有月份都0

如何让结果集包含 BLA 的所有值，甚至是那些将在几个月内产生0的值？

以下是所需的结果：

+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 1   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 2   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 3   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 4   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 5   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 6   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 7   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 8   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 9   |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 10  |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 11  |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 12  |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 13  |
| 1   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 2   | 0   | 0   | 14  |
| 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 15  |
| …   | …   | …   | …   | …   | …   | …   | …   | …   | …   | …   | …   | …   |
+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+

这是我的查询：

SELECT  CASE WHEN SUM(Jan) is not null THEN SUM(Jan) ELSE 0 end Jan,
        CASE WHEN SUM(Feb) is not null THEN SUM(Feb) ELSE 0 end Feb,
        CASE WHEN SUM(Mar) is not null THEN SUM(Mar) ELSE 0 end Mar,
        CASE WHEN SUM(Apr) is not null THEN SUM(Apr) ELSE 0 end Apr,
        CASE WHEN SUM(May) is not null THEN SUM(May) ELSE 0 end May,
        CASE WHEN SUM(Jun) is not null THEN SUM(Jun) ELSE 0 end Jun,
        CASE WHEN SUM(Jul) is not null THEN SUM(Jul) ELSE 0 end Jul,
        CASE WHEN SUM(Aug) is not null THEN SUM(Aug) ELSE 0 end Aug,
        CASE WHEN SUM(Sep) is not null THEN SUM(Sep) ELSE 0 end Sep,
        CASE WHEN SUM(Oct) is not null THEN SUM(Oct) ELSE 0 end Oct,
        CASE WHEN SUM(Nov) is not null THEN SUM(Nov) ELSE 0 end Nov,
        CASE WHEN SUM(Dec) is not null THEN SUM(Dec) ELSE 0 end Dec,
        CASE WHEN Bla > 191 THEN 192 ELSE Bla END Bla
FROM CTE
GROUP BY CASE WHEN Bla > 191 THEN 192 ELSE Bla END
order by bla

Answer 1

您可以使用数字表或具有 1 到 192 之间所有值的表。您可以创建该表，使用循环或递归 CTE 随时随地创建一个表，使用某些系统视图等。这是一个例子：

SELECT  ISNULL(SUM(Jan),0) Jan,
        ISNULL(SUM(Feb),0) Feb,
        ISNULL(SUM(Mar),0) Mar,
        ISNULL(SUM(Apr),0) Apr,
        ISNULL(SUM(May),0) May,
        ISNULL(SUM(Jun),0) Jun,
        ISNULL(SUM(Jul),0) Jul,
        ISNULL(SUM(Aug),0) Aug,
        ISNULL(SUM(Sep),0) Sep,
        ISNULL(SUM(Oct),0) Oct,
        ISNULL(SUM(Nov),0) Nov,
        ISNULL(SUM(Dec),0) Dec,
        A.RN Bla
FROM (  SELECT *, RN=ROW_NUMBER() OVER(ORDER BY object_id)
        FROM sys.all_objects) A
LEFT JOIN CTE B
ON A.RN = CASE WHEN B.Bla > 191 THEN 192 ELSE B.Bla END
WHERE A.RN BETWEEN 1 AND 192
GROUP BY A.RN
order by A.RN

Answer 2

我创建了一个演示，可能是您需要的，它有几个版本的 Bla 列，Bla3 是结果中显示的最终版本，您可以轻松修改此查询以处理您的表格，请尝试一下：

DECLARE @tbl TABLE (Jan INT, Feb INT, Mar INT, Apr INT, May INT, Jun INT, 
Jul INT, Aug INT, Sep INT, Oct INT, Nov INT, Dec INT, Bla INT)
INSERT @tbl VALUES 
(0,0,0,0,0,0,0,0,0,0,0,4,1),
(0,0,0,0,0,0,0,0,0,0,0,4,1),
(0,0,0,0,0,0,0,0,0,0,0,0,12),
(0,12,10,10,10,11,10,0,0,0,0,21,35),
(0,12,10,10,10,11,10,0,0,0,0,22,47),
(0,12,10,10,10,11,10,0,0,0,0,23,192),
(0,12,10,10,10,11,10,0,0,0,0,24,203)
;WITH a AS (
    SELECT  *, DENSE_RANK() OVER (ORDER BY Bla) Bla2
    FROM @tbl
),
b AS (
    SELECT  *, 
            CASE WHEN Bla > 191 THEN 192 ELSE Bla2 END Bla3
    FROM a
)
SELECT  ISNULL(SUM(Jan), 0) Jan,
        ISNULL(SUM(Feb), 0) Feb,
        ISNULL(SUM(Mar), 0) Mar,
        ISNULL(SUM(Apr), 0) Apr,
        ISNULL(SUM(May), 0) May,
        ISNULL(SUM(Jun), 0) Jun,
        ISNULL(SUM(Jul), 0) Jul,
        ISNULL(SUM(Aug), 0) Aug,
        ISNULL(SUM(Sep), 0) Sep,
        ISNULL(SUM(Oct), 0) Oct,
        ISNULL(SUM(Nov), 0) Nov,
        ISNULL(SUM(Dec), 0) Dec,
        Bla3
FROM    b
GROUP   BY Bla3
ORDER   BY Bla3