现在我这有一张表，数据按每小时一条存放（比如是存雨量值）， 大于50的列出来。比如10-11-12时的雨量统计值，11-12-13时的雨量统计值，12-13-14时的雨量统计值。
这样该如何写？
------解决方案--------------------
可以提供表结构和一些测试数据吗，表结构可以简化为这样吗
日期 雨量
10-11-12 100
10-11-13 50
另外 10-11-12是指 10月11日12点吧
------解决方案--------------------
IF OBJECT_ID('Test') IS NOT NULL
DROP TABLE Test
GO
CREATE TABLE Test
(
ID INT PRIMARY KEY NOT NULL,
StationID VARCHAR(16),
DataTime DATETIME,
[Value] INT
)
INSERT INTO Test(ID,StationID,DataTime,[Value])
SELECT 1,'S1','2014-09-01 10:00:00',15 UNION ALL
SELECT 2,'S1','2014-09-01 11:00:00',10 UNION ALL
SELECT 3,'S1','2014-09-01 12:00:00',25 UNION ALL
SELECT 4,'S2','2014-09-01 10:00:00',35 UNION ALL
SELECT 5,'S2','2014-09-01 11:00:00',40 UNION ALL
SELECT 6,'S1','2014-09-02 12:00:00',55 UNION ALL
SELECT 7,'S2','2014-09-02 11:00:00',40
WITH t1 AS(
SELECT StationID,CONVERT(VARCHAR(10),DataTime,23) 'Date',SUM([Value]) 'SumRainfall'
FROM test GROUP BY StationID,CONVERT(VARCHAR(10),DataTime,23))
SELECT test.ID,test.StationID,test.dataTime,test.[Value] FROM test INNER JOIN t1 ON test.StationID = t1.StationID AND CONVERT(VARCHAR(10),test.DataTIme,23) = t1.Date
WHERE t1.SumRainfall >= 50
------解决方案--------------------
WITH 雨量表(StationID,[DateTime],Value) AS (
SELECT 1,Convert(DateTime,'2014-09-03 10:00',120),15 UNION ALL
SELECT 1,'2014-09-03 11:00',20 UNION ALL
SELECT 1,'2014-09-03 12:00',10 UNION ALL
SELECT 1,'2014-09-03 13:00',25
),
t AS (
SELECT t1.StationID,
t1.[DateTime] dt1, t1.Value v1,
t2.[DateTime] dt2, t2.Value v2,
t3.[DateTime] dt3, t3.Value v3
FROM 雨量表 t1
JOIN 雨量表 t2
ON t1.StationID = t2.StationID
AND DateDiff(hour,t1.[DateTime],t2.[DateTime]) = 1
JOIN 雨量表 t3
ON t1.StationID = t2.StationID
AND DateDiff(hour,t1.[DateTime],t3.[DateTime]) = 2
)
SELECT StationID,
Convert(varchar(10),dt1,120) Date,
Convert(varchar(2),dt1,114) + '-' +
Convert(varchar(2),dt2,114) + '-' +
Convert(varchar(2),dt3,114) Time,
v1+v2+v3 Value
FROM T
WHERE v1+v2+v3 > 50
StationID Date Time Value
----------- ---------- -------- -----------
1 2014-09-03 11-12-13 55