我有表MeteTableID nDate EnvirTemp MaxEnvirTemp
1 2009-04-03 00:01:00 2.3 5.4
2 2009-04-03 01:01:00 2.3 5.4
3 2009-04-03 01:03:00 2.4 5.2
4 2009-04-03 02:00:00 2.4 5.2
5 2009-04-03 03:00:00 2.3 5.2
6 2009-04-03 04:00:00 2.6 3.2
7 2009-04-04 13:00:00 2.6 3.2
..........................................................
表面存放着一个月的数据,现在我需要将表里的整点数据及对应日期的最大值查询出来,如
行:小时整点 20(前一日的20点) 21 22 23 0 1 2 3.... 19(本日19点) Max(EnvirTemp)列:
本月1日
本月2日
本月3日
.
.
.
.
.
.
本月31日如果没有数据的,插入空占行
请问如何实现这个查询,谢谢
1 2009-04-03 00:01:00 2.3 5.4
2 2009-04-03 01:01:00 2.3 5.4
3 2009-04-03 01:03:00 2.4 5.2
4 2009-04-03 02:00:00 2.4 5.2
5 2009-04-03 03:00:00 2.3 5.2
6 2009-04-03 04:00:00 2.6 3.2
7 2009-04-04 13:00:00 2.6 3.2
..........................................................
表面存放着一个月的数据,现在我需要将表里的整点数据及对应日期的最大值查询出来,如
行:小时整点 20(前一日的20点) 21 22 23 0 1 2 3.... 19(本日19点) Max(EnvirTemp)列:
本月1日
本月2日
本月3日
.
.
.
.
.
.
本月31日如果没有数据的,插入空占行
请问如何实现这个查询,谢谢
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,nDate DATETIME,EnvirTemp NUMERIC(2,1),MaxEnvirTemp NUMERIC(2,1))
INSERT INTO @T
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--SQL查询如下:DECLARE @dt VARCHAR(6)
SET @dt=(SELECT TOP 1 CONVERT(VARCHAR(6),nDate,112) FROM @T)SELECT
number,
(SELECT MAX(MaxEnvirTemp)
FROM @T
WHERE DAY(nDate)=number-1
AND DATEPART(hour,nDate)=20) AS [20],
(SELECT MAX(MaxEnvirTemp)
FROM @T
WHERE DAY(nDate)=number-1
AND DATEPART(hour,nDate)=21) AS [21],
(SELECT MAX(MaxEnvirTemp)
FROM @T
WHERE DAY(nDate)=number-1
AND DATEPART(hour,nDate)=22) AS [22],
(SELECT MAX(MaxEnvirTemp)
FROM @T
WHERE DAY(nDate)=number-1
AND DATEPART(hour,nDate)=23) AS [23],
pvt.*
FROM (
SELECT A.number,DATEPART(HOUR,nDate) AS hh,MaxEnvirTemp
FROM master.dbo.spt_values AS A
LEFT JOIN @T AS B
ON A.number=DAY(b.nDate)
WHERE A.number BETWEEN 1 AND DAY(DATEADD(day,-1,DATEADD(month,1,@dt+'01')))
AND A.type='p'
) AS A
PIVOT(MAX(MaxEnvirTemp) FOR hh IN([0],[1],[2],[3],[4],[5],
[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],
[16],[17],[18],[19])) AS pvt