SELECT city, category
FROM (
SELECT city, category, ROW_NUMBER() OVER(PARTITION BY city ORDER BY COUNT(*) DESC) AS row_num
FROM table_name
GROUP BY city, category
) AS subquery
WHERE row_num = 1;
其中,替换table_name为实际表的名称。 该查询使用ROW_NUMBER()和PARTITION BY子句计算每个城市中每个类别的出现次数,并返回每个城市中最具代表性的类别。
上一篇:编写SQL查询中的提取函数