对http://topic.csdn.net/u/20100117/20/b4264af7-ed0d-4171-806a-fdb0a9956cbf.html?seed=1140408712&r=62794205#r_62794205的补充
刚才把上贴给结了,但测试后发现,如果传入的@month为空时,及按年份统计时,里面的sum ennmd部分统计出错
说明:按年份或按月份统计,小弟已经在存储过程中作了判断,
麻烦高手们再帮小弟看看,再次谢过小弟实现的存储过程
CREATE PROCEDURE [dbo].[P_CompareWithOut]
@dscd VARCHAR(50),
@pointname VARCHAR(50),
@year1 INT,--起始年份 2007
@year2 INT,--终止年份 2009
@month VARCHAR(100),--月份字符串 '01,03,09,11',月份可为空,当月份为空时,直接生成年份
@ifshowPoint BIT--是否按监测点显示,1代表按站点显示,0代表按地区等显示,在地区编码长度为6时,自动按监测点显示
WITH
EXECUTE AS CALLER
AS
DECLARE @sql VARCHAR(MAX),@sqlrow VARCHAR(MAX)
SET @sqlrow=' '
--判断month的长度,如果为空,则按年份统计
IF(len(ltrim(rtrim(@month)))!=0)
BEGIN
--如果按监测点输出,则无需统计个数
if(@ifshowPoint=0)
begin
SELECT @sqlrow=@sqlrow+',['+date+'个数]=SUM(case sssj when '''+date+''' then 1 else 0 end),['+date+'总量]=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
else
begin
SELECT @sqlrow=@sqlrow+',['+date+'总量]=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
END
ELSE
BEGIN
--如果按监测点输出,则无需统计个数
if(@ifshowPoint=0)
begin
SELECT @sqlrow=@sqlrow+',['+date+'个数]=count(case left(sssj,4) when '''+date+''' then distinct [2210_dataEveryMonth].ennmcd else 0 end),['+date+'总量]=sum(case left(sssj,4) when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
else
begin
SELECT @sqlrow=@sqlrow+',['+date+'总量]=sum(case left(sssj,4) when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
END
IF(@ifshowPoint=1 OR len(@dscd)=6)
BEGIN
SET @sql='select ennm ,area ,city '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by area,city,ennm,dscd order by dscd'
END
ELSE
BEGIN
IF(len(@dscd)=2)
BEGIN
SET @sql='select isnull(area,''合计'') '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by area,left(dscd,4) order by left(dscd,4)'
END
ELSE
BEGIN
SET @sql='select isnull(city,''合计'') '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by city,dscd order by dscd'
END
END
--EXEC(@sql)
PRINT @sql
/*执行方法
EXEC dbo.P_CompareWithOut @dscd = '33', -- varchar(50)
@pointname = '', -- varchar(50)
@year1 = 2007, -- int
@year2 =2009, -- int
@month = '01,03,11,12', -- varchar(100)
@begin = '2008-01-17 08:04:22', -- varchar(50)
@end = '2010-01-17 08:04:22', -- varchar(50)
@ifshowPoint=1
*/
GO自定义函数:
CREATE FUNCTION [dbo].[f_generateDate] (@year1 INT, @year2 INT, @month VARCHAR (100))
RETURNS @temp TABLE (date VARCHAR (20))
AS
/*
用于同期比较时:生成比较的时间
@year1 起始年份 2007
@year1 结束年份 2009
@month 月份:格式(两位,不足两位用0补足,月份间用,隔开;01,08,11) 为空时生成年份
*/
BEGIN
DECLARE
@split VARCHAR (2),
@monthS VARCHAR (2),
@num VARCHAR (10),
@y INT SET @split = ',' --按逗号截取传入的月份,并按截取出来的月份循环
WHILE (CHARINDEX (@split, @month) <> 0)
BEGIN
SET @y = @year1 --从年的起始到结束时间进行循环
WHILE (@y <= @year2)
BEGIN
--月份为空时直接生成年份
IF(len(@month)!=0)
BEGIN
INSERT @temp (date)
VALUES (
CAST (@y AS VARCHAR)
+ '-'
+ SUBSTRING (@month, 1, CHARINDEX (@split, @month) - 1))
END
ELSE
BEGIN
INSERT @temp (date)
VALUES (
CAST (@y AS VARCHAR)
+ SUBSTRING (@month, 1, CHARINDEX (@split, @month) - 1))
END
SET @y = @y + 1
END SET @month =
STUFF (@month,
1,
CHARINDEX (@split, @month),
'')
END WHILE (@year1 <= @year2)
BEGIN
IF(len(@month)!=0)
BEGIN
INSERT @temp (date)
VALUES (CAST (@year1 AS VARCHAR) + '-' + @month)
END
ELSE
BEGIN
INSERT @temp (date)
VALUES (CAST (@year1 AS VARCHAR) + @month)
END
SET @year1 = @year1 + 1
END RETURN
END
GO
刚才把上贴给结了,但测试后发现,如果传入的@month为空时,及按年份统计时,里面的sum ennmd部分统计出错
说明:按年份或按月份统计,小弟已经在存储过程中作了判断,
麻烦高手们再帮小弟看看,再次谢过小弟实现的存储过程
CREATE PROCEDURE [dbo].[P_CompareWithOut]
@dscd VARCHAR(50),
@pointname VARCHAR(50),
@year1 INT,--起始年份 2007
@year2 INT,--终止年份 2009
@month VARCHAR(100),--月份字符串 '01,03,09,11',月份可为空,当月份为空时,直接生成年份
@ifshowPoint BIT--是否按监测点显示,1代表按站点显示,0代表按地区等显示,在地区编码长度为6时,自动按监测点显示
WITH
EXECUTE AS CALLER
AS
DECLARE @sql VARCHAR(MAX),@sqlrow VARCHAR(MAX)
SET @sqlrow=' '
--判断month的长度,如果为空,则按年份统计
IF(len(ltrim(rtrim(@month)))!=0)
BEGIN
--如果按监测点输出,则无需统计个数
if(@ifshowPoint=0)
begin
SELECT @sqlrow=@sqlrow+',['+date+'个数]=SUM(case sssj when '''+date+''' then 1 else 0 end),['+date+'总量]=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
else
begin
SELECT @sqlrow=@sqlrow+',['+date+'总量]=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
END
ELSE
BEGIN
--如果按监测点输出,则无需统计个数
if(@ifshowPoint=0)
begin
SELECT @sqlrow=@sqlrow+',['+date+'个数]=count(case left(sssj,4) when '''+date+''' then distinct [2210_dataEveryMonth].ennmcd else 0 end),['+date+'总量]=sum(case left(sssj,4) when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
else
begin
SELECT @sqlrow=@sqlrow+',['+date+'总量]=sum(case left(sssj,4) when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
end
END
IF(@ifshowPoint=1 OR len(@dscd)=6)
BEGIN
SET @sql='select ennm ,area ,city '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by area,city,ennm,dscd order by dscd'
END
ELSE
BEGIN
IF(len(@dscd)=2)
BEGIN
SET @sql='select isnull(area,''合计'') '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by area,left(dscd,4) order by left(dscd,4)'
END
ELSE
BEGIN
SET @sql='select isnull(city,''合计'') '+@sqlrow
--SELECT @sql=@sql+',['+date+']=sum(case sssj when '''+date+''' then isnull(qsl,0)/10000 else 0 end)' FROM dbo.f_generateDate(@year1,@year2,@month)
SET @sql=@sql+' from [2210_dataEveryMonth] inner join sxsqsk_import s on [2210_dataEveryMonth].ennmcd=s.ennmcd where dscd like ''%'+@dscd+'%'' and ennm like ''%'+@pointname+'%'' group by city,dscd order by dscd'
END
END
--EXEC(@sql)
PRINT @sql
/*执行方法
EXEC dbo.P_CompareWithOut @dscd = '33', -- varchar(50)
@pointname = '', -- varchar(50)
@year1 = 2007, -- int
@year2 =2009, -- int
@month = '01,03,11,12', -- varchar(100)
@begin = '2008-01-17 08:04:22', -- varchar(50)
@end = '2010-01-17 08:04:22', -- varchar(50)
@ifshowPoint=1
*/
GO自定义函数:
CREATE FUNCTION [dbo].[f_generateDate] (@year1 INT, @year2 INT, @month VARCHAR (100))
RETURNS @temp TABLE (date VARCHAR (20))
AS
/*
用于同期比较时:生成比较的时间
@year1 起始年份 2007
@year1 结束年份 2009
@month 月份:格式(两位,不足两位用0补足,月份间用,隔开;01,08,11) 为空时生成年份
*/
BEGIN
DECLARE
@split VARCHAR (2),
@monthS VARCHAR (2),
@num VARCHAR (10),
@y INT SET @split = ',' --按逗号截取传入的月份,并按截取出来的月份循环
WHILE (CHARINDEX (@split, @month) <> 0)
BEGIN
SET @y = @year1 --从年的起始到结束时间进行循环
WHILE (@y <= @year2)
BEGIN
--月份为空时直接生成年份
IF(len(@month)!=0)
BEGIN
INSERT @temp (date)
VALUES (
CAST (@y AS VARCHAR)
+ '-'
+ SUBSTRING (@month, 1, CHARINDEX (@split, @month) - 1))
END
ELSE
BEGIN
INSERT @temp (date)
VALUES (
CAST (@y AS VARCHAR)
+ SUBSTRING (@month, 1, CHARINDEX (@split, @month) - 1))
END
SET @y = @y + 1
END SET @month =
STUFF (@month,
1,
CHARINDEX (@split, @month),
'')
END WHILE (@year1 <= @year2)
BEGIN
IF(len(@month)!=0)
BEGIN
INSERT @temp (date)
VALUES (CAST (@year1 AS VARCHAR) + '-' + @month)
END
ELSE
BEGIN
INSERT @temp (date)
VALUES (CAST (@year1 AS VARCHAR) + @month)
END
SET @year1 = @year1 + 1
END RETURN
END
GO
['+date+'个数]=count(case left(sssj,4) when '''+date+''' then distinct [2210_dataEveryMonth].ennmcd else 0 end)
这里应改为
['+date+'个数]=sum(case left(sssj,4) when '''+date+''' then 1 else 0 end)
select
*
from
tb t
where
ennmcd=(select max(ennmcd) from tb where dscd=t.dscd)
select 'QSxsxcej0041B','f0621','2006-06',1 union all
select 'Qsxsxcej0f9B','f0621','2006-07',1 union all
select 'Qsxsxcej0352B','f0621','2006-05',36 union all
select 'QSxsxcej0195B','f0621','2007-08',67 union all
select 'Qsxsxcej0404B','f0621','2007-05',89 union all
select 'QSxsxcej0242B','f0621','2007-05',0.8 union all
select 'QSxsxcej0314B','f0651','2007-05',90 union all
select 'QSxsxcej0309B','f0651','2006-09',14 union all
select 'QSxsxcej0122B','f0651','2007-05',78
现在的结果
dscd 2006个数 2006 2007个数 2007
----- ----------- ---------------------------------------- ----------- ----------------------------------------
f0621 4 39.0 3 89.8
f0651 1 14 2 168.0
正确的
dscd 2006个数 2006 2007个数 2007
----- ----------- ---------------------------------------- ----------- ----------------------------------------
f0621 3 39.0 3 89.8
f0651 1 14 2 168.0
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-01-17 21:07:15
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ennmcd] varchar(13),[dscd] varchar(5),[sssj] varchar(7),[qsl] numeric(3,1))
insert [tb]
select 'QSxsxcej0041B','f0621','2006-05',1 union all
select 'QSxsxcej0041B','f0621','2006-06',1 union all
select 'Qsxsxcej0f9B','f0621','2006-07',1 union all
select 'Qsxsxcej0352B','f0621','2006-05',36 union all
select 'QSxsxcej0195B','f0621','2007-08',67 union all
select 'Qsxsxcej0404B','f0621','2007-05',89 union all
select 'QSxsxcej0242B','f0621','2007-05',0.8 union all
select 'QSxsxcej0314B','f0651','2007-05',90 union all
select 'QSxsxcej0309B','f0651','2006-09',14 union all
select 'QSxsxcej0122B','f0651','2007-05',78
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select dscd '
select @sql = @sql + ' , sum(case left(sssj,4) when ''' + left(sssj,4) + ''' then 1 else 0 end) [' + left(sssj,4) + '个数],
sum(case left(sssj,4) when ''' + left(sssj,4) + ''' then qsl else 0 end) [' + left(sssj,4) + '总数]'
from (select distinct sssj from tb) as a
set @sql = @sql + ' from ( select
*
from
tb t
where
ennmcd=(select max(ennmcd) from tb where sssj=t.sssj))t group by dscd'
exec(@sql)
----------------结果----------------------------
/*dscd 2006个数 2006总数 2006个数 2006总数 2006个数 2006总数 2006个数 2006总数 2007个数 2007总数 2007个数 2007总数
----- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- ---------------------------------------
f0621 3 38.0 3 38.0 3 38.0 3 38.0 2 156.0 2 156.0
f0651 1 14.0 1 14.0 1 14.0 1 14.0 0 0.0 0 0.0(2 行受影响)
*/
改成这样就可以了