SQL自定义选择
问题描述:
我想写一个给出以下结果的sql。这可能吗? 我试过UNION但它只适用于一个记录。SQL自定义选择
col1 | col2 | col3 | col4 --------------------------- A | 10 | | | 2 | val1 | val2 | 5 | val3 | val4 | 3 | val5 | val6 B | 11 | | | 3 | val7 | val8 | 5 | val9 | val10 | 3 | val0 | val12
这里我的查询:
SELECT a.val1, NULL val2, a.val3 FROM table1 a
WHERE a.val1 = 'A'
UNION
SELECT NULL val1, b.val2, b.val3 FROM table2 b
WHERE b.val1 = 'A' ;
也许这会让问题更加清晰。
试想一下,如果我运行查询:
SELECT a.val1, NULL val2, a.val3 FROM table1 a
UNION
SELECT NULL val1, b.val2, b.val3 FROM table2 b ;
我想要的结果设置为如上图所示。
col1 | col2 | col3 | col4 --------------------------- A | 10 | | | 2 | val1 | val2 | 5 | val3 | val4 | 3 | val5 | val6 B | 11 | | | 3 | val7 | val8 | 5 | val9 | val10 | 3 | val0 | val12
答
无论汇总或常规组能做到这一点,但你还没有占到col2
。从哪里来的,就像你的sql片段一样,你根本就没有它。
SQL> create table yourtab(col1, col2, col3, col4)
2 as
3 select 'A', 2, 'val1', 'val2' from dual
4 union all select 'A', 5, 'val3', 'val4' from dual
5 union all select 'A', 3, 'val5', 'val6' from dual
6 union all select 'B', 3, 'val7', 'val8' from dual
7 union all select 'B', 5, 'val9', 'val10' from dual
8 union all select 'B', 3, 'val11', 'val12' from dual;
Table created.
SQL>
SQL> select col1, sum(col2) col2, col3, col4
2 from yourtab
3 group by col1, rollup(col3, col4)
4 having (grouping_id(col3), grouping_id(col4)) in ((0,0),(1,1))
5 order by col1, grouping_id(col3) desc;
C COL2 COL3 COL4
- ---------- ----- -----
A 10
A 5 val3 val4
A 3 val5 val6
A 2 val1 val2
B 11
B 3 val7 val8
B 5 val9 val10
B 3 val11 val12
8 rows selected.
SQL>
SQL> select col1, col2, col3, col4
2 from (select col1, col2, col3, col4, 2 grp
3 from yourtab
4 union all
5 select col1, sum(col2), null, null, 1 grp
6 from yourtab
7 group by col1)
8 order by col1, grp;
C COL2 COL3 COL4
- ---------- ----- -----
A 10
A 5 val3 val4
A 3 val5 val6
A 2 val1 val2
B 11
B 3 val11 val12
B 5 val9 val10
B 3 val7 val8
8 rows selected.
请显示您的表格构造 – silly 2013-02-19 05:43:23
'SQL'是指哪个RDBMS在这里:)? – aspiring 2013-02-19 05:45:33
你能解释一下结果吗? – Dukeling 2013-02-19 05:45:46