将SQL查询限制为每组仅前两个计数 [英] Limit SQL query to only the top two counts per group
本文介绍了将SQL查询限制为每组仅前两个计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如果我有一个名为FAVORITE_FLAVOR的数据库表,其中每一行都有用户喜欢的冰淇淋口味.
If I have a DB table called FAVORITE_FLAVOR where each row has a user's favorite flavor of ice cream.
User ID | Flavor | State
1 | Chocolate | CA
2 | Vanilla | ND
3 | Chocolate | CA
4 | Rocky Road | CA
5 | vanilla | CA
6 | Vanilla | CA
7 | Vanilla | CA
现在,如果我想查询每个州的两种最受欢迎的语言(规范化大小写和空格),我可以查询:
Now, if I want to query the 2 most popular flavors in each state (normalizing capitalization and whitespace), I could query:
SELECT state, INITCAP(TRIM(flavor)), count(INITCAP(TRIM(flavor))) AS total
FROM favorite_flavor GROUP BY state, INITCAP(TRIM(flavor))
ORDER BY state ASC, total DESC;
哪个返回:
CA | Vanilla | 3
CA | Chocolate | 2
CA | Rocky Road | 1
ND | Vanilla | 1
现在,我只想知道每个州的前2种口味.如何限制查询,以便不再列出Rocky Road.
Now, I only wanted to know the top 2 flavors per state. How do I limit the query so that Rocky Road is no longer listed.
推荐答案
SELECT
State,
flv,
total
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY state ORDER BY count(INITCAP(TRIM(flavor))) DESC ) RowNumber,
State,
INITCAP(TRIM(flavor)) flv,
count(INITCAP(TRIM(flavor))) total
FROM favorite_flavor
GROUP BY state, INITCAP(TRIM(flavor))
) dt
WHERE RowNumber <= 2
ORDER BY state ASC, total DESC
这篇关于将SQL查询限制为每组仅前两个计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文