SQL子查询通过记录
问题描述:
这是一个简单的查询:SQL子查询通过记录
SELECT fDATE, fRAIN
FROM tRAIN
WHERE (fRAIN >=5)
检索查询可能是这样的:
现在,我想对应的时间越长,子集雨季,在这种情况下,日期为10月9日至13日。有没有办法做到这一点,而不通过记录集循环?
我感谢您的帮助。问候
答
我已经完成了任务,通过回答张贴在How to get count of consecutive dates
WITH Q4 AS (
SELECT Q3.fDate, Q3.RW
FROM
(SELECT
RW=ROW_NUMBER() OVER(PARTITION BY GRP ORDER BY fDate) ,fDate
FROM
(
SELECT
fDate, DATEDIFF(Day, '1900-01-01' , fDate)- ROW_NUMBER() OVER(ORDER BY
fDate) AS GRP
FROM (SELECT CONVERT(DATE,CONCAT(fYEAR , '/' , fMONTH , '/' ,fDAY),111) AS fDate,
fRAIN
FROM tRain
WHERE (fRain >= _value AND fStation = '_string' AND fYear = _value)
) Q1
) Q2
) Q3
)
SELECT (DATEADD(DAY,-Q4.RW+1,Q4.fDate)) AS Start_Date, Q4.fDate AS
Final_Date, Q4.RW as Season_Length
FROM Q4
WHERE Q4.RW= (SELECT TOP 1 MAX(Q4.RW) FROM Q4)
引导输出看起来像:
问候赛季
大小由计数确定连续几天? –
预期的结果是什么? – Ali
什么是您的数据库? – Turo