现有表t1数据如下: t1: sid s_type s_value
s01 a 5
s01 b 1
s01 c 8
s01 d 4
s02 a 7
s02 b 6
s02 c 9
s02 d 7想得出如下结果: t2:
sid type_a type_b type_c_and_d
s01 5 1 12
s02 7 6 16
请问最优的SQL怎么写才好?
s01 a 5
s01 b 1
s01 c 8
s01 d 4
s02 a 7
s02 b 6
s02 c 9
s02 d 7想得出如下结果: t2:
sid type_a type_b type_c_and_d
s01 5 1 12
s02 7 6 16
请问最优的SQL怎么写才好?
select sid,
sum(decode(s_type,'a',s_value,0)) type_a
sum(decode(s_type,'b',s_value,0)) type_b
sum(decode(s_type,'c',s_value,0)) type_c
sum(decode(s_type,'d',s_value,0)) type_d
from t1
group by sid
select sid,
sum(decode(s_type,'a',s_value,0)) type_a
sum(decode(s_type,'b',s_value,0)) type_b
sum(decode(s_type,'c',s_value,'d',s_value,0)) type_c_and_d
from t1
group by sid
语句:
select t.sid ,
sum(case t.s_type when 'a' then t.s_value end) type_a ,
sum(case t.s_type when 'b' then t.s_value end) type_b ,
sum(case t.s_type when 'c' then t.s_value when 'd' then t.s_value end) type_o
from tes t
group by t.sid
; 查询结果:
s01 5 1 12
s02 7 6 16
spool d:\1.sql
declare
cursor distinct type_cur is
select s_type
from s_type;
begin
dbms_output.put_line('select sid');
for type_rec in type_cur
loop
dbms_output.put_line(',max(decode(t1.s_type,'''||type_rec.s_type||''' t1.s_value,null)) type_'||type_rec.s_type);
end loop;
dbms_output.put_line('from t1');
dbms_output.put_line('group by t.sid;');
end;
/
spool off
@d:\1.sql
如果标准的SQL的话,就用case when.
sum(decode(s_type,'a',s_value,0)) type_a,
sum(decode(s_type,'b',s_value,0)) type_b,
sum(decode(s_type,'c',s_value,'d',s_value,0)) type_c_and_d
from t1
group by sid不错,喜欢。。
sum(decode(s_type,'a',s_value,0)) type_a
sum(decode(s_type,'b',s_value,0)) type_b
sum(decode(s_type,'c',s_value,'d',s_value,0)) type_c_and_d
from t1
group by sid
---------------------------------------------------奇怪,我执行报错 未找到预期 FROM 关键字
select sid,
sum(decode(s_type,'a',s_value,0)) type_a,
sum(decode(s_type,'b',s_value,0)) type_b,
sum(decode(s_type,'c',s_value,'d',s_value,0)) type_c_and_d
from t1
group by sid
sum(case t.s_type when 'a' then t.s_value end) type_a ,
sum(case t.s_type when 'b' then t.s_value end) type_b ,
sum(case t.s_type when 'c' then t.s_value when 'd' then t.s_value end) type_o
from t1 t
group by t.sid
;
应该这个才是答案吧!
select sid,
sum(decode(s_type,'a',s_value,0)) type_a,
sum(decode(s_type,'b',s_value,0)) type_b,
sum(decode(s_type,'c',s_value,'d',s_value,0)) type_c_and_d
from t1
group by sid
不行!
我的数据库是teradata,所以只支持标准SQL,也就是说,只能用case then.
同时也赞同10楼的观点!我的确是应用于一个统计报表!!
再次谢谢大家~~
得不到想要的结果.
也许可发用DECODE,只是写错了吧!