语句:
select sku_code,
sum(decode(to_char(to_date(t1.order_date),'mm'),'01',quantity,0)) sum_0401,
sum(decode(to_char(to_date(t1.order_date),'mm'),'02',quantity,0)) sum_0402,
sum(decode(to_char(to_date(t1.order_date),'mm'),'03',quantity,0)) sum_0403,
sum(decode(to_char(to_date(t1.order_date),'mm'),'04',quantity,0)) sum_0404
from (
select t2.sku_code,t2.quantity,t1.order_date
from t_order t1,t_orderline t2
where t1.id=t2.order_id
and t1.order_date>=to_date('2004.4.1','yyyy.mm.dd')
and t1.order_date<to_date('2004.5.1','yyyy.mm.dd')
) t1 group by sku_code;
动态行转列的解决方案:
写一个存储过程
根据数据,动态生成create view ..的语句
利用触发器进行动态调用SQL> select * from tbname;COL1 COL2 COL3
---------- ---------- ----------
a 1 123
a 2 23
a 3 33
b 5 456
b 3 756
c 66 3
c 67 3已选择7行。已用时间: 00: 00: 00.60
SQL> create or replace procedure p_createview(v_sql varchar2)
2 as
3 pragma autonomous_transaction; --注意,此部分为独立事务处理
4 begin
5 execute immediate v_sql;
6 end p_createview;
7 /过程已创建。已用时间: 00: 00: 01.02
SQL> create or replace trigger trg_tbname after insert or delete or update on tbname
2 declare
3 v_sql varchar2(4000):='create or replace view v_tbname as select col1,';
4 cursor cur_name is select distinct col2 from tbname;
5 begin
6 for c in cur_name loop
7 v_sql:=v_sql||'max(decode(col2,'||c.col2||',col3,null)) "'||c.col2||'",';
8 end loop;
9 v_sql:=substr(v_sql,1,length(v_sql)-1)||' from tbname group by col1';
10 p_createview(v_sql);
11 end trg_tbname;
12 /触发器已创建已用时间: 00: 00: 00.80
SQL> insert into tbname values('c',68,3);已创建 1 行。已用时间: 00: 00: 00.80
SQL> select * from v_tbname;COL1 1 2 3 5 66 67
---------- ---------- ---------- ---------- ---------- ---------- ----------
68
----------
a 123 23 33
b 756 456
c 3 3
3
已用时间: 00: 00: 00.50
SQL> OK!根据上面的方法,将存储过程修改一下即可。
select sku_code,
sum(decode(to_char(to_date(t1.order_date),'mm'),'01',quantity,0)) sum_0401,
sum(decode(to_char(to_date(t1.order_date),'mm'),'02',quantity,0)) sum_0402,
sum(decode(to_char(to_date(t1.order_date),'mm'),'03',quantity,0)) sum_0403,
sum(decode(to_char(to_date(t1.order_date),'mm'),'04',quantity,0)) sum_0404
from (
select t2.sku_code,t2.quantity,t1.order_date
from t_order t1,t_orderline t2
where t1.id=t2.order_id
and t1.order_date>=to_date('2004.4.1','yyyy.mm.dd')
and t1.order_date<to_date('2004.5.1','yyyy.mm.dd')
) t1 group by sku_code;
动态行转列的解决方案:
写一个存储过程
根据数据,动态生成create view ..的语句
利用触发器进行动态调用SQL> select * from tbname;COL1 COL2 COL3
---------- ---------- ----------
a 1 123
a 2 23
a 3 33
b 5 456
b 3 756
c 66 3
c 67 3已选择7行。已用时间: 00: 00: 00.60
SQL> create or replace procedure p_createview(v_sql varchar2)
2 as
3 pragma autonomous_transaction; --注意,此部分为独立事务处理
4 begin
5 execute immediate v_sql;
6 end p_createview;
7 /过程已创建。已用时间: 00: 00: 01.02
SQL> create or replace trigger trg_tbname after insert or delete or update on tbname
2 declare
3 v_sql varchar2(4000):='create or replace view v_tbname as select col1,';
4 cursor cur_name is select distinct col2 from tbname;
5 begin
6 for c in cur_name loop
7 v_sql:=v_sql||'max(decode(col2,'||c.col2||',col3,null)) "'||c.col2||'",';
8 end loop;
9 v_sql:=substr(v_sql,1,length(v_sql)-1)||' from tbname group by col1';
10 p_createview(v_sql);
11 end trg_tbname;
12 /触发器已创建已用时间: 00: 00: 00.80
SQL> insert into tbname values('c',68,3);已创建 1 行。已用时间: 00: 00: 00.80
SQL> select * from v_tbname;COL1 1 2 3 5 66 67
---------- ---------- ---------- ---------- ---------- ---------- ----------
68
----------
a 123 23 33
b 756 456
c 3 3
3
已用时间: 00: 00: 00.50
SQL> OK!根据上面的方法,将存储过程修改一下即可。
月份 kode sum
一共三列
select t2.sku_code,
sum(decode(to_char(t1.order_date,'yyyymm'), '200401', t2.quantity, 0)) sum_0401,
sum(decode(to_char(t1.order_date,'yyyymm'), '200402', t2.quantity, 0)) sum_0402,
sum(decode(to_char(t1.order_date,'yyyymm'), '200403', t2.quantity, 0)) sum_0403,
sum(decode(to_char(t1.order_date,'yyyymm'), '200404', t2.quantity, 0)) sum_0404
from t_order t1,t_orderline t2
where t1.id=t2.order_id
and t1.order_date>=to_date('2004.1.1','yyyy.mm.dd')
and t1.order_date<to_date('2004.5.1','yyyy.mm.dd')
group by t2.sku_code;