力扣中有很多解法用到了union all,有時候還挺好用

# 1555. 銀行賬戶概要

https://leetcode-cn.com/problems/bank-account-summary/

用戶表： Users
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| user_id | int |
| user_name | varchar |
| credit | int |
+--------------+---------+
user_id 是這個表的主鍵。
表中的每一列包含每一個用戶當前的額度信息。

交易表：Transactions
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| trans_id | int |
| paid_by | int |
| paid_to | int |
| amount | int |
| transacted_on | date |
+---------------+---------+
trans_id 是這個表的主鍵。
表中的每一列包含銀行的交易信息。
ID 為 paid_by 的用戶給 ID 為 paid_to 的用戶轉賬。

Create table If Not Exists Users (user_id int, user_name varchar(20), credit int)
Create table If Not Exists Transactions (trans_id int, paid_by int, paid_to int, amount int, transacted_on date)
Truncate table Users
insert into Users (user_id, user_name, credit) values ('1', 'Moustafa', '100')
insert into Users (user_id, user_name, credit) values ('2', 'Jonathan', '200')
insert into Users (user_id, user_name, credit) values ('3', 'Winston', '10000')
insert into Users (user_id, user_name, credit) values ('4', 'Luis', '800')
Truncate table Transactions
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('1', '1', '3', '400', '2020-08-01')
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('2', '3', '2', '500', '2020-08-02')
insert into Transactions (trans_id, paid_by, paid_to, amount, transacted_on) values ('3', '2', '1', '200', '2020-08-03')

寫一條 SQL 語句，查詢：
user_id 用戶 ID
user_name 用戶名
credit 完成交易后的余額
credit_limit_breached 檢查是否透支 （"Yes" 或 "No"）
以任意順序返回結果表。

查詢格式見如下示例：

Users 表：
+------------+--------------+-------------+
| user_id | user_name | credit |
+------------+--------------+-------------+
| 1 | Moustafa | 100 |
| 2 | Jonathan | 200 |
| 3 | Winston | 10000 |
| 4 | Luis | 800 |
+------------+--------------+-------------+

Transactions 表：
+------------+------------+------------+----------+---------------+
| trans_id | paid_by | paid_to | amount | transacted_on |
+------------+------------+------------+----------+---------------+
| 1 | 1 | 3 | 400 | 2020-08-01 |
| 2 | 3 | 2 | 500 | 2020-08-02 |
| 3 | 2 | 1 | 200 | 2020-08-03 |
+------------+------------+------------+----------+---------------+

結果表：
+------------+------------+------------+-----------------------+
| user_id | user_name | credit | credit_limit_breached |
+------------+------------+------------+-----------------------+
| 1 | Moustafa | -100 | Yes |
| 2 | Jonathan | 500 | No |
| 3 | Winston | 9900 | No |
| 4 | Luis | 800 | No |
+------------+------------+------------+-----------------------+
Moustafa 在 "2020-08-01" 支付了 400 并在 "2020-08-03" 收到了 200 ，當前額度 (100 -400 +200) = -100
Jonathan 在 "2020-08-02" 收到了 500 并在 "2020-08-08" 支付了 200 ，當前額度 (200 +500 -200) = 500
Winston 在 "2020-08-01" 收到了400 并在 "2020-08-03" 支付了 500 ，當前額度 (10000 +400 -500) = 9900
Luis 未收到任何轉賬信息，額度 = 800

方法1 自己寫的

select a1.user_id,a1.user_name,credit-credit_1+credit_2 as credit,
case when credit-credit_1+credit_2 <0 then"Yes"
else"No" end as credit_limit_breached from
(select user_id,user_name,credit,sum(ifnull(amount,0)) as credit_1
from users u left join Transactions t on u.user_id=t.paid_by
group by user_id,user_name,credit)a1
join 
(select user_id,user_name,sum(ifnull(amount,0)) as credit_2
from users u left join Transactions t on u.user_id=t.paid_to
group by user_id,user_name)a2
on a1.user_id=a2.user_id

方法2 union all

select u.user_id,user_name, sum(aa.credit) as credit,
case when sum(aa.credit) <0 then"Yes"
else"No" end as credit_limit_breached from
((select paid_by as user_id,-amount as credit
from Transactions)
union all
(select paid_to as user_id, amount 
from Transactions)
union all
(select user_id,credit from users)) aa
join users u on aa.user_id=u.user_id
group by u.user_id,user_name

1076. 項目員工II

編寫一個SQL查詢，報告所有雇員最多的項目。

Table: Project
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| project_id | int |
| employee_id | int |
+-------------+---------+
主鍵為 (project_id, employee_id)。
employee_id 是員工表 Employee 表的外鍵。

Table: Employee
+------------------+---------+
| Column Name | Type |
+------------------+---------+
| employee_id | int |
| name | varchar |
| experience_years | int |
+------------------+---------+
主鍵是 employee_id。

Create table If Not Exists Project (project_id int, employee_id int)
Create table If Not Exists Employee (employee_id int, name varchar(10), experience_years int)
Truncate table Project
insert into Project (project_id, employee_id) values ('1', '1')
insert into Project (project_id, employee_id) values ('1', '2')
insert into Project (project_id, employee_id) values ('1', '3')
insert into Project (project_id, employee_id) values ('2', '1')
insert into Project (project_id, employee_id) values ('2', '4')
Truncate table Employee
insert into Employee (employee_id, name, experience_years) values ('1', 'Khaled', '3')
insert into Employee (employee_id, name, experience_years) values ('2', 'Ali', '2')
insert into Employee (employee_id, name, experience_years) values ('3', 'John', '1')
insert into Employee (employee_id, name, experience_years) values ('4', 'Doe', '2')

查詢結果格式如下所示：

Project table:
+-------------+-------------+
| project_id | employee_id |
+-------------+-------------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 4 |
+-------------+-------------+

Employee table:
+-------------+--------+------------------+
| employee_id | name | experience_years |
+-------------+--------+------------------+
| 1 | Khaled | 3 |
| 2 | Ali | 2 |
| 3 | John | 1 |
| 4 | Doe | 2 |
+-------------+--------+------------------+

Result table:
+-------------+
| project_id |
+-------------+
| 1 |
+-------------+
第一個項目有3名員工，第二個項目有2名員工。

方法

select project_id from
(select project_id,
dense_rank()over(order by count(employee_id) desc) as dk 
from project
group by project_id) a 
where a.dk=1

注意窗口函數(shù)里邊要用聚合函數(shù)，需要group by