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

with
    didList as (
        select
            driver_id as did
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
        where
            start_time is null
    ),
    dataList as (
        select
            driver_id,
            grade
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
        where
            driver_id in (
                select
                    did
                from
                    didList
            )
    )
select
    coalesce(driver_id,'总体') driver_id,
    round(avg(grade),1)
from
    dataList
where 
    grade is not null 
group by 
    driver_id 
    with rollup

本体难度不大，

步骤1：先求出grade=null的drive_id 的列表，然后将用户打车表和打车订单表通过order_id去连接。

步骤2：group by drive_id with rollup 注意必须要将drive_id进行一个null判断可以用ifnull或者coalesce