SQL Server:用 0 为不存在的行填充 MAX(列)值

问题描述

对于表中不存在的行，我想为 MAX_TIER 返回 0

I want to return 0 for MAX_TIER for non existing rows in table

这是我目前的代码:

SELECT   
    LAST_YARD_BAY, LAST_YARD_ROW, MAX(LAST_YARD_TIER) as MAX_TIER
FROM
    Handlift
WHERE 
    HDCHKOUTD_OUT IS NULL 
    AND LIFLAG = 'A'
    AND LAST_YARD_PARK = 'J1'
GROUP BY 
    LAST_YARD_BAY, LAST_YARD_ROW
ORDER BY 
    LAST_YARD_BAY, LAST_YARD_ROW;

截图显示了部分结果:

对于 Bay 005 我有 MAX_TIER 用于 ROWS 00A 到 00G，但是对于 Bay 007 我只有 3 行:00E、00F 和 00G.

For Bay 005 I have MAX_TIER for ROWS 00Ato 00G, but for Bay 007 I have only 3 Rows: 00E,00F and 00G.

如何为 4 个缺失的行(00A、00B、00C)填充 MAX_TIER和 00D) Bay 007 和其他缺失的行?

How can I populate MAX_TIER with value 0 for the 4 missing rows (00A, 00B, 00C and 00D) of Bay 007 and the other missing rows?

推荐答案

这就是您要找的吗?

SELECT *
FROM T
WHERE LastYardBay = '005'
UNION
SELECT '007',
       T1.LastYardRow,
       ISNULL(T2.MaxTier, 0)
FROM 
    (
      SELECT *
      FROM T
      WHERE LastYardBay = '005'
    ) T1
    LEFT JOIN
    (
      SELECT *
      FROM T
      WHERE LastYardBay = '007'
    ) T2
ON T1.LastYardRow = T2.LastYardRow;

退货:

+-------------+-------------+---------+
| LastYardBay | LastYardRow | MaxTier |
+-------------+-------------+---------+
|         005 | 00A         |       3 |
|         005 | 00B         |       4 |
|         005 | 00C         |       1 |
|         005 | 00D         |       1 |
|         005 | 00E         |       1 |
|         005 | 00F         |       4 |
|         005 | 00G         |       1 |
|         007 | 00A         |       0 |
|         007 | 00B         |       0 |
|         007 | 00C         |       0 |
|         007 | 00D         |       0 |
|         007 | 00E         |       5 |
|         007 | 00F         |       4 |
|         007 | 00G         |       1 |
+-------------+-------------+---------+

现场演示

更新:

因为你已经有了 '005' 你只需要一个 CROSS JOIN 然后用 IS NULL as 过滤

Since you already have '005' you need just a CROSS JOIN then filter with IS NULL as

WITH A AS
(
SELECT       T1.LastYardBay LYB1,
             T1.LastYardRow LYR1,
             T1.MaxTier MT1,
             T2.LastYardBay LYB2,
             T2.LastYardRow LYR2,
             T2.MaxTier MT2
FROM 
    (
      SELECT *
      FROM T
      WHERE LastYardBay = '005'
    ) T1
    CROSS JOIN
    (
      SELECT *
      FROM T
      WHERE LastYardBay != '005'
    ) T2
)
SELECT *
FROM T
WHERE LastYardBay = '005'
UNION
SELECT  LYB2,
        LYR1,   
        CASE WHEN LYR2 = LYR1 THEN MT2 ELSE 0 END MT
FROM A LEFT JOIN T ON A.LYB2 IS NULL;