sql 牛客网 175题

https://www.nowcoder.com/practice/f022c9ec81044d4bb7e0711ab794531a?tpId=268&tags=&title=&difficulty=0&judgeStatus=0&rp=0&sourceUrl=%2Fexam%2Fintelligent%3FquestionJobId%3D10%26tagId%3D21003

题本身的思路并不难，我的方法是采用union 连接两个查询

( select driver_id, round(avg(grade), 1) as avg_grade 
from tb_get_car_order 
where driver_id in ( 
select driver_id
from tb_get_car_order 
where start_time is null 
and date_format(order_time, "%Y%m") ='202110' 
)
group by driver_id 
order by driver_id 
) 
union 
( 
select '总体' as driver_id,
round(avg(grade), 1) as avg_grade
from tb_get_car_order
where driver_id in (
    select driver_id 
    from tb_get_car_order
    where start_time is null 
    and date_format(order_time, '%Y%m') = '202110'
) ) 

看了评论区的题解后发现另一种解法：

select coalesce(driver_id, '总体'), 
round(avg(grade), 1) as avg_grade 
from tb_get_car_order 
where driver_id in 
(
select driver_id 
from tb_get_car_order 
where date_format(order_time, '%Y%m')='202110' and start_time is null 
) 
group by driver_id 
with rollup;

　　coalesce 函数会将第一个为null的数替换成 总体

colaesce(driver_id, '总体') 本身不会计算总体的平均分，实际上可以计算出总体平均分是因为使用了 with rollup 关键字

rollup 在分组之后会计算出一个总体的平均分，显示为null 

driver_id | avg_grade
----------|----------
1 | 4.5
2 | 5.5
3 | 4.0
NULL | 4.6

所以当 sql 查询的时候使用 coalesce(driver_id, 总体)的时候 函数会将 null替换为总体

driver_id | avg_grade
----------|----------
1 | 4.5
2 | 5.5
3 | 4.0
总体 | 4.6