SQL查询按日期选择盛行风向
SQL查询按日期选择盛行风向
提问于 2015-08-21 14:58:06
我在Server 2012中有一个包含天气数据的表(每隔2分钟)
CREATE TABLE [dbo].[Test]
(
[SampleDateTime] [datetime] NULL,
[Unit ID] [nvarchar](4) NULL,
[WS Avg 2min] [float] NULL,
[WD Avg 2min] [float] NULL,
[WGS 10min] [float] NULL,
[WGD 10min] [float] NULL,
[Air Temp] [float] NULL,
[Rel Humidity] [float] NULL,
[Dew Point] [float] NULL,
[Pyranometer] [float] NULL,
[Quantum] [float] NULL,
[Air Pressure] [float] NULL,
[Snow Level] [float] NULL,
[Rainfall] [float] NULL,
[PW Current] [varchar](10) NULL,
[Visibility] [float] NULL,
[CBase 1] [float] NULL,
[CBase 2] [float] NULL,
[CBase 3] [float] NULL,
[Vert Vis] [float] NULL
) ON [PRIMARY]我正试着在一个月中的每一天得到流行的风向。所以基本上是每天的方向计数，返回最大计数。有了这个查询，我就接近了(从MS Access)
SELECT
Day([SampleDateTime]) AS [Date],
Round([WD Avg 2min],0) AS WindDir,
Count(Round([WD Avg 2min],0)) AS [Count]
FROM
WeatherData
WHERE
(((Year([SampleDateTime]) * 12 + DatePart("m", [SampleDateTime])) = Year(Date()) * 12 + DatePart("m", Date()) - 0))
GROUP BY
Day([SampleDateTime]), Round([WD Avg 2min],0)
ORDER BY
Day([SampleDateTime]), Count(Round([WD Avg 2min],0)) DESC;我得到了这个结果
Date WindDir Count
1 74 45
1 342 11
1 331 11
1 333 11
1 338 10
2 48 20
2 45 20
2 42 20
2 50 17我似乎无法获得查询返回相同的结果，但对于每个日期只有最大“计数”，如下所示
Date WindDir Count
1 74 45
2 48 20建议？
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-08-21 15:17:01
我将猜测您的第一个查询是正确的，并希望减少到第二个结果。
使用CTE Using Common Table Expressions
SQL FIDDLE
WITH month_direction as (
SELECT
Day([SampleDateTime]) AS [Date],
Round([WD Avg 2min],0) AS WindDir,
Count(Round([WD Avg 2min],0)) AS [Count]
FROM
WeatherData
WHERE
(((Year([SampleDateTime]) * 12 + DatePart("m", [SampleDateTime])) = Year(Date()) * 12 + DatePart("m", Date()) - 0))
GROUP BY
Day([SampleDateTime]), Round([WD Avg 2min],0)
ORDER BY
Day([SampleDateTime]), Count(Round([WD Avg 2min],0)) DESC
),
max_count as (
SELECT *, row_number() OVER (PARTITION BY date ORDER BY Count desc) as rnum
FROM month_direction
)
SELECT *
FROM max_count
WHERE rnum = 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/32143575
复制相关文章
相似问题
腾讯云开发者
