在存储过程中创建临时表来重构数据

在存储过程中创建临时表来重构数据

有下面两张表

这种情况下查询出来的结果按下面的形式显示:

ID Name Tm Score
001 小小 2011-1-1 90
001 小小 2011-2-1 80
002 小强 2011-1-5 75
这种结果是通过Tab_Score inner jion Tab_Students 来实现的，导致每个时间有一个考核分数，然后进行罗列显示。

然而有时客户希望得到下面的显示结果。

ID Name 年份 月份 上旬分数 中旬分数 下旬分数 全月分数

也就是每个月份有一个显示记录

作为一个和用户交互的、可分页存储过程一般包括下面几个输入参数：

 开始时间、结束时间、开始取值位置，结束取值位置、搜索条件、排序条件

下面是通过临时表来重构数据的一个查询：

复制代码
–参数
declare @startdate datetime=’2011-01-15 08:00:00.000’
declare @enddate datetime=’2012-10-22 08:00:00.000’

declare @startIndex int=1
declare @endIndex int=100

declare @conditionStr nvarchar(200)=’1<>0’
declare @orderStr nvarchar(200)=’ID desc’
–临时表
drop table #MyTable
create table #MyTable
(
ID nvarchar(50) not null,
Name nvarchar(50) not null,
Yr int not null,
Mth int not null,
UpScore int not null,
MiddleScore int not null,
DownScore int not null,
AllScore int not null,
)
–获取月初时间
set @startdate=DATEADD(MM,DATEDIFF(MM,0,@startdate),0)
set @enddate=DATEADD(MM,DATEDIFF(MM,0,@enddate), 0)
–向临时表添加数据
while @startdate<@enddate
begin
insert into #MyTable (ID,Name,Yr,Mth,UpScore,MiddleScore,DownScore,AllScore)
select Tab_Students.ID,
Tab_Students.Name,
DATEPART(YEAR,@startdate) as Yr,
DATEPART(MONTH,@startdate) as Mth,
(
select isnull(sum(Score),0) from Tab_Score
where Tab_Score.ID = Tab_Students.ID
and TM between @startdate and DATEADD(DAY,10,@startdate)
)as UpScore,
(
select isnull(sum(Score),0) from Tab_Score
where Tab_Score.ID = Tab_Students.ID
and TM between DATEADD(DAY,10,@startdate) and DATEADD(DAY,20,@startdate)
)as MiddleScore,
(
select isnull(sum(Score),0) from Tab_Score
where Tab_Score.ID = Tab_Students.ID
and TM between DATEADD(DAY,20,@startdate) and DATEADD(mm,1,@startdate)
)as DownScore,
(
select isnull(sum(Score),0) from Tab_Score
where Tab_Score.ID = Tab_Students.ID
and TM between @startdate and DATEADD(mm,1,@startdate)
)as AllScore
from Tab_Students
set @startdate=DATEADD(mm,1,@startdate)
end
–结果查询
declare @selectStr nvarchar(1000)
set @selectStr=’select * from #MyTable where ’ + @conditionStr +’ order by ‘+@orderStr
exec (@selectStr)
复制代码

复制代码
–结果表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[#MyTable]’) AND type in (N’U’))
DROP TABLE [dbo].[#MyTable]
create table #MyTable
(
DRP decimal(8,2),
TM datetime
)

declare @DRP decimal(8,2)

–查询时段：8:00 …..8:00 雨量统计算结束时间的8:00-8:00
while @startTime<@endTime
begin
select @DRP= isnull(sum(DRP),0) FROM ST_PPTN_R
where
STCD = @STCD and
TM >DATEADD(hh,-1,@startTime) and TM<=@startTime and –向前推一个小时到当前时间
STCD+’,’+cast(TM as Nvarchar) not in (select ST_PPTN_R_Add.STCD+’,’+cast(ST_PPTN_R_Add.TM as Nvarchar) from ST_PPTN_R_Add)

insert into #MyTable(DRP,TM)
values
(
   @DRP, 
   @startTime--降雨结束时间
)
set @startTime=DATEADD(hh,1,@startTime)  

end
select * from #MyTable
复制代码