正文
MySql实例关于ifnull，count，case when，group by（转力扣简单）
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
给定表
customer
，里面保存了所有客户信息和他们的推荐人。
id | name | referee_id|
+------+------+-----------+
| 1 | Will | NULL |
| 2 | Jane | NULL |
| 3 | Alex | 2 |
| 4 | Bill | NULL |
| 5 | Zack | 1 |
| 6 | Mark | 2 |
注：-- 假如expr1不为NULL,则 IFNULL(expr1， expr2) 的返回值为expr1; 否则其返回值为 expr2
select name from customer where ifnull(referee_id,0) !=2
编写一个SQL查询，为下了 最多订单 的客户查找 customer_number 。测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。查询结果格式如下所示。
输入:
Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 3 |
+--------------+-----------------+
输出:
+-----------------+
| customer_number |
+-----------------+
| 3 |
+-----------------+
解释:
customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。
所以结果是该顾客的 customer_number ，也就是 3 。
注：-- 根据聚合函数排序
SELECT
customer_number
FROM
orders
GROUP BY
customer_number
ORDER BY
COUNT(customer_number) DESC
LIMIT 1
编写SQL查询以查找每个部门中薪资最高的员工。按 任意顺序 返回结果表。查询结果格式如下例所示。
输入：
Employee 表:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department 表:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
输出：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+
解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。-- in可以匹配多个值
select
b.name as Department ,
a.name as Employee ,
a.salary as Salary
from
Employee a
left join
Department b
on a.departmentId = b.id
where (a.departmentId , a.salary) in (select departmentId , max(salary) from Employee group by departmentId)
写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对
(actor_id, director_id)
ActorDirector 表：
+-------------+-------------+-------------+
| actor_id | director_id | timestamp |
+-------------+-------------+-------------+
| 1 | 1 | 0 |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 2 | 4 |
| 2 | 1 | 5 |
| 2 | 1 | 6 |
+-------------+-------------+-------------+
Result 表：
+-------------+-------------+
| actor_id | director_id |
+-------------+-------------+
| 1 | 1 |
+-------------+-------------+
唯一的 id 对是 (1, 1)，他们恰好合作了 3 次。
注：-- group by 一列就是把这一列相同的作为一组，多列就是多列相同的作为一组
select actor_id,director_id
from ActorDirector
group by actor_id, director_id
having count(*)>=3
返回的结果表单，以
travelled_distance
降序排列
，如果有两个或者更多的用户旅行了相同的距离, 那么再以
name
升序排列
。
Users 表：
+------+-----------+
| id | name |
+------+-----------+
| 1 | Alice |
| 2 | Bob |
| 3 | Alex |
| 4 | Donald |
| 7 | Lee |
| 13 | Jonathan |
| 19 | Elvis |
+------+-----------+
Rides 表：
+------+----------+----------+
| id | user_id | distance |
+------+----------+----------+
| 1 | 1 | 120 |
| 2 | 2 | 317 |
| 3 | 3 | 222 |
| 4 | 7 | 100 |
| 5 | 13 | 312 |
| 6 | 19 | 50 |
| 7 | 7 | 120 |
| 8 | 19 | 400 |
| 9 | 7 | 230 |
+------+----------+----------+
Result 表：
+----------+--------------------+
| name | travelled_distance |
+----------+--------------------+
| Elvis | 450 |
| Lee | 450 |
| Bob | 317 |
| Jonathan | 312 |
| Alex | 222 |
| Alice | 120 |
| Donald | 0 |
+----------+--------------------+
Elvis 和 Lee 旅行了 450 英里，Elvis 是排名靠前的旅行者，因为他的名字在字母表上的排序比 Lee 更小。
Bob, Jonathan, Alex 和 Alice 只有一次行程，我们只按此次行程的全部距离对他们排序。
Donald 没有任何行程, 他的旅行距离为 0。
注：order by travelled_distance desc,name ，一列travelled_distance倒序，一列正序
select
a.name,
ifnull(sum(b.distance),0) as travelled_distance
from Rides b
right join Users a
on b.user_id = a.id
group by b.user_id
order by travelled_distance desc,name
请写SQL查询出截至
2019-07-27
（包含2019-07-27），近
30
天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）
输入：
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 3 | 2 | 2019-07-21 | end_session |
| 4 | 3 | 2019-06-25 | open_session |
| 4 | 3 | 2019-06-25 | end_session |
+---------+------------+---------------+---------------+
输出：
+------------+--------------+
| day | active_users |
+------------+--------------+
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
+------------+--------------+
解释：注意非活跃用户的记录不需要展示。
select activity_date as day, count(distinct(user_id)) as active_users
from Activity
where activity_date between '2019-06-28' and '2019-07-27'
group by activity_date
注:
#这里如果改用datediff('2019-07-27', activity_date) < 30 要注意判断这样会算出2019-07-07往后30的数据
#count函数需要注意
#count(*)：统计记录总数，包含重复的记录，以及为NULL或空的记录。
#count(1)：根据第一列统计记录总数，包含重复的记录，包含为NULL或空的值。也可以使用count(2)
#count(列名)：根据指定的列统计记录总数，包含重复的记录，不包括NULL或空的值。
#count(distinct 列名)：根据指定的列统计记录总数，不包含重复的记录，不包括NULL或空的值。
写出一个SQL 查询语句，计算每个雇员的奖金。如果一个雇员的id是奇数并且他的名字不是以'M'开头，那么他的奖金是他工资的100%，否则奖金为0。
Employees 表:
+-------------+---------+--------+
| employee_id | name | salary |
+-------------+---------+--------+
| 2 | Meir | 3000 |
| 3 | Michael | 3800 |
| 7 | Addilyn | 7400 |
| 8 | Juan | 6100 |
| 9 | Kannon | 7700 |
+-------------+---------+--------+
输出：
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2 | 0 |
| 3 | 0 |
| 7 | 7400 |
| 8 | 0 |
| 9 | 7700 |
+-------------+-------+
select employee_id ,(CASE WHEN (employee_id % 2 )= 1 and name not like 'M%' THEN salary else 0 end) AS bonus
from Employees
order by employee_id
注：case when语句，判断奇数
