字段: id(pk) name(名称) area区域 value(值) time(记录时间)
1 a 市区 10 2009-2-5
2 a 市区 10 2009-2-5
3 a 市区 10 2009-2-5
4 a 市区 10 2009-11-25
5 b 郊区 20 2009-2-5
6 b 郊区 20 2009-2-5
7 b 郊区 20 2009-2-5
8 b 郊区 20 2009-11-25
9 c 市区 30 2009-2-5
10 c 市区 30 2008-1-5
11 c 市区 30 2009-1-5
12 c 市区 30 2009-11-25 a和b都属于同一个行业 以根据市区和郊区分组比较a和b并同比结果为:area区域 name(名称) 同比
市区 a (a至当前日期的今年销售累计/所有市区的今年销售累计)
郊区 b (b至当前日期的今年销售累计/所有郊区的今年销售累计)
1 a 市区 10 2009-2-5
2 a 市区 10 2009-2-5
3 a 市区 10 2009-2-5
4 a 市区 10 2009-11-25
5 b 郊区 20 2009-2-5
6 b 郊区 20 2009-2-5
7 b 郊区 20 2009-2-5
8 b 郊区 20 2009-11-25
9 c 市区 30 2009-2-5
10 c 市区 30 2008-1-5
11 c 市区 30 2009-1-5
12 c 市区 30 2009-11-25 a和b都属于同一个行业 以根据市区和郊区分组比较a和b并同比结果为:area区域 name(名称) 同比
市区 a (a至当前日期的今年销售累计/所有市区的今年销售累计)
郊区 b (b至当前日期的今年销售累计/所有郊区的今年销售累计)
name,
sum(case when datediff(day,[time],getdate())>=0 then [value] else 0 end)*1.0
/sum(case when area='市区' then [value] else 0 end ),
sum(case when datediff(day,[time],getdate())>=0 then [value] else 0 end)*1.0
/sum(case when area='郊区' then [value] else 0 end )
from tb
group by area,name
这样的吗>-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] datetime)
insert @TB
select 1,'a','市区',10,'2008-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2008-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'select [area], [name], CAST(SUM([value])/CAST((SELECT SUM([value]) FROM @TB WHERE T.NAME = NAME AND T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) AS NUMERIC(16,2)) AS 同比
from @TB t
GROUP BY [area], [name]
--测试结果:
/*
area name 同比
---- ---- ---------------------------------------
市区 a 1.33
郊区 b 1.33
市区 c 1.33(3 row(s) affected)
*/
declare @tb table (id int,name varchar(1),area varchar(4),value int,time datetime)
insert into @tb
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'select
a.area,a.name,
同比=cast(sum(a.[value])*1.0/sum(b.[value]) as decimal(10,2))
from @tb a,@tb b
where a.area=b.area --a.name=b.name and
and a.name in('a','b')
and datediff(day,a.time,getdate())>=0 and datediff(day,b.time,getdate())>=0
group by a.area,a.name
area name 同比
---- ---- ---------------------------------------
郊区 b 1.00
市区 a 0.50(2 行受影响)
a.area,a.name,
同比=cast(sum(a.[value])*1.0/sum(b.[value]) as decimal(10,2))
from @tb a,@tb b
where a.area=b.area --a.name=b.name and
and a.hy_id=1 --这边改一下就可以
and datediff(day,a.time,getdate())>=0 and datediff(day,b.time,getdate())>=0
group by a.area,a.name
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] datetime)
insert [tb]
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'
---查询---
select
a.name,
a.area,
sum(a.value) as value,
ltrim(cast(sum(a.value)*100.0/b.sumvalue as dec(18,2)))+'%' as 同比
from
tb a,
(select area,sum(value) as sumValue from tb group by area) b
where
a.area=b.area
group by
a.name,
a.area,
b.sumvalue
order by
name---结果---
name area value 同比
---- ---- ----------- -----------------------------------------
a 市区 40 25.00%
b 郊区 80 100.00%
c 市区 120 75.00%(所影响的行数为 3 行)
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] datetime)
insert @TB
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'
select [area], [name], CAST(SUM([value])/CAST((SELECT SUM([value]) FROM @TB WHERE T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) AS NUMERIC(16,2)) AS 同比
from @TB t
GROUP BY [area], [name]
--测试结果:
/*
area name 同比
---- ---- ---------------------------------------
郊区 b 1.00
市区 a 0.31
市区 c 0.92(3 row(s) affected)
*/
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] datetime)
insert @TB
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'
select [area], [name], SUM([value]) as 至当前日期的今年销售累计,
CAST((SELECT SUM([value]) FROM @TB WHERE T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) as 所有市区的今年销售累计,
CAST(SUM([value])/CAST((SELECT SUM([value]) FROM @TB WHERE T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) AS NUMERIC(16,2)) AS 同比
from @TB t
GROUP BY [area], [name]
ORDER BY [name]
--测试结果:
/*
area name 至当前日期的今年销售累计 所有市区的今年销售累计 同比
---- ---- ------------ --------------------------------------- ---------------------------------------
市区 a 40 130.00 0.31
郊区 b 80 80.00 1.00
市区 c 120 130.00 0.92(3 row(s) affected)
*/
--> 测试数据:@table
declare @table table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] varchar(10))
insert @table
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'select area,name,
convert(varchar(10),cast(sum(value)*100.0/(select sum(value) from @table) as dec)) + '%' as 同比
from @table
where name in('a','b')
and year(time) = '2009'
group by area,name--结果
-----------------------------
市区 a 17%
郊区 b 33%
唉, 这下总算对了吧
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] datetime)
insert @TB
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'
select [area], [name], SUM([value]) as 至当前日期的今年销售累计,
CAST((SELECT SUM([value]) FROM @TB WHERE T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) as 所有市区的今年销售累计,
CAST(SUM([value])/CAST((SELECT SUM([value]) FROM @TB WHERE T.AREA = AREA AND DATEDIFF(YY,[time],GETDATE())=0) AS NUMERIC(16,2)) AS NUMERIC(16,2)) AS 同比
from @TB t
WHERE DATEDIFF(YY,[time],GETDATE())=0
GROUP BY [area], [name]
ORDER BY [name]
--测试结果:
/*
area name 至当前日期的今年销售累计 所有市区的今年销售累计 同比
---- ---- ------------ --------------------------------------- ---------------------------------------
市区 a 40 130.00 0.31
郊区 b 80 80.00 1.00
市区 c 90 130.00 0.69(3 row(s) affected)
*/
---查询---
select
a.name,
a.area,
sum(a.value) as value,
ltrim(cast(sum(a.value)*100.0/b.sumvalue as dec(18,2)))+'%' as 同比
from
tb a,
(select area,sum(value) as sumValue from tb where datediff(yy,[time],getdate())=0 group by area) b
where
a.area=b.area
and
datediff(yy,[time],getdate())=0
group by
a.name,
a.area,
b.sumvalue
order by
name---结果---
name area value 同比
---- ---- ----------- -----------------------------------------
a 市区 40 30.77%
b 郊区 80 100.00%
c 市区 90 69.23%(所影响的行数为 3 行)
--> 测试数据:@table
declare @table table([id] int,[name] varchar(1),[area] varchar(4),[value] int,[time] varchar(10))
insert @table
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'select area,name,
convert(varchar(10),cast(sum(value)*100.0/(select sum(value) from @table where t.area = area) as dec)) + '%' as 同比
from @table t
where name in('a','b')
and year(time) = '2009'
group by area,name--结果
------------------------------------
郊区 b 100%
市区 a 25%
declare @tb table (id int,name varchar(1),area varchar(4),value int,time datetime)
insert into @tb
select 1,'a','市区',10,'2009-2-5' union all
select 2,'a','市区',10,'2009-2-5' union all
select 3,'a','市区',10,'2009-2-5' union all
select 4,'a','市区',10,'2009-11-25' union all
select 5,'b','郊区',20,'2009-2-5' union all
select 6,'b','郊区',20,'2009-2-5' union all
select 7,'b','郊区',20,'2009-2-5' union all
select 8,'b','郊区',20,'2009-11-25' union all
select 9,'c','市区',30,'2009-2-5' union all
select 10,'c','市区',30,'2008-1-5' union all
select 11,'c','市区',30,'2009-1-5' union all
select 12,'c','市区',30,'2009-11-25'
select
a.area,a.name,
同比=cast(sum(a.[value])*1.0/ b.[value] as decimal(10,2))
from @tb a ,
(
select area, [value]=sum([value]) from @tb
where datediff(day, time,getdate())>=0
group by area
)b
where a.area=b.area
and a.name in('a','b')
and datediff(day,a.time,getdate())>=0
group by a.area ,a.name,b.[value]
area name 同比
---- ---- ---------------------------------------
郊区 b 1.00
市区 a 0.25(2 行受影响)