求一SQL语句 请各位高手指点
表
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的行。
最好还能有所有时间的次数之和 这一列 方便排序。没有也行。
[解决办法]
select name,
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
[解决办法]
- SQL code
select name,sum(a) 1月份,sum(b) 2月份,sum(c) 3月份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
[解决办法]
2樓錯誤啊！
[解决办法]
- SQL code
create table t (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 tgroup 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
[解决办法]
select name,
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
[解决办法]
try it ..
- SQL code
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