SQL 选择列.. IF NULL 然后选择其他列

Question

我的观点是这样的:

ID| Key | Product | Item | Block | Source | Title | Text | Type | H1 | H2 | H3 |
-------------------------------------------------------------------------------
1 | 456 | abcd    | def  |  1    |  TP    | QWERT | YUIP | tgr  | A1 | A2 | A3 |
2 | 567 | fhrh    | klo  |  1    |  GT    | TREWQ | ITGF | trp  | A1 | A2 | A3 |
3 | 891 | ufheu   | yut  |  2    |  FR    | WERTY | MNBV | uip  |NULL|NULL|NULL|

我想将其中一些列导出到现有的空表中。我想选择前六列，然后选择其他列，就像从右到左的层次结构一样。

如果 H1、H2 和 H3 不为空，则它们应该出现在输出中并且标题、文本和类型应该为空(即使它们包含值)。

如果 H1、H2 和 H3 为 NULL，我希望输出中包含标题、文本和类型。

应该是这样的:

ID| Key | Product | Item | Block | Source | Title | Text | Type | H1 | H2 | H3 |
-------------------------------------------------------------------------------
1 | 456 | abcd    | def  |  1    |  TP    | NULL  | NULL | NULL | A1 | A2 | A3 |
2 | 567 | fhrh    | klo  |  1    |  GT    | NULL  | NULL | NULL | A1 | A2 | A3 |
3 | 891 | ufheu   | yut  |  2    |  FR    | WERTY | MNBV | uip  |NULL|NULL|NULL|

谁能帮我解决这个问题？非常感谢您的帮助!

Answer 1

如果你想逐列比较，那么使用coalesce():

select ID, Key, Product, Item, Block, Source,
       (case when h1 is not null then null else title end) as title,
       (case when h2 is not null then null else text end) as text,
       (case when h3 is not null then null else type end) as type,
       coalesce(h1, title) as h1,
       coalesce(h2, text) as h2,
       coalesce(h3, type) as h3
from t;

但是，我不确定您是否同时指所有三列:

select ID, Key, Product, Item, Block, Source,
       (case when h1 is null and h2 is null and h3 is null then title end) as title,
       (case when h1 is null and h2 is null and h3 is null then text end) as text,
       (case when h1 is null and h2 is null and h3 is null then type end) as type,
       (case when h1 is null and h2 is null and h3 is null then NULL else h1 end) as h1,
       (case when h1 is null and h2 is null and h3 is null then NULL else h2 end) as h2,
       (case when h1 is null and h2 is null and h3 is null then NULL else h3 end) as h3
from t;