小试下新博客，一个列传行的SQL

源表的结构如下：

create table TAB_SAL
(
  empno         NUMBER,
  income_type   NUMBER,
  income_amount NUMBER
)

 

列转行SQL：

 1 select t.empno,
 2        (select sum(t1.income_amount)
 3           from TAB_SAL t1
 4          where t1.income_type = 11
 5            and t1.empno = t.empno),
 6        (select sum(t1.income_amount)
 7           from TAB_SAL t1
 8          where t1.income_type = 22
 9            and t1.empno = t.empno),
10        (select sum(t1.income_amount)
11           from TAB_SAL t1
12          where t1.income_type = 33
13            and t1.empno = t.empno),
14        (select sum(t1.income_amount)
15           from TAB_SAL t1
16          where t1.income_type = 44
17            and t1.empno = t.empno)
18 
19   from TAB_SAL t
20  group by t.empno
 1 select t.empno,
 2        (select t1.income_amount
 3           from TAB_SAL t1
 4          where t1.empno = t.empno
 5            and t1.income_type = 11),
 6        (select t1.income_amount
 7           from TAB_SAL t1
 8          where t1.empno = t.empno
 9            and t1.income_type = 22),
10        (select t1.income_amount
11           from TAB_SAL t1
12          where t1.empno = t.empno
13            and t1.income_type = 33),
14        (select t1.income_amount
15           from TAB_SAL t1
16          where t1.empno = t.empno
17            and t1.income_type = 44)
18 
19   from TAB_SAL t
20 
21  group by t.empno

一个加sum一个不加，结果都可以，我认为分组完毕后select后面只能出现每组最小维的一行数据，只要保证这个我觉得就可以。

 

posted @ 2018-07-27 17:26  明月镇魂  阅读(210)  评论(0)  编辑  收藏  举报