1、= 或 <> 不能 用于判断NULL值
2、使用 IS NULL 或 IS NOT NULL 判断NULL值
3、使用 安全等于<=> 判断 NULL 值,其中<=> 代表的是 判断 是否等于,<=>也可以用于判断数字
5、可以使用IFNULL函数将 字段中空值 变为 其他数值 ,然后进行数值间的比较
ifnull(exp1,exp2):若exp1不是NULL值,则ifnull(exp1,exp2)返回exp1;否则返回exp2
二、where 后面 能不能 接聚合函数
不能!不能!不能!
因为where的执行顺序优先于group by,而聚合函数是基于分组的,
having后面可以
三、LEECODE262.行程和用户
取消率 的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。
写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户(乘客和司机都必须未被禁止)的取消率。非禁止用户即 banned 为 No 的用户,禁止用户即 banned 为 Yes 的用户。
返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。
输入:
Trips 表:
+----+-----------+-----------+---------+---------------------+------------+
| id | client_id | driver_id | city_id | status | request_at |
+----+-----------+-----------+---------+---------------------+------------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
+----+-----------+-----------+---------+---------------------+------------+
Users 表:
+----------+--------+--------+
| users_id | banned | role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
输出:
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/trips-and-users
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
思路:
1、针对的是非禁止用户计算的,先筛选出所有非禁止用户。
(难点:users表既有trips表的client_d和driver_id)
2、根据trips表的status计算 "2013-10-01" 至 "2013-10-03" 期间的 被司机或乘客取消的生成的订单数量(即取消的订单数目) 和 用户生成的订单总数(即所有订单)。
代码一:不进行多表连接
select request_at as day,
round(sum(if(status != 'completed', 1, 0))/count(status),2) as 'cancellation rate'
from trips
where client_id not in (
select users_id
from users
where banned = 'yes')
and driver_id not in (
select user_id
from users
where banned = 'yes')
and request_at between '2013-10-01' and '2013-10-03'
group by request_at;
代码二:多表连接
select request_at as day,
round(sum(if(status!='completed', 1, 0))/count(status),2) as 'cancellation rate'
from trips t
join users u1 on t.client_id = u1.users_id and u1.banned = 'no'
join users u2 on t.driver_id = u2.users_id and u2.banned = 'no'
where request_at between '2013-10-01' and '2013-10-03'
group by request_at