实例说明
资料表结构(红色为主键)==
主表: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(+)