Sql以选择按一列分组的两个上的最大事件

Question

我有下表：

+----------+------+
| country  | event     |
+----------+-----------+
|   usa    | running   |
|   usa    | running   |
|   usa    | running   |
|  canada  | running   |
|  Canada  | running   |
|   usa    |  javline  |
|  canada  |  javline  |
|  canada  |  javline  |
|  canada  |  javline  |
+----------+-----------+

我想通过sql查询得到以下内容：

USA    |  Running |  3
Canada |  Javline |  3

我尝试在MS sql server上使用以下查询：

select country, case when c > 1 then null else event end event 
from (select country, [ModelName], recs, count(*) over (partition by event, recs ) c, 
row_number() over (partition by country order by recs desc) rn
from (select country, event, count(*) recs
from table
group by country, event)  )
where rn = 1
order by 1

但我得到了一个错误：

消息102，级别15，状态1，第12行
")"附近的语法不正确。

欢迎任何指向正确解决方案的指针。谢谢

Answer 1

您需要在子查询上加一个别名：

select 
country, 
case when c > 1 then null else event end event 
from (
select  -- No event here
country, 
[ModelName], 
recs, 
count(*) over (partition by event, recs ) c, 
row_number() over (partition by country order by recs desc) rn
from (
select country, event, count(*) recs -- No ModelName here
from [table]
group by country, event
) x -- You need to put an alias here
)t -- and here
where rn = 1
order by 1

请注意，上述查询仍然会产生错误：

列名"ModelName"无效。

列名"event"无效。

这是因为ModelName不包括在最内层的子查询中，而event不包括在外层的子查询。

---

根据您的样本数据，您可以使用此查询来获得所需的结果：

;WITH Cte AS(
SELECT Country, Event, COUNT(*) AS CC
FROM [Table]
GROUP BY Country, Event
)
,CteRowNumber AS(
SELECT *,
RN = ROW_NUMBER() OVER(PARTITION BY Country ORDER BY CC DESC)
FROM Cte
)
SELECT Country, Event, CC
FROM CteRowNumber
WHERE RN = 1

Answer 2

您可以在cte:中使用窗口函数

-- this counts a number per each country and event
with q as(
select country,event,
row_number() over(partition by country,event order by country) r
from your_table t
)
--this takes only the maximum of them
select * 
from q
where r=(select max(r) 
from q q2 
where q2.country=q.country)

结果：

| country |   event | r |
|---------|---------|---|
|  canada | javline | 3 |
|     usa | running | 3 |