问题描述
对于表中不存在的行,我想为 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;