考慮這樣的表：
| 類別 | 子類 | 物品 |
|---|---|---|
| 富 | 蘋果 | i1 |
| 富 | 蘋果 | i2 |
| 富 | 蘋果 | i3 |
| 富 | 梨 | i4 |
| 富 | 梨 | i5 |
| 酒吧 | 黑莓 | i6 |
| 酒吧 | 藍莓 | i7 |
| 酒吧 | 藍莓 | i8 |
我想，對于每個category，獲得ssubcategory計數最高item的 。我不在乎物品的身份（甚至它們的數量）。所以，我希望最終的回報是
| 類別 | 子類 |
|---|---|
| 富 | 蘋果 |
| 酒吧 | 藍莓 |
我試過了
WITH pool AS (
SELECT
category,
subcategory,
COUNT(item) AS "itemCount"
FROM table
GROUP BY category, subcategory
),
maxItems AS (
SELECT
category,
MAX(subcategory), -- In real life, this is a numeric column
FROM pool
GROUP BY category
HAVING itemCount = MAX(itemCount)
)
-- and final query here filtered on the category -> subcategory mapping from above
但是 HAVING 陳述句錯誤
is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
作者當然這不是群分析研究。我不想按最大計數分組，我想按它過濾。
我可以使它與 maxItems 中的子查詢一起使用，將其更改為
maxItems AS (
SELECT
category,
MAX(subcategory), -- In real life, this is a numeric column
FROM pool
JOIN (
SELECT
subcategory,
MAX(itemCount) AS "itemCount"
FROM pool
GROUP BY subcategory
) AS "maxFilter"
ON rmCounts.subcategory = maxFilter.subcategory
AND maxFilter.itemCount = rmCounts.itemCount
GROUP BY category
)
但我真的覺得如果HAVING有效，它會更優雅，更清晰，我不明白為什么它不。
uj5u.com熱心網友回復：
您可以使用FIRST_VALUE()視窗函式來做到這一點：
SELECT DISTINCT Category,
FIRST_VALUE(Subcategory) OVER (PARTITION BY Category ORDER BY COUNT(*) DESC) Subcategory
FROM tablename
GROUP BY Category, Subcategory;
請參閱演示。
uj5u.com熱心網友回復：
這是一種處理關系的方法：
select * from (
select category,Subcategory,rank() over (partition by category order by count(*) desc) rn
from tablename
group by category,Subcategory
)t where rn = 1
db<>在這里擺弄
uj5u.com熱心網友回復：
這將獲得每個子類別的最高值，如果計數相等，則回傳兩個子類別：
select a.category, a.subcategory, itemcounts.total
from table a
cross apply ( select top 1 b.subcategory, count(b.item) as total
from table b
where b.category = a.category
group by b.subcategory
order by count(b.item) desc) itemcounts
group by a.category, a.subcategory, itemcounts.total
having count(a.item) = itemcounts.total
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/362298.html
標籤：sql sql-server 查询语句 通过...分组 窗函数