力扣中有很多解法用到了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
注意窗口函数里边要用聚合函数，需要group by
