create table tb3 (id number , name varchar(20) ); insert into tb3 (ID, NAME) values (3, '分类3 ');insert into tb3 (ID, NAME) values (2, '分类2');insert into tb3 (ID, NAME) values (1, '分类1 ');create table tb4 (no number , id number, dt date ) ; insert into tb4 (NO, ID, DT, ROWID) values (4, 1, to_date('28-11-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAA');insert into tb4 (NO, ID, DT, ROWID) values (5, 2, to_date('13-10-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAB');insert into tb4 (NO, ID, DT, ROWID) values (3, 2, to_date('05-11-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAC');insert into tb4 (NO, ID, DT, ROWID) values (1, 1, to_date('01-12-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAD');insert into tb4 (NO, ID, DT, ROWID) values (2, 1, to_date('07-12-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAE'); with dd as (select name,yf,count(yf) tj from ( select tb3.name,to_char(tb4.dt ,'mm') as yf from tb3 left join tb4 on tb3.id=tb4.id
) ttt group by yf ,name ) select name , max(decode(yf,12,tj ,0) )十二月 , max(decode(yf,11,tj ,0) )十一月 , max(decode(yf,10,tj ,0) )十月 , max(decode(yf,09,tj ,0) )九月 , max(decode(yf,08,tj ,0) )八月 , max(decode(yf,07,tj ,0) )七月 , max(decode(yf,06,tj ,0) )六月 , max(decode(yf,05,tj ,0) )五月 , max(decode(yf,04,tj ,0) )四月 , max(decode(yf,03,tj ,0) )三月 , max(decode(yf,02,tj ,0) )二月 , max(decode(yf,01,tj ,0) ) 一月 from dd group by name order by name 最后那个不知道怎么实现
如果是这样 必须要用动态sql了 因为不确定当前是几月 所以标识字段别名做不到或者在程序里面写 会简单很多如果用存储过程 拿当前日期循环减一个月 拼接起来就行了 类似这样 别名用to_char(add_months(sysdate,-1),'mm')select name , sum(decode(to_char(rq,'yyyymm'),to_char(add_months(sysdate,-0),'yyyymm'),1,0)) "1", sum(decode(to_char(rq,'yyyymm'),to_char(add_months(sysdate,-1),'yyyymm'),1,0)) "2", ..... from tb group by name
根据代码名称分组,加上 利用case when 行转列。
具体sql自己写吧。
(id number ,
name varchar(20)
);
insert into tb3 (ID, NAME)
values (3, '分类3
');insert into tb3 (ID, NAME)
values (2, '分类2');insert into tb3 (ID, NAME)
values (1, '分类1
');create table tb4
(no number ,
id number,
dt date
) ;
insert into tb4 (NO, ID, DT, ROWID)
values (4, 1, to_date('28-11-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAA');insert into tb4 (NO, ID, DT, ROWID)
values (5, 2, to_date('13-10-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAB');insert into tb4 (NO, ID, DT, ROWID)
values (3, 2, to_date('05-11-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAC');insert into tb4 (NO, ID, DT, ROWID)
values (1, 1, to_date('01-12-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAD');insert into tb4 (NO, ID, DT, ROWID)
values (2, 1, to_date('07-12-2013', 'dd-mm-yyyy'), 'AAAR88AAEAAAASPAAE');
with dd as
(select name,yf,count(yf) tj from
(
select tb3.name,to_char(tb4.dt ,'mm') as yf from tb3 left join tb4 on tb3.id=tb4.id
) ttt
group by yf ,name
)
select name ,
max(decode(yf,12,tj ,0)
)十二月 ,
max(decode(yf,11,tj ,0)
)十一月 ,
max(decode(yf,10,tj ,0)
)十月 ,
max(decode(yf,09,tj ,0)
)九月 ,
max(decode(yf,08,tj ,0)
)八月 ,
max(decode(yf,07,tj ,0)
)七月 ,
max(decode(yf,06,tj ,0)
)六月 ,
max(decode(yf,05,tj ,0)
)五月 ,
max(decode(yf,04,tj ,0)
)四月 ,
max(decode(yf,03,tj ,0)
)三月 ,
max(decode(yf,02,tj ,0)
)二月 ,
max(decode(yf,01,tj ,0)
) 一月
from dd
group by name
order by name 最后那个不知道怎么实现
sum(decode(to_char(rq,'yyyymm'),to_char(add_months(sysdate,-0),'yyyymm'),1,0)) "1",
sum(decode(to_char(rq,'yyyymm'),to_char(add_months(sysdate,-1),'yyyymm'),1,0)) "2",
.....
from tb
group by name