多表查询优化需求：获取各城市最受欢迎餐厅

原查询存在的问题 #

1. 字段名错误：你的SQL中使用了restaurants.name和restaurants.restaurant_id，但实际表结构里，餐厅名称字段是restaurant_name，餐厅主键是id，关联条件应该是delivery_orders.restaurant_id = restaurants.id。
2. 冗余条件：HAVING COUNT(restaurants.name) > 0是多余的，因为INNER JOIN delivery_orders已经自动过滤掉没有订单的餐厅。
3. 未筛选城市最高订单数：原查询仅分组统计了所有有订单的餐厅，没有提取每个城市中订单数最多的餐厅，因此会返回该城市所有有订单的记录，导致城市名称重复。

解决方案 #

方法1：使用窗口函数（推荐，支持并列第一） #

通过RANK()窗口函数按城市分组，给每个城市的餐厅按订单数降序排名，最终筛选排名为1的记录：

WITH restaurant_order_stats AS (
    SELECT
        r.restaurant_name,
        c.city_name,
        COUNT(*) AS order_count,
        -- 按城市分组，订单数降序排名，并列的餐厅排名相同
        RANK() OVER (PARTITION BY c.city_name ORDER BY COUNT(*) DESC) AS rank
    FROM restaurants r
    INNER JOIN city c ON r.city_id = c.id
    INNER JOIN delivery_orders o ON o.restaurant_id = r.id
    GROUP BY r.restaurant_name, c.city_name
)
SELECT restaurant_name, city_name, order_count
FROM restaurant_order_stats
WHERE rank = 1;

如果不需要并列（仅取任意一个最高订单数的餐厅），可以把RANK()换成ROW_NUMBER()。

方法2：子查询关联（兼容旧版SQL） #

先计算每个餐厅的订单数，再找出每个城市的最大订单数，最后关联得到符合条件的餐厅：

SELECT
    r_stats.restaurant_name,
    c.city_name,
    r_stats.order_count
FROM (
    -- 统计每个餐厅的订单数
    SELECT
        r.city_id,
        r.restaurant_name,
        COUNT(*) AS order_count
    FROM restaurants r
    INNER JOIN delivery_orders o ON o.restaurant_id = r.id
    GROUP BY r.city_id, r.restaurant_name
) r_stats
INNER JOIN city c ON r_stats.city_id = c.id
INNER JOIN (
    -- 统计每个城市的最大订单数
    SELECT
        city_id,
        MAX(order_count) AS max_order_count
    FROM (
        SELECT
            r.city_id,
            COUNT(*) AS order_count
        FROM restaurants r
        INNER JOIN delivery_orders o ON o.restaurant_id = r.id
        GROUP BY r.city_id, r.restaurant_name
    ) t
    GROUP BY city_id
) city_max ON r_stats.city_id = city_max.city_id 
          AND r_stats.order_count = city_max.max_order_count;

测试结果（基于你的样本数据） #

执行上述查询后，会得到：

（注：New York和Miami的餐厅无订单记录，因此不会出现在结果中）

内容的提问来源于stack exchange，提问作者loli01