具体的交叉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(+)
楼上的我试了 不行啊! 能不能给我一个基于我给出表的sql查询语句啊!
--基于楼主的表 select time, (sum(case when id = "x1" then value else 0 end) as x1, sum(case when id = "x2" then value else 0 end) as x2 from tableName group by time
多了个括号 是这个select time, sum(case when id = "x1" then value else 0 end) as x1, sum(case when id = "x2" then value else 0 end) as x2 from t1 group by time
想来我还只会decode/case的方法。
如果值不确定,很多,就写proc吧
资料表结构(红色为主键)==>
主表: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(+)
不行啊!
能不能给我一个基于我给出表的sql查询语句啊!
select time,
(sum(case when id = "x1" then value else 0 end) as x1,
sum(case when id = "x2" then value else 0 end) as x2
from tableName
group by time
是这个select time,
sum(case when id = "x1" then value else 0 end) as x1,
sum(case when id = "x2" then value else 0 end) as x2
from t1
group by time
但是,现在问题出现变化。
查询后结果应为:
time x1_value x2_value
20051111 12 10
20051112 11 0----〉变成xxx(字符串而不是数值)
该怎么实现呢?
大家讨论一下。
sum后用转换函数不就可以了。to_char之类的
20051111 12 10
20051112 11 "null"
给个sql语句北。
sum(case when id = "x1" then value else null end) as x1,
sum(case when id = "x2" then value else null end) as x2
from t1
group by time