原文
http://community.csdn.net/Expert/topic/5480/5480106.xml?temp=.3789331
上次看了paoluo 的存储过程.我就改成了下面那个样子按月查询
ALTER PROCEDURE au_info_all
@time varchar(40),
@year varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NID As 河流编号'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [第' + Rtrim(ID)+ '次打分]' From
(Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = @time And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Month(RIV2_DATE) = @time and Year(RIV2_DATE) = @year) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所属镇, DateName(mm, RIV2_DATE) As 月份 From (Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = ' + @time + ' and Year(RIV2_DATE) ='+ @year+') As ID, * From PointInfo A Where month(RIV2_DATE) ='+ @time+') A Where Year(RIV2_DATE) ='+ @year+' Group By RIV_NID, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)
GO按年月查询
ALTER PROCEDURE YearSelect
@year varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NID As 河流编号 '
Select @S = @S + '' From
(Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Year(RIV2_DATE) = @year) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所属镇 From (Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Year(RIV2_DATE) = ' + @year + ') As ID, * From PointInfo A Where Year(RIV2_DATE) = ' + @year + ') A Where Year(RIV2_DATE) =' + @year + ' Group By RIV_NID, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)现在想按所属镇查询.写出来的存储过程就出问题了。alter PROCEDURE ALLSelect
@city varchar(40),
@year varchar(40),
@time varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NID As 河流编号'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [第' + Rtrim(ID)+ '次打分]' From
(Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = @time And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Month(RIV2_DATE) = @time and Year(RIV2_DATE) = @year and RIV2_EXAMINE=@city ) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所属镇, DateName(mm, RIV2_DATE) As 月份 From (Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = ' + @time + ' and Year(RIV2_DATE) ='+ @year+') As ID, * From PointInfo A Where month(RIV2_DATE) ='+ @time+') A Where Year(RIV2_DATE) ='+ @year+' Group By RIV_NID, DateName(mm, RIV2_DATE)' --加上AVG
EXEC(@S)
GO
Create Table 表
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
--插入數據
Insert 表 Select 'A', N'张山', 71, N'杨行镇', '2007-3-12 11:10:37'
Union All Select 'A', N'张山', 83, N'杨行镇', '2007-3-12 13:57:28'
Union All Select 'A', N'张山', 77, N'杨行镇', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'杨行镇', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦镇', '2007-3-23 14:41:35'
Union All Select 'B', N'张山', 73, N'杨行镇', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'杨行镇', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'杨行镇', '2007-3-27 13:19:12'
Union All Select 'C', N'王五', 92, N'杨行镇', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'杨行镇', '2007-3-27 13:23:16'
GO
@city varchar(40),
@year varchar(40),
@time varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NID As 河流编号'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [第' + Rtrim(ID)+ '次打分]' From
(Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = @time And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Month(RIV2_DATE) = @time and Year(RIV2_DATE) = @year ) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所属镇, DateName(mm, RIV2_DATE) As 月份 From (Select (Select Count(*) From PointInfo Where RIV_NID = A.RIV_NID And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = ' + @time + ' and Year(RIV2_DATE) ='+ @year+') As ID, * From PointInfo A Where month(RIV2_DATE) ='+ @time+') A Where Year(RIV2_DATE) ='+ @year+' and RIV2_EXAMINE='+@city+' Group By RIV_NID, DateName(mm, RIV2_DATE) ' --加上AVG
EXEC(@S)
GO我执行 exec ALLSelect '杨行镇','2007','04'
就会报列名 '杨行镇' 无效。
没有字段 列名 'RIV_NID' 无效能说详细点吗.谢谢了!
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
--插入數據
Insert PointInfo Select 'A', N'張山', 71, N'楊行鎮', '2007-3-12 11:10:37'
Union All Select 'A', N'張山', 83, N'楊行鎮', '2007-3-12 13:57:28'
Union All Select 'A', N'張山', 77, N'楊行鎮', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'楊行鎮', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦鎮', '2007-3-23 14:41:35'
Union All Select 'B', N'張山', 73, N'楊行鎮', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'楊行鎮', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'楊行鎮', '2007-3-27 13:19:12'
Union All Select 'C', N'王五', 92, N'楊行鎮', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'楊行鎮', '2007-3-27 13:23:16'
GO
--建立存儲過程
Create PROCEDURE ALLSelect
@city Varchar(40),
@year Varchar(40),
@time Varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME As 河流編號'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [第' + Rtrim(ID)+ '次打分]' From
(Select (Select Count(*) From PointInfo Where RIV_NAME = A.RIV_NAME And RIV2_EXAMINE = A.RIV2_EXAMINE And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = @time And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Month(RIV2_DATE) = @time and Year(RIV2_DATE) = @year and RIV2_EXAMINE=@city ) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所屬鎮, DateName(mm, RIV2_DATE) As 月份 From (Select (Select Count(*) From PointInfo Where RIV_NAME = A.RIV_NAME And RIV2_EXAMINE = A.RIV2_EXAMINE And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = ' + @time + ' and Year(RIV2_DATE) ='+ @year+') As ID, * From PointInfo A Where month(RIV2_DATE) ='+ @time+') A Where Year(RIV2_DATE) ='+ @year+' and RIV2_EXAMINE=''' + @city + ''' Group By RIV_NAME, DateName(mm, RIV2_DATE)'
EXEC(@S)
GO
--測試
EXEC ALLSelect N'楊行鎮', '2007', '3'
GO
--刪除測試環境
Drop Table PointInfo
Drop PROCEDURE ALLSelect
/*
河流編號 第1次打分 第2次打分 第3次打分 第4次打分 平均分 所屬鎮 月份
A 71 83 77 77 楊行鎮 三月
B 91 73 82 楊行鎮 三月
C 90 94 92 95 92 楊行鎮 三月
*/
(RIV_NAME Varchar(10),
Username Nvarchar(10),
RIV_NUM Int,
RIV2_EXAMINE Nvarchar(10),
RIV2_DATE DateTime)
--插入数据
Insert PointInfo Select 'A', N'张山', 71, N'杨行镇', '2007-3-12 11:10:37'
Union All Select 'A', N'张山', 83, N'杨行镇', '2007-3-12 13:57:28'
Union All Select 'A', N'张山', 77, N'杨行镇', '2007-3-12 13:58:08'
Union All Select 'B', N'李四', 91, N'杨行镇', '2007-3-23 13:52:14'
Union All Select 'B', N'李四', 80, N'月浦镇', '2007-3-23 14:41:35'
Union All Select 'B', N'张山', 73, N'杨行镇', '2007-3-23 15:05:05'
Union All Select 'C', N'王五', 90, N'杨行镇', '2007-3-27 13:13:11'
Union All Select 'C', N'王五', 94, N'杨行镇', '2007-3-27 13:19:12'
Union All Select 'C', N'王五', 92, N'杨行镇', '2007-3-27 13:19:36'
Union All Select 'C', N'王五', 95, N'杨行镇', '2007-3-27 13:23:16'
GO
--建立存储过程
Create PROCEDURE ALLSelect
@city Varchar(40),
@year Varchar(40),
@time Varchar(40)
AS
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME As 河流编号'
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else '''' End) As [第' + Rtrim(ID)+ '次打分]' From
(Select (Select Count(*) From PointInfo Where RIV_NAME = A.RIV_NAME And RIV2_EXAMINE = A.RIV2_EXAMINE And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = @time And Year(RIV2_DATE)=@year ) As ID, * From PointInfo A Where Month(RIV2_DATE) = @time and Year(RIV2_DATE) = @year and RIV2_EXAMINE=@city ) A Group By ID
Select @S = @S+ ' , AVG(RIV_NUM) As [平均分], Max(RIV2_EXAMINE) As 所属镇, DateName(mm, RIV2_DATE) As 月份 From (Select (Select Count(*) From PointInfo Where RIV_NAME = A.RIV_NAME And RIV2_EXAMINE = A.RIV2_EXAMINE And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = ' + @time + ' and Year(RIV2_DATE) ='+ @year+') As ID, * From PointInfo A Where month(RIV2_DATE) ='+ @time+') A Where Year(RIV2_DATE) ='+ @year+' and RIV2_EXAMINE=''' + @city + ''' Group By RIV_NAME, DateName(mm, RIV2_DATE)'
EXEC(@S)
GO
--测试
EXEC ALLSelect N'杨行镇', '2007', '3'
GO
--删除测试环境
Drop Table PointInfo
Drop PROCEDURE ALLSelect
/*
河流编号 第1次打分 第2次打分 第3次打分 第4次打分 平均分 所属镇 月份
A 71 83 77 77 杨行镇 三月
B 91 73 82 杨行镇 三月
C 90 94 92 95 92 杨行镇 三月
*/