oracle 交叉链接,Oracle里的交叉SQL语句写法-数据库专栏,ORACLE

实例说明

资料表结构(红色为主键)==>

主表:test_part_cost_tab(料号资料表)

part_no  varchar2(20)

part_name  varchar2(50)

从表:test_part_cost_dt_tab(料号成本资料表)

part_no varchar2(10)

cost_id varchar2(5)

cost number

数据==>

主表资料:

part_no  part_name

1 1000            name1000

2 1001            name1001

从表资料:

part_no cost_id  cost

1 1000        100              1.1

2 1000        200              1.2

3 1000        300              1.3

4 1000        321              1.321

5 1001        100              2.1

交叉资料==>

sql语句产生的结果

part_no  part_name cost_100 cost_200 cost_300 cost_321

1 1000          name1000       1.1                   1.2                 1.3        1.321

2 1001          name1001       2.1                   0                    0                0

具体的交叉sql语句写法:

select a.part_no,a.part_name,

–sum(b.cost)

sum(case when  b.cost_id = 100 then b.cost else 0 end) as cost_100,

sum(case when  b.cost_id = 200 then b.cost else 0 end) as cost_200,

sum(case when  b.cost_id = 300 then b.cost else 0 end) as cost_300,

sum(case when  b.cost_id = 321 then b.cost else 0 end) as cost_321

from test_part_cost_tab a,test_part_cost_dt_tab b

where a.part_no = b.part_no

group by a.part_no,a.part_name

ps: 若主表有资料,从表没有资料时,交叉后会没有相应的资料

解决办法是在where条件里用外连接

where a.part_no = b.part_no(+)

参考资料:

http://www.cnblogs.com/iouniuniu/archive/2004/04/05/5238.aspx  ==>此文的语法与本文的语法不同

http://it.icxo.com/htmlnews/2004/11/11/454293.htm

http://www.itpub.net/176727.html