大家好,请问我想查出表A中字段B含有分别以2,3,45开头的总数怎样写,并按月份统计?
如:表A
B SAVETIME
232M 2010-01-17
2rr 2010-01-17
3dd 2010-01-17
3re 2010-01-17
45i 2010-01-17
45u 2010-01-17
45mm 2010-01-18
结果为:
2 2010-01-17 2
3 2010-01-17 2
45 2010-01-17 2
45 2010-01-18 1
如:表A
B SAVETIME
232M 2010-01-17
2rr 2010-01-17
3dd 2010-01-17
3re 2010-01-17
45i 2010-01-17
45u 2010-01-17
45mm 2010-01-18
结果为:
2 2010-01-17 2
3 2010-01-17 2
45 2010-01-17 2
45 2010-01-18 1
2 按照你的结果,SQL为:
select b,savetime,count(*) from a
where b like '2%' or b like '3%' or b like '45%'
group by b,savetime;你没有给出savetime的类型,如果savetime为date型,则须:
select b,to_char(savetime,'yyyy-mm-dd') savetime,count(*) from a
where b like '2%' or b like '3%' or b like '45%'
group by b,to_char(savetime,'yyyy-mm0dd');
group by b,savetime
union all
select '3' b,to_char(savetime,'yyyy-mm-dd') savetime,sum(decode(substr(b,1,1),'3',1)) total from a
group by b,savetime
union all
select '45' b,to_char(savetime,'yyyy-mm-dd') savetime,sum(decode(substr(b,1,2),'45',1)) total from a
group by b,savetime;有环境测测。写写睡觉,GAME OVER。
这段sql可以稍微变动一下,可能会更好
select substr(b,1,1) b,to_char(savetime,'yyyy-mm-dd') savetime,count(*) total from a
where substr(b,1,1) in ('2','3','45')
group by substr(b,1,1),savetime;
也想过这么做。奈何他要统计2,3,45,45不是substr(b,1,1),有点小郁闷咯,呵呵。
select case when substr(b,1,2) ='45' then substr(b,1,2) else substr(b,1,1) end b,to_char(savetime,'yyyy-mm-dd') savetime,count(*) total from a
where substr(b,1,1) in ('2','3') or substr(b,1,2) ='45'
group by substr(b,1,1),savetime;
2 union all select '2rr' B, '2010-01-17' SAVETIME from dual
3 union all select '3dd' B, '2010-01-17' SAVETIME from dual
4 union all select '3re' B, '2010-01-17' SAVETIME from dual
5 union all select '45i' B, '2010-01-17' SAVETIME from dual
6 union all select '45u' B, '2010-01-17' SAVETIME from dual
7 union all select '45m' B, '2010-01-17' SAVETIME from dual
8 union all select '46m' B, '2010-01-17' SAVETIME from dual)
9 select case when substr(b,1,1) in ('2','3') then substr(b,1,1) else substr(b,1,1)||'5' end b,savetime,count(*) total from a
10 where substr(b,1,1) in ('2','3') or substr(b,1,2) ='45'
11 group by substr(b,1,1),savetime;
B SAVETIME TOTAL
-- ---------- ----------
2 2010-01-17 2
3 2010-01-17 2
45 2010-01-17 3这个写的恶劣了,没办法
借花献佛,来个笨的。SELECT b,savetime,COUNT(*) FROM
(select case when substr(b,1,2) ='45' then substr(b,1,2) else substr(b,1,1) end b,
trunc(savetime) savetime
from test
where substr(b,1,1) in ('2','3') or substr(b,1,2) ='45')
group by b,savetime
ORDER BY 1;
(select substr(B,1,2) BB,SAVETIME from A where BB='45') group by BB,SAVETIME
create table test1(
B char(5),
tm datetime)
goinsert test1 values ('232M', '2010-01-17');
insert test1 values ('2rr', '2010-01-17');
insert test1 values ('3dd', '2010-01-17');
insert test1 values ('3re', '2010-01-17');
insert test1 values ('45i', '2010-01-17');
insert test1 values ('45u', '2010-01-17');
insert test1 values ('45mm', '2010-01-18');
goselect tim, datediff(dd, tm, getdate()), count(*) from
(select "tim" =
case
when substring(B, 1, 1) = '2' then '2'
when substring(B, 1, 1) = '3' then '3'
when substring(B, 1, 2) = '45' then '45'
end,
tm
from test1) as tmp_table
group by tim, datediff(dd, tm, getdate())
go
sybase语法,测试完毕。不太清楚如何得到当天0点时间(其实convert可以,但是应该有更方便的),自己修改吧。
稍微修改一下这里吧。
select BB,SAVETIME,count(*) from (
(select substr(B,1,1) BB,SAVETIME from A where substr(b,1,1)='2' or substr(b,1,1)='3')
union all
(select substr(B,1,2) BB,SAVETIME from A where substr(b,1,2)='45')
) group by BB,SAVETIME
WHEN substr(b, 1, 1) IN ('2', '3') THEN
substr(b, 1, 1)
ELSE
'45'
END),
trunc(tm),
COUNT(1) total
FROM test1
WHERE b >= '2' and b < '5'
GROUP BY substr(b, 1, 1), trunc(tm);