表1:tjshuru 表2:Region使用如下语句:
select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth='200908' and t.EnergyType='WM' 查询结果如下:现在想把这条语句改为一个存储过程,加一个参数判断,判断“t.YearMonth='200908'”:1、参数“DAYear(2009)”和“DAMonth(09)”
2、判断,“DAMonth(09)”是否为空
3、为空的话,“t.YearMonth”等于“DAYear(2009)”参数
4、不为空的话,“t.YearMonth”等于“DAYear(2009)+DAMonth(09)”参数这样的存储过程应该怎样写呢?急用,谢谢了。
select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth='200908' and t.EnergyType='WM' 查询结果如下:现在想把这条语句改为一个存储过程,加一个参数判断,判断“t.YearMonth='200908'”:1、参数“DAYear(2009)”和“DAMonth(09)”
2、判断,“DAMonth(09)”是否为空
3、为空的话,“t.YearMonth”等于“DAYear(2009)”参数
4、不为空的话,“t.YearMonth”等于“DAYear(2009)+DAMonth(09)”参数这样的存储过程应该怎样写呢?急用,谢谢了。
表1:tjshuru
表2:Region
select ... where t.YearMonth = @DAYear(2009)
else
select ... where t.YearMonth = @DAYear(2009) + @DAMonth
select ... where t.YearMonth = @DAYear
else
select ... where t.YearMonth = @DAYear + @DAMonth
ALTER proc [dbo].[Sp_GetWaterData]
@DAYear char(4),
@DAMonth char(2)
as
declare @sql nvarchar(800)
if @DAMonth<>''
set @sql=' select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth='+ @DAYear +@DAMonth+' and t.EnergyType="WM"'
else
set @sql=' select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth Like '+ @DAYear+'% and t.EnergyType="WM"'
exec (@sql)
ALTER proc [dbo].[Sp_GetWaterData]
@DAYear char(4),
@DAMonth char(2)
as
declare @sql nvarchar(800)
if @DAMonth<>''
set @sql=' select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth='+ @DAYear +@DAMonth+' and t.EnergyType="WM"'
else
set @sql=' select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth Like '+ @DAYear+'% and t.EnergyType="WM"'
exec (@sql)
AS
BEGIN
DECLARE @DATE SYSNAME
SELECT @DATE = CASE WHEN @MONTH = '' THEN @YEAR ELSE @YEAR + @MONTH END
select r.RegionName , t.SumQty , YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth= @DATE and t.EnergyType='WM'
END
自己这样写的。ALTER proc [dbo].[Sp_GetWaterData]
@DAYear char(4),
@DAMonth char(2)
as
if @DAMonth is null
select r.RegionName , sum(t.SumQty) as SumQty, YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth like (@DAYear+'%') and t.EnergyType='WM'
else
select r.RegionName , t.SumQty as SumQty, YearMonth from tjshuru as t inner join Region as r on t.RegionNo=r.RegionNo where t.YearMonth=(@DAYear+@DAMonth) and t.EnergyType='WM'问题:“sum(t.SumQty)”提示错误,信息如下:
消息 8120,级别 16,状态 1,过程 Sp_GetWaterData,第 6 行
选择列表中的列 'Region.RegionName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。怎样根据“where t.YearMonth like (@DAYear+'%')”来对“t.SumQty”求和?
CREATE TABLE tjshuru
(
RegionNo varchar(4),
BuildingNo varchar(10),
MeterNo varchar(10),
EnergyType varchar(10),
YearMonth varchar(10),
SumQty float
)
INSERT INTO tjshuru
SELECT 'A','TJ0001','XDA001','WM','200401',197500.000000 UNION ALL
SELECT 'A','TJ0001','XDA001','WM','200402',197500.000000 UNION ALL
SELECT 'A','TJ0001','XDA001','WM','200403',197500.000000 UNION ALL
SELECT 'B','TJ0001','XDA001','WM','200404',197500.000000 UNION ALL
SELECT 'B','TJ0001','XDA001','WM','200405',197500.000000 UNION ALL
SELECT 'C','TJ0001','XDA001','WM','200909',297500.000000 UNION ALL
SELECT 'C','TJ0001','XDA001','WM','200409',197500.000000 UNION ALL
SELECT 'D','TJ0001','XDA001','WM','200901',197000.000000 UNION ALL
SELECT 'E','TJ0001','XDA001','WM','200909',197500.000000 UNION ALL
SELECT 'F','TJ0001','XDA001','WM','200901',197500.000000 CREATE TABLE Region
(
RegionNo varchar(4),
RegionName varchar(20),
RegionAilas varchar(20)
)
INSERT INTO Region
SELECT 'A','1小区','' UNION ALL
SELECT 'B','2小区','' UNION ALL
SELECT 'C','3小区','' UNION ALL
SELECT 'D','4小区','' UNION ALL
SELECT 'E','5小区','' UNION ALL
SELECT 'F','6小区',''SELECT * FROM tjshuru
SELECT * FROM Region
CREATE PROCEDURE GetSumNum
@DAYear varchar(4),
@DAMonth varchar(2)
AS
DECLARE @YearMonth varchar(10)
if @DAMonth is NULL
begin
SET @YearMonth=@DAYear
select r.RegionName , sum(t.SumQty) as SumQty, YearMonth from tjshuru t,Region r WHERE t.RegionNo=r.RegionNo and t.YearMonth like (@YearMonth+'%') and t.EnergyType='WM'
end
else
begin
SET @YearMonth=@DAYear+@DAMonth
select r.RegionName , t.SumQty as SumQty, YearMonth from tjshuru t,Region r WHERE t.RegionNo=r.RegionNo and t.YearMonth=@YearMonth and t.EnergyType='WM'
end
谢谢,但是有错啊。
选择列表中的列 'Region.RegionName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
如果改为,加上“ group by r.RegionName,t.YearMonth”:ALTER proc [dbo].[Sp_GetWaterData]
@DAYear varchar(4),
@DAMonth varchar(2)
AS
DECLARE @YearMonth varchar(10)
if @DAMonth is NULL
begin
SET @YearMonth=@DAYear
select r.RegionName , sum(t.SumQty) as SumQty, YearMonth from tjshuru t,Region r WHERE t.RegionNo=r.RegionNo and t.YearMonth like (@YearMonth+'%') and t.EnergyType='WM' group by r.RegionName,t.YearMonth
end
else
begin
SET @YearMonth=@DAYear+@DAMonth
select r.RegionName , t.SumQty as SumQty, YearMonth from tjshuru t,Region r WHERE t.RegionNo=r.RegionNo and t.YearMonth=@YearMonth and t.EnergyType='WM'
end 结果,没有“YourMonth”没有按照“2009”来统计:
) and t.EnergyType='WM'