34,593
社区成员
发帖
与我相关
我的任务
分享
------------------------------------------------------------------------
-- Author: happyflystone
-- Date : 2009-04-01 21:48:37
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
------------------------------------------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(ID INT,nDate DATETIME,EnvirTemp NUMERIC(2,1),MaxEnvirTemp NUMERIC(2,1))
Go
INSERT INTO ta
SELECT 1,'2009-04-03 00:01:00',2.3,5.4 UNION ALL
SELECT 2,'2009-04-03 01:01:00',2.3,5.4 UNION ALL
SELECT 3,'2009-04-03 01:03:00',2.4,5.2 UNION ALL
SELECT 4,'2009-04-03 02:00:00',2.4,5.2 UNION ALL
SELECT 5,'2009-04-03 03:00:00',2.3,5.2 UNION ALL
SELECT 6,'2009-04-03 04:00:00',2.6,3.2 UNION ALL
SELECT 7,'2009-04-04 13:00:00',2.6,3.2
GO
--Start
select top 24 rid= identity(int,-4,1) into # from sys.sysobjects
select top 30 rid= identity(int,0,1) into #2 from sys.sysobjects
select d,rid into #1
from #
, (
select convert(char(10),ndate,120) as d
from ta group by convert(char(10),ndate,120)
) a
declare @s varchar(8000)
select @s = isnull(@s+',','')
+'['+ ltrim(case when rid < 0 then 24+rid else rid end)+']=
max(case when datediff(hh,ndate,'''+
convert(char(19),dateadd(hh,rid,cast(d as datetime)),120)+
''') = 0 then EnvirTemp else 0 end)' from #1
exec('select convert(char(10),ndate,120) as rq,'+ @s+'
from ta
group by convert(char(10),ndate,120) ')
drop table #,#1,#2
--Result:
/*
rq 20 21 22 23 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
---------- ---------------------------------------
2009-04-03 0.0 0.0 0.0 0.0 2.3 2.4 2.4 2.3 2.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
2009-04-04 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.6 0.0 0.0 0.0 0.0 0.0 0.0
*/
--End