题解|存疑#2021年国庆在北京接单3次及以上的统计信息#

# SELECT "北京" as city, ROUND(AVG(order_num), 3) as avg_order_num,
#     ROUND(AVG(income), 3) as avg_income
# FROM (
#     SELECT driver_id, COUNT(order_id) as order_num, SUM(fare) as income
#     FROM tb_get_car_order
#     JOIN tb_get_car_record USING(order_id)
#     WHERE city = "北京" and DATE_FORMAT(order_time,"%Y%m%d") BETWEEN '20211001' AND '20211007'
#     GROUP BY driver_id
#     HAVING COUNT(order_id) >= 3
# ) as t_driver_info;


SELECT '北京' AS city,ROUND(AVG(order_num),3) AS avg_order_num, ROUND(AVG(income),3) AS avg_income
FROM(
    SELECT driver_id,COUNT(a.order_id) AS order_num, SUM(fare) AS income
    FROM tb_get_car_order a
    JOIN tb_get_car_record b
    ON a.order_id = b.order_id
    WHERE DATE_FORMAT(order_time,'%Y-%m-%d') BETWEEN '2021-10-01' AND '2021-10-07' AND city = '北京'
    GROUP BY driver_id
    HAVING COUNT(a.order_id) >= 3   
)t1

这里有一些bug就是：如果在子查询SELECT中写入city，会因为driver_id,city和GROUP BY driver_id之间产生矛盾。
第一个解决方法是在第一个查询里直接SELECT ‘北京’ AS city （如上）

第二个解决方法是按照GROUP BY city,driver_id在子查询中进行分组之后，再次在最外层查询中使用GROUP BY city
进行分组 （如下）
SELECT city,ROUND(AVG(order_num),3) AS avg_order_num, ROUND(AVG(income),3) AS avg_income
FROM(
    SELECT city,driver_id,COUNT(a.order_id) AS order_num, SUM(fare) AS income
    FROM tb_get_car_order a
    JOIN tb_get_car_record b
    ON a.order_id = b.order_id
    WHERE DATE_FORMAT(order_time,'%Y-%m-%d') BETWEEN '2021-10-01' AND '2021-10-07' AND city = '北京'
    GROUP BY driver_id,city
    HAVING COUNT(a.order_id) >= 3   
)t1
GROUP BY city