有两个表(abc)和(bm),字段如下:
表abc
abcid(编号) abcdq(地区) abcbm(部门) abctime(时间)
1 0101 100 2005-01-02
2 0102 100 2005-07-01
3 0103 200 2007-07-02
4 0101 300 2006-01-10
5 0102 400 2006-01-20
6 0101 100 2005-01-02
7 0102 300 2005-07-01
8 0103 400 2006-07-02
9 0101 300 2006-01-10
10 0102 300 2006-01-20
11 0101 100 2005-01-02
12 0102 400 2005-07-01
表bm
bmid bmnm ----注: bm.bmid=abc.abcbm
100 中部
200 北部
300 西部
400 南部
500 东部要求是:查某一地区(0101)某年(06)跟前一年(05)的总笔数的对比,及每部门在某年总笔数的百分比,以部门为组,查询出的ABCBM是以bm来取,因为考虑到ABC中可能没有某一部门的,如没有则值为0。其中地区跟某年是动态的
我要的结果是:abcbm 06 05 06部门所占百分比
100 0 3 0
200 0 0 0
300 3 1 60%
400 2 1 40%
500 0 0 0
合计 5 4 100%
不知各位有高效的SQL语句吗?存储过程也OK。谢谢各位啦,有效都有分!
表abc
abcid(编号) abcdq(地区) abcbm(部门) abctime(时间)
1 0101 100 2005-01-02
2 0102 100 2005-07-01
3 0103 200 2007-07-02
4 0101 300 2006-01-10
5 0102 400 2006-01-20
6 0101 100 2005-01-02
7 0102 300 2005-07-01
8 0103 400 2006-07-02
9 0101 300 2006-01-10
10 0102 300 2006-01-20
11 0101 100 2005-01-02
12 0102 400 2005-07-01
表bm
bmid bmnm ----注: bm.bmid=abc.abcbm
100 中部
200 北部
300 西部
400 南部
500 东部要求是:查某一地区(0101)某年(06)跟前一年(05)的总笔数的对比,及每部门在某年总笔数的百分比,以部门为组,查询出的ABCBM是以bm来取,因为考虑到ABC中可能没有某一部门的,如没有则值为0。其中地区跟某年是动态的
我要的结果是:abcbm 06 05 06部门所占百分比
100 0 3 0
200 0 0 0
300 3 1 60%
400 2 1 40%
500 0 0 0
合计 5 4 100%
不知各位有高效的SQL语句吗?存储过程也OK。谢谢各位啦,有效都有分!
a.bmid,
[06]=sum(case year(b.abctime) when '2006' then 1 else 0 end),
[05]=sum(case year(b.abctime) when '2005' then 1 else 0 end),
百分比=rtrim(sum(case year(b.abctime) when '2006' then 1 else 0 end)*100/(select count(1) from 表abc where year(abctime)=2006))+'%'
from
表bm a
left join
表abc b
on
a.bmid=b.abcbm
group by
a.bmid
with rollup
case t2.zbs when 0 then 0 else cast(CAST(t2.y06 * 100 as dec)/t2.zbs as varchar)+'%' end as 百分比
from bm t1 left join
(select abcbm,sum(case year(abctime) when 2006 then 1 end) as [y06],
sum(case year(abctime) when 2005 then 1 end) as [y05],
zbs = (select count(1) from abcbm where year(abctime) = 2006)
from abc
where year(abctime) = 2006 or year(abctime) = 2005
group by abcbm
)t2 on t1.bmid = t2.abcbm
insert into abc select 1 ,'0101',100,'2005-01-02'
insert into abc select 2 ,'0102',100,'2005-07-01'
insert into abc select 3 ,'0103',200,'2007-07-02'
insert into abc select 4 ,'0101',300,'2006-01-10'
insert into abc select 5 ,'0102',400,'2006-01-20'
insert into abc select 6 ,'0101',100,'2005-01-02'
insert into abc select 7 ,'0102',300,'2005-07-01'
insert into abc select 8 ,'0103',400,'2006-07-02'
insert into abc select 9 ,'0101',300,'2006-01-10'
insert into abc select 10,'0102',300,'2006-01-20'
insert into abc select 11,'0101',100,'2005-01-02'
insert into abc select 12,'0102',400,'2005-07-01'
create table bm(bmid int,bmnm varchar(8))
insert into bm select 100,'中部'
insert into bm select 200,'北部'
insert into bm select 300,'西部'
insert into bm select 400,'南部'
insert into bm select 500,'东部'
goselect
abcbm=isnull(cast(a.bmid as varchar(8)),'合计'),
[06] =sum(case year(b.abctime) when '2006' then 1 else 0 end),
[05] =sum(case year(b.abctime) when '2005' then 1 else 0 end),
百分比=rtrim(sum(case year(b.abctime) when '2006' then 1 else 0 end)*100/(select count(1) from abc where year(abctime)=2006))+'%'
from
bm a
left join
abc b
on
a.bmid=b.abcbm
group by
a.bmid
with rollup
go
/*
abcbm 06 05 百分比
-------- ----------- ----------- -------------
100 0 4 0%
200 0 0 0%
300 3 1 60%
400 2 1 40%
500 0 0 0%
合计 5 6 100%
*/drop table bm,abc
go
drop table abc
gocreate table abc
(
abcid int,
abcdq varchar(10),
abcbm int,
abctime datetime
)
insert into abc(abcid,abcdq,abcbm,abctime) values(1 ,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(2 ,'0102',100,'2005-07-01')
insert into abc(abcid,abcdq,abcbm,abctime) values(3 ,'0103',200,'2007-07-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(4 ,'0101',300,'2006-01-10')
insert into abc(abcid,abcdq,abcbm,abctime) values(5 ,'0102',400,'2006-01-20')
insert into abc(abcid,abcdq,abcbm,abctime) values(6 ,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(7 ,'0102',300,'2005-07-01')
insert into abc(abcid,abcdq,abcbm,abctime) values(8 ,'0103',400,'2006-07-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(9 ,'0101',300,'2006-01-10')
insert into abc(abcid,abcdq,abcbm,abctime) values(10,'0102',300,'2006-01-20')
insert into abc(abcid,abcdq,abcbm,abctime) values(11,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(12,'0102',400,'2005-07-01')
go
if object_id('pubs..bm') is not null
drop table bm
gocreate table bm
(
bmid int,
bmnm varchar(10)
)
insert into bm(bmid,bmnm) values(100,'中部')
insert into bm(bmid,bmnm) values(200,'北部')
insert into bm(bmid,bmnm) values(300,'西部')
insert into bm(bmid,bmnm) values(400,'南部')
insert into bm(bmid,bmnm) values(500,'东部')
goselect
abcbm = isnull(cast(a.bmid as varchar(4)),'合计'),
sum(case year(b.abctime) when '2006' then 1 else 0 end) as '06',
sum(case year(b.abctime) when '2005' then 1 else 0 end) as '05',
rtrim(sum(case year(b.abctime) when '2006' then 1 else 0 end)*100/(select count(1) from abc where year(abctime)=2006))+'%' as '06部门所占百分比'
from bm a,abc b
where a.bmid=b.abcbm
group by a.bmid
with rollupdrop table abc
drop table bmabcbm 06 05 06部门所占百分比
----- ----------- ----------- -------------
100 0 4 0%
200 0 0 0%
300 3 1 60%
400 2 1 40%
合计 5 6 100%(所影响的行数为 5 行)
drop table abc
gocreate table abc
(
abcid int,
abcdq varchar(10),
abcbm int,
abctime datetime
)
insert into abc(abcid,abcdq,abcbm,abctime) values(1 ,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(2 ,'0102',100,'2005-07-01')
insert into abc(abcid,abcdq,abcbm,abctime) values(3 ,'0103',200,'2007-07-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(4 ,'0101',300,'2006-01-10')
insert into abc(abcid,abcdq,abcbm,abctime) values(5 ,'0102',400,'2006-01-20')
insert into abc(abcid,abcdq,abcbm,abctime) values(6 ,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(7 ,'0102',300,'2005-07-01')
insert into abc(abcid,abcdq,abcbm,abctime) values(8 ,'0103',400,'2006-07-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(9 ,'0101',300,'2006-01-10')
insert into abc(abcid,abcdq,abcbm,abctime) values(10,'0102',300,'2006-01-20')
insert into abc(abcid,abcdq,abcbm,abctime) values(11,'0101',100,'2005-01-02')
insert into abc(abcid,abcdq,abcbm,abctime) values(12,'0102',400,'2005-07-01')
go
if object_id('pubs..bm') is not null
drop table bm
gocreate table bm
(
bmid int,
bmnm varchar(10)
)
insert into bm(bmid,bmnm) values(100,'中部')
insert into bm(bmid,bmnm) values(200,'北部')
insert into bm(bmid,bmnm) values(300,'西部')
insert into bm(bmid,bmnm) values(400,'南部')
insert into bm(bmid,bmnm) values(500,'东部')
goselect
abcbm = isnull(cast(a.bmid as varchar(4)),'合计'),
sum(case year(b.abctime) when '2006' then 1 else 0 end) as '06',
sum(case year(b.abctime) when '2005' then 1 else 0 end) as '05',
rtrim(sum(case year(b.abctime) when '2006' then 1 else 0 end)*100/(select count(1) from abc where year(abctime)=2006))+'%' as '06部门所占百分比'
from bm a left join abc b on a.bmid=b.abcbm group by a.bmid with rollupdrop table abc
drop table bmabcbm 06 05 06部门所占百分比
----- ----------- ----------- -------------
100 0 4 0%
200 0 0 0%
300 3 1 60%
400 2 1 40%
500 0 0 0%
合计 5 6 100%(所影响的行数为 6 行)
insert into abc select 1 ,'0101',100,'2005-01-02'
insert into abc select 2 ,'0102',100,'2005-07-01'
insert into abc select 3 ,'0103',200,'2007-07-02'
insert into abc select 4 ,'0101',300,'2006-01-10'
insert into abc select 5 ,'0102',400,'2006-01-20'
insert into abc select 6 ,'0101',100,'2005-01-02'
insert into abc select 7 ,'0102',300,'2005-07-01'
insert into abc select 8 ,'0103',400,'2006-07-02'
insert into abc select 9 ,'0101',300,'2006-01-10'
insert into abc select 10,'0102',300,'2006-01-20'
insert into abc select 11,'0101',100,'2005-01-02'
insert into abc select 12,'0102',400,'2005-07-01'
create table bm(bmid int,bmnm varchar(8))
insert into bm select 100,'中部'
insert into bm select 200,'北部'
insert into bm select 300,'西部'
insert into bm select 400,'南部'
insert into bm select 500,'东部'
godeclare @sql varchar(8000)
set @sql=''select
@sql=@sql+',['+rtrim(abctime)+']=sum(case year(b.abctime) when '+rtrim(abctime)+' then 1 else 0 end)'
from
(select distinct year(abctime) as abctime from abc where year(abctime) in(2006,2005)) t
order by
t.abctime descselect @sql='select abcbm=isnull(cast(a.bmid as varchar(8)),''合计'')'+@sql
+',百分比=rtrim(sum(case year(b.abctime) when '+rtrim(abctime)
+' then 1 else 0 end)*100/(select count(1) from abc where year(abctime)='
+rtrim(abctime)+'))+''%'''
+'from bm a left join abc b on a.bmid=b.abcbm group by a.bmid with rollup'
from
(select max(year(abctime)) as abctime from abc where year(abctime) in(2006,2005)) texec(@sql)
go/*
abcbm 06 05 百分比
-------- ----------- ----------- -------------
100 0 4 0%
200 0 0 0%
300 3 1 60%
400 2 1 40%
500 0 0 0%
合计 5 6 100%
*/drop table bm,abc
go
a.bmid,
sum(case year(b.abctime) when '2006' then 1 else 0 end) as '06',
sum(case year(b.abctime) when '2005' then 1 else 0 end) as '05',
百分比=rtrim(sum(case year(b.abctime) when '2006' then 1 else 0 end)*100/(select count(1) from 表abc where year(abctime)=2006))+'%'
from
表bm a
left join
表abc b
on
a.bmid=b.abcbm
group by
a.bmid
但我想把条件改成是某年某月的SUM跟前一年有这月的比较呢?即2006年7月跟2005年1月,abctime是smalldatetime型。后面你写的一会再详细看看,真不亏是高手!
某年某月的SUM跟前一年有这月的比较呢?即2006年7月跟2005年1月
insert abc
select 1 , '0101', '100', '2005-01-02'
union all select 2 , '0102', '100', '2005-07-01'
union all select 3 , '0103', '200', '2007-07-02'
union all select 4 , '0101', '300', '2006-01-10'
union all select 5 , '0102', '400', '2006-01-20'
union all select 6 , '0101', '100', '2005-01-02'
union all select 7 , '0102', '300', '2005-07-01'
union all select 8 , '0103', '400', '2006-07-02'
union all select 9 , '0101', '300', '2006-01-10'
union all select 10, '0102', '300', '2006-01-20'
union all select 11, '0101', '100', '2005-01-02'
union all select 12, '0102', '400', '2005-07-01'create table bm(bmid char(3), bmnm nvarchar(10))
insert bm select '100', '中部'
union all select '200', '北部'
union all select '300', '西部'
union all select '400', '南部'
union all select '500', '东部'select bm.bmid, isnull(tmp.[06], 0) as [06], isnull(tmp.[05], 0) as [05],
[06部门所占百分比]=case when tmp.total is null then 0 else cast(isnull(tmp.[06], 0) as decimal) / cast(tmp.total as decimal) end
from bm
left join
(
select abcbm,
[06]=sum(case when year(abctime)=2006 then 1 else 0 end),
[05]=sum(case when year(abctime)=2005 then 1 else 0 end),
total=count(*)
from abc
group by abcbm
)tmp on bm.bmid=tmp.abcbmdrop table abc
drop table bm
case t2.zbs when 0 then 0 else cast(CAST(t2.y06 * 100 as dec)/t2.zbs as varchar)+'%' end as 百分比
from bm t1 left join
(select abcbm,sum(case year(abctime) when 2006 then 1 end) as [y06],
sum(case year(abctime) when 2005 then 1 end) as [y05],
zbs = (select count(1) from abcbm where year(abctime) = 2006)
from abc
where year(abctime) = 2006 or year(abctime) = 2005
group by abcbm
)t2 on t1.bmid = t2.abcbm