这是我的SQL Server表:
CREATE TABLE weather (id INT NOT NULL IDENTITY PRIMARY KEY, place varchar(100) NULL, description varchar(100) NULL);地点列包含城市名称。
描述包含天气描述(例如“晴天”)。
每个城市每小时插入多个值。 现在我想根据我收集的数据知道什么是“avarage”天气描述。
结果集可能如下所示:
place description --------------------- london sunny berlin rainy这是我的SQL小提琴示例数据: http : //sqlfiddle.com/#!6/ee736/2/0
我目前的陈述尚未完成:
SELECT w.place, w.description, COUNT(w.description) DESCRIPTION_COUNT FROM weather w GROUP BY w.place, w.description该陈述错过了对地点进行分组并找到描述的最大数量。 我猜它可以通过使用HAVING和subselects来解决。
This is my SQL Server table:
CREATE TABLE weather (id INT NOT NULL IDENTITY PRIMARY KEY, place varchar(100) NULL, description varchar(100) NULL);The place column contains a city name.
The description contains the weather description (e.g. "sunny").
Multiple values are inserted per hour per city. Now I want to know what is the "avarage" weather description, based on my collected data.
A result set could look like this:
place description --------------------- london sunny berlin rainyHere is my SQL fiddle with sample data: http://sqlfiddle.com/#!6/ee736/2/0
My current statement, which is not completed yet:
SELECT w.place, w.description, COUNT(w.description) DESCRIPTION_COUNT FROM weather w GROUP BY w.place, w.descriptionThis statement misses to group the place and find the maximum count on the description. I guess it can be solved by using HAVING and subselects.
最满意答案
你可以这样做
SELECT top(1) with ties w.place , w.description, COUNT(w.description) DESCRIPTION_COUNT from weather w group by w.place, w.description order by row_number() over (partition by place order by COUNT(w.description) desc);You can do it this way
SELECT top(1) with ties w.place , w.description, COUNT(w.description) DESCRIPTION_COUNT from weather w group by w.place, w.description order by row_number() over (partition by place order by COUNT(w.description) desc);更多推荐
发布评论