表a:
id No CName
1 AB0701-001 A
2 AB0701-002 B
3 AB0702-001 C
4 SE0702-001 D
5 AB0703-001 B
6 SE0702-002 FNo开头2个字母是代号,07是年份,01是月份,-后面的是业务顺序号(有空号)
要求统计:按代号统计每个代号每月的业务量;按月份统计每个月的业务量,即:
按代号:
代号 月份 业务量
AB 1 2
AB 2 1
AB 3 1
SE 2 2
按月份
月份 代号 业务量
1 AB 2
2 AB 1
SE 2
3 AB 1
按CName
CName 月份 业务量
A 1 1
id No CName
1 AB0701-001 A
2 AB0701-002 B
3 AB0702-001 C
4 SE0702-001 D
5 AB0703-001 B
6 SE0702-002 FNo开头2个字母是代号,07是年份,01是月份,-后面的是业务顺序号(有空号)
要求统计:按代号统计每个代号每月的业务量;按月份统计每个月的业务量,即:
按代号:
代号 月份 业务量
AB 1 2
AB 2 1
AB 3 1
SE 2 2
按月份
月份 代号 业务量
1 AB 2
2 AB 1
SE 2
3 AB 1
按CName
CName 月份 业务量
A 1 1
select 代号 = left(No,2),月份 = substring(No,5,2), 业务量 = count(1) from a group by
代号,月份 order by left(No,2),substring(No,5,2)
--按月份
select 月份 = substring(No,5,2),代号 = left(No,2), 业务量 = count(1) from a group by
月份, 代号 order by substring(No,5,2),left(No,2)
--按CName
select CName,月份 = substring(No,5,2),业务量 = count(1) from a group by CName,月份
order by CName,substring(No,5,2)
insert test select 1,'AB0701-001','A'
union all select 2,'AB0701-002','B'
union all select 3,'AB0702-001','C'
union all select 4,'SE0702-001','D'
union all select 5,'AB0703-001','B'
union all select 6,'SE0702-002','F'select 代号=left(No,2),月份=cast(substring(No,5,2) as int),业务量=count(*)
from test
group by left(No,2),cast(substring(No,5,2) as int)
order by 代号
goselect 月份=cast(substring(No,5,2) as int),代号=left(No,2),业务量=count(*)
from test
group by left(No,2),cast(substring(No,5,2) as int)
order by 月份
goselect CName,月份=cast(substring(No,5,2) as int),业务量=count(*)
from test
group by CName,cast(substring(No,5,2) as int)
order by CNamedrop table test代号 月份 业务量
---- ----------- -----------
AB 1 2
AB 2 1
AB 3 1
SE 2 2月份 代号 业务量
----------- ---- -----------
1 AB 2
2 AB 1
2 SE 2
3 AB 1CName 月份 业务量
---------- ----------- -----------
A 1 1
B 1 1
B 3 1
C 2 1
D 2 1
F 2 1
set nocount on
go
create table tmp_d (id int identity(1,1), No char(10), CName char(1))
go
insert into tmp_d (No,Cname) values ('AB0701-001','A')
insert into tmp_d (No,Cname) values ('AB0701-002','B')
insert into tmp_d (No,Cname) values ('AB0702-001','C')
insert into tmp_d (No,Cname) values ('SE0702-001','D')
insert into tmp_d (No,Cname) values ('AB0703-001','B')
insert into tmp_d (No,Cname) values ('SE0702-002','F')
go
create view vw_tmp as
select code = left(No,2),
yr = substring(No,3,2),
mn = substring(No,5,2),
No,
CName
from tmp_d
go
-- 按代号统计每个代号每月的业务量
select code, mn, cnt = count(1) from vw_tmp group by code, mn
-- 按月份统计每个月的业务量
select mn, code, cnt = count(1) from vw_tmp group by code, mn
-- 按CName统计每个月的业务量
select cName, mn, cnt = count(1) from vw_tmp group by cName, mn
go
drop view vw_tmp
drop table tmp_d
go
code,mn,cnt
AB,01,2
AB,02,1
SE,02,2
AB,03,1mn,code,cnt
01,AB,2
02,AB,1
02,SE,2
03,AB,1cName,mn,cnt
A,01,1
B,01,1
C,02,1
D,02,1
F,02,1
B,03,1