题解 | #有取消订单记录的司机平均评分#

1. 2021年10月有过取消订单记录的司机的driver_id

select distinct driver_id
        from tb_get_car_order
        where date_format(order_time,'%Y-%m') = '2021-10' 
        AND isnull(start_time) = TRUE

2. 获取这些driver_id的打车订单表中有评价的订单明细

select *
    from tb_get_car_order 
    where driver_id 
    in (
        select distinct driver_id
        from tb_get_car_order
        where date_format(order_time,'%Y-%m') = '2021-10' 
        AND isnull(start_time) = TRUE
    ) and isnull(grade) = FALSE

3. 将这些数据汇总，使用 group by with rollup来获得统计数据

select 
    ifnull(driver_id,'总体') as driver_id,
    round(avg(grade),1) as avg_grade
    from tb_get_car_order 
    where driver_id 
    in (
        select distinct driver_id
        from tb_get_car_order
        where date_format(order_time,'%Y-%m') = '2021-10' 
        AND isnull(start_time) = TRUE
    ) and isnull(grade) = FALSE
    group by driver_id with ROLLUP