MySql實例關於ifnull，count，case when，group by（轉力扣簡單）
- 2022 年 6 月 2 日
- 筆記
給定表 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語句，判斷奇數
