SQL经典行转列

ID	NAME	SUBJECT	SCORE
1	张三	语文	90
2	张三	数学	80
3	李四	语文	99
4	李四	数学	78
5	张三	英语	89

现要转换成下表：

NAME	语文	数学	英语
张三	90	80	89
李四	99	78

这是一个典型的行转列过程，只需如下SQL即可：

select NAME, sum(decode(SUBJECT,'语文', SCORE,null)), sum(decode(SUBJECT,'数学', SCORE, null)), sum(decode(SUBJECT,'英语', SCORE, null)) from A  group by NAME;

关于Join：

1.inner join ：

1.  select * from A a, B b where a.id = b.aId 与  

2.  select * from A a inner join B b on a.id = b.aId 是一样的; 

2. left join 与left outer join：

1.  select * from A a left join B b on (a.id = b.aId); 

此时，不管B中有没有对应A的记录，都会查出A表中的所有记录。

关于Oracle数据库中行转列以及Join用法总结就介绍到这里了，希望本次的介绍能够对您有所收获！

Oracle数据库中行转列以及Join用法是本文我们主要要介绍的内容，我们知道，在Oracle中行转列，可以利用decode函数来实现。我们假设有以下的学生表A，如下所示：（接上）

 

 

 

表结构和数据如下（表名Test）：

NO VALUE  NAME
1       a       测试1
1       b       测试2
1       c       测试3
1       d       测试4
2       e       测试5
4      f        测试6
4       g       测试7

Sql语句：
select No,
       ltrim(max(sys_connect_by_path(Value,';')), ';') as Value,
       ltrim(max(sys_connect_by_path(Name, ';')),';') as Name
  from (select No,
              Value,
              Name,
              rnFirst,
              lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                      a.Value,
                      a.Name,
                      row_number() over(order by a.No, a.Value desc) rnFirst
                 from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;

检索结果如下：

NO VALUE    NAME
1    a;b;c;d   测试1;测试2;测试3;测试4
2   e            测试5
4    f;g          测试6;测试7

简单解释一下那个Sql吧：
1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：
select a.No,
       a.Value,
       a.Name,
       row_number() over(order by a.No, a.Valuedesc) rnFirst
  from Test a
该语句结果如下：
NO VALUE NAME RNFIRST
1     d       测试4     1
1     c       测试3     2
1     b       测试2     3
1     a       测试1     4
2     e       测试5     5
4     g       测试7     6
4     f       测试6     7

2、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：
select No,
       Value,
       Name,
       rnFirst,
       lead(rnFirst) over(partition by No orderby rnFirst) rnNext
  from (这里是tmpTable1的SQL)tmpTable1

lead(rnFirst)：取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序，并按No分区，分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示
该语句结果如下：
NO VALUE NAME RNFIRST RNNEXT
1     d        测试4    1         2
1     c        测试3     2         3
1     b        测试2    3         4
1     a        测试1    4         NULL
2     e        测试5    5         NULL
4     g        测试7    6         7
4     f         测试6    7         NULL

3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始，递归查找，
如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来，
大家可以先试试下面这个没有Max和Group的Sql：
select No,
       sys_connect_by_path(Value, ';') as Value,
       sys_connect_by_path(Name, ';') as Name
  from (select No,
              Value,
              Name,
              rnFirst,
              lead(rnFirst) over(partition by No order by rnFirst) rnNext
          from (select a.No,
                      a.Value,
                      a.Name,
                      row_number() over(order by a.No, a.Value desc) rnFirst
                 from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst

结果是：
NO VALUE       NAME
1    ;a            ;测试1
1     ;a;b        ;测试1;测试2
1     ;a;b;c     ;测试1;测试2;测试3
1     ;a;b;c;d  ;测试1;测试2;测试3;测试4
2    ;e            ;测试5
4    ;f             ;测试6
4    ;f;g          ;测试6;测试7

可以看到，每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max，再加个Group by No，得到的结果就是行转列的结果了
最后再加一个Ltrim，去掉最前面的那个分号，完成。