从两张表获取count(*)最大值，按州查询分类最高商家数量

表结构假设 #

基于常见业务场景做合理假设，你可根据实际表字段调整：

• business表：包含state（州）、category_id（分类ID）、business_id（商家ID）字段
• category表：包含category_id（分类ID）、category_name（分类名称）字段

实现步骤与代码 #

1. 统计每个州下各分类的商家总数

关联两张表并分组，得到基础统计数据：

SELECT 
    b.state,
    c.category_name,
    COUNT(b.business_id) AS business_count
FROM business b
JOIN category c ON b.category_id = c.category_id
GROUP BY b.state, c.category_name

2. 找出每个州的商家数量最大值

基于第一步的结果，计算每个州的最高商家数：

WITH state_category_counts AS (
    SELECT 
        b.state,
        c.category_name,
        COUNT(b.business_id) AS business_count
    FROM business b
    JOIN category c ON b.category_id = c.category_id
    GROUP BY b.state, c.category_name
)
SELECT 
    state,
    MAX(business_count) AS max_business_count
FROM state_category_counts
GROUP BY state

3. 关联得到每个州对应最大值的分类和数量

将前两步结果关联，最终得到每个州中商家数量最多的分类及对应数量（若有多个分类并列第一，都会被列出）：

WITH state_category_counts AS (
    SELECT 
        b.state,
        c.category_name,
        COUNT(b.business_id) AS business_count
    FROM business b
    JOIN category c ON b.category_id = c.category_id
    GROUP BY b.state, c.category_name
),
state_max_counts AS (
    SELECT 
        state,
        MAX(business_count) AS max_business_count
    FROM state_category_counts
    GROUP BY state
)
SELECT 
    scc.state,
    scc.category_name,
    scc.business_count
FROM state_category_counts scc
JOIN state_max_counts smc ON scc.state = smc.state 
    AND scc.business_count = smc.max_business_count
ORDER BY scc.state;

如果你的实际表字段和假设不一致，替换对应字段名即可。

内容的提问来源于stack exchange，提问作者Hoang Pham Thanh