请各位多多帮忙,是这样的,我通过查询语句
SELECT b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站名
对“一小时雨量”进行求和,查询统计出了以站名,起始时间,至,终止时间,雨量为列名的数据,然后我想将此数据中“雨量”的最大值的那一组查询出来,查询语句应如何写,不能按照“雨量”的值按倒序排列然后将第一行显示出来,因为雨量的最大值有可能不止一个,即不考虑
SELECT top 1 b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站
order by 雨量 desc谢谢大家!
SELECT b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站名
对“一小时雨量”进行求和,查询统计出了以站名,起始时间,至,终止时间,雨量为列名的数据,然后我想将此数据中“雨量”的最大值的那一组查询出来,查询语句应如何写,不能按照“雨量”的值按倒序排列然后将第一行显示出来,因为雨量的最大值有可能不止一个,即不考虑
SELECT top 1 b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站
order by 雨量 desc谢谢大家!
可以先查出每组最大值, 再和你的语句join.或用row_number
SELECT *
FROM
(
SELECT b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量,RANK()OVER (ORDER BY SUM(a.一小时雨量)) AS Flag
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站名
)X WHERE Flag = 1
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')and SUM(a.一小时雨量) = (select top 1 SUM(a.一小时雨量) from tabTimeData
where 日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00' group by 区站号
order by SUM(a.一小时雨量) desc) GROUP BY b.站名
SELECT top 1 b.站名, '2010-06-07 15:00' AS 起始时间, '至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM tabTimeData AS a INNER JOIN
tabAddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站
order by 雨量 desc
功能应该是差不多的吧,我不想这样考虑,因为是统计的雨量,所以很有可能有几个地方雨量是相同的,因而有可能有好几个最大的雨量总和出现
tabaddress
ID 区站号 站名
1 S6765 沐川利店镇
2 S6766 沐川舟坝镇
3 S6796 马边荣丁镇
4 S6797 马边苏坝镇
tabtimedata(特别说明:一小时雨量是以0.1为单位,即50则表示5.0毫米,8则表示0.8毫米)
ID 区站号 日期时间 一小时雨量
1 S6765 2010-6-7 16:00 50
2 S6766 2010-6-7 16:00 60
3 S6796 2010-6-7 16:00 65
4 S6797 2010-6-7 16:00 70
5 S6765 2010-6-7 17:00 5
6 S6766 2010-6-7 17:00 7
7 S6796 2010-6-7 17:00 15
8 S6797 2010-6-7 17:00 10
我想要达到的目的是,我已经通过联合查询能查询出下表(日期时间为2010-6-7 16:00到17:00)
区站号 站名 雨量
S6765 沐川利店镇 55
S6766 沐川舟坝镇 67
S6796 马边荣丁镇 80
S6797 马边苏坝镇 80
我想找出以上结果中的雨量最大的组,即以下两组,也有可能是因为数据不同只有一组最大,也有可能是多组,现在只是举个例子说明
S6796 马边荣丁镇 80
S6797 马边苏坝镇 80
请问应该如何写查询语句?
DECLARE @tabaddress TABLE (ID INT, [区站号] VARCHAR(10), [站名] NVARCHAR(100))
INSERT INTO @tabaddress VALUES (1, 'S6765', N'沐川利店镇')
INSERT INTO @tabaddress VALUES (2, 'S6766', N'沐川舟坝镇')
INSERT INTO @tabaddress VALUES (3, 'S6796', N'马边荣丁镇')
INSERT INTO @tabaddress VALUES (4, 'S6797', N'马边苏坝镇')DECLARE @tabtimedata TABLE (ID INT,[区站号] VARCHAR(10), [日期时间] DATETIME, [一小时雨量] INT)
INSERT INTO @tabtimedata VALUES (1, 'S6765', '2010-6-7 16:00', 50)
INSERT INTO @tabtimedata VALUES (2, 'S6766', '2010-6-7 16:00', 60)
INSERT INTO @tabtimedata VALUES (3, 'S6796', '2010-6-7 16:00', 65)
INSERT INTO @tabtimedata VALUES (4, 'S6797', '2010-6-7 16:00', 70)
INSERT INTO @tabtimedata VALUES (5, 'S6765', '2010-6-7 17:00', 5)
INSERT INTO @tabtimedata VALUES (6, 'S6766', '2010-6-7 17:00', 7)
INSERT INTO @tabtimedata VALUES (7, 'S6796', '2010-6-7 17:00', 15)
INSERT INTO @tabtimedata VALUES (8, 'S6797', '2010-6-7 17:00', 10)SELECT TOP 1 WITH TIES
b.站名, '2010-06-07 15:00' AS 起始时间, N'至' AS 至,
'2010-06-08 15:00' AS 终止时间, SUM(a.一小时雨量) * 0.1 AS 雨量
FROM @tabtimedata AS a INNER JOIN
@tabaddress AS b ON a.区站号 = b.区站号
WHERE (a.日期时间 BETWEEN '2010-6-7 16:00:00' AND '2010-6-8 15:00:00')
GROUP BY b.站名
ORDER BY 雨量 DESC