表
name time
a 2007-01-01
a 2007-02-09
b 2007-01-02
b 2007-01-01
c 2007-02-03
c 2007-02-01
d 2007-03-05
e 2007-05-09结果表
name 1月份 2月份 3月份
a 1次 1次 0次
b 2次 0次 0次
c 0次 2次 0次
d 0次 0次 1次
注意结果表没有e的行。
最好还能有所有时间的次数之和 这一列 方便排序。没有也行。
name time
a 2007-01-01
a 2007-02-09
b 2007-01-02
b 2007-01-01
c 2007-02-03
c 2007-02-01
d 2007-03-05
e 2007-05-09结果表
name 1月份 2月份 3月份
a 1次 1次 0次
b 2次 0次 0次
c 0次 2次 0次
d 0次 0次 1次
注意结果表没有e的行。
最好还能有所有时间的次数之和 这一列 方便排序。没有也行。
sum(decode(to_char(time,'MM'),'01',1,0))as '1月份' ,
sum(decode(to_char(time,'MM'),'02',1,0)) as '2月份' ,
sum(decode(to_char(time,'MM'),'03',1,0))as '3月份'
from tb groub by name
from(
select name,count(time) a,0 b,0 c
from t where to_char(time,'yyyymm') = '200701'
group by name
union all
select name,0 a,count(time) b,0 c
from t where to_char(time,'yyyymm') = '200702'
group by name
union all
select name,0 a,0 b,count(time) c
from t where to_char(time,'yyyymm') = '200703'
group by name
)
group by name看看行不
(bm varchar(20),
yf char(2),
zc varchar(20),
je NUMBER);
insert into t
(select '部门A',1,'内部',10 from DUAL)
union all
(select '部门A',1,'外部',15 from DUAL)
union all
(select '部门A',2,'内部',1 from DUAL)
union all
(select '部门B',1,'内部',10 from DUAL)
union all
(select '部门B',2,'内部',10 from DUAL)
union all
(select '部门B',3,'内部',10 from DUAL);select nvl(bm,'合计'),nvl(zc,'小计'),
sum(decode(yf,1,je,0)) as "1月",
sum(decode(yf,2,je,0)) as "2月",
sum(decode(yf,3,je,0)) as "3月",
sum(decode(yf,4,je,0)) as "4月",
sum(decode(yf,5,je,0)) as "5月",
sum(decode(yf,6,je,0)) as "6月",
sum(decode(yf,7,je,0)) as "7月",
sum(decode(yf,8,je,0)) as "8月",
sum(decode(yf,9,je,0)) as "9月",
sum(decode(yf,10,je,0)) as "10月",
sum(decode(yf,11,je,0)) as "11月",
sum(decode(yf,12,je,0)) as "12月",
sum(je) as "合计"
from t
group by rollup(bm,zc);
NVL(BM,'合计') NVL(ZC,'小计') 1月 2月 3月
---------------------------------------------------------------------------
部门A 内部 10 1 0
部门A 外部 15 0 0
部门A 小计 25 1 0
部门B 内部 10 10 10
部门B 小计 10 10 10
合计 小计 35 11 10
sum(decode(to_char(time,'MM'),'01',1,0)) Jan,
sum(decode(to_char(time,'MM'),'02',1,0)) Feb ,
sum(decode(to_char(time,'MM'),'03',1,0)) Mar
from temp1
group by name
having (sum(decode(to_char(time,'MM'),'01',1,0))
+sum(decode(to_char(time,'MM'),'02',1,0))
+sum(decode(to_char(time,'MM'),'03',1,0))
)>0
SQL:select tt.na,
sum(decode(to_char(tt.ti,'mm'),1,1,0)) as "1 month",
sum(decode(to_char(tt.ti,'mm'),2,1,0)) as "2 month",
sum(decode(to_char(tt.ti,'mm'),3,1,0)) as "3 month",
count(*) as "all_months"
from tablename tt
where tt.na <> 'e'
group by tt.na;
RESULT:
NA 1 month 2 month 3 month all_months
-- ---------- ---------- ---------- ----------
a 1 1 0 2
b 2 0 0 2
c 0 2 0 2
d 0 0 1 1
不过8楼的可能是看错了!结果没有‘e’是因为e的时间是5月没在统计范围内。
所以行的求和就不能是count(*)了。
还有就是如果按周汇总,你的decode就不能用了吧。
谁能知道怎么把列求和啊。这个我还没办法实现。