sql力扣刷题六

1164. 指定日期的产品价格

Create table If Not Exists Products (product_id int, new_price int, change_date date)
Truncate table Products
insert into Products (product_id, new_price, change_date) values ('1', '20', '2019-08-14')
insert into Products (product_id, new_price, change_date) values ('2', '50', '2019-08-14')
insert into Products (product_id, new_price, change_date) values ('1', '30', '2019-08-15')
insert into Products (product_id, new_price, change_date) values ('1', '35', '2019-08-16')
insert into Products (product_id, new_price, change_date) values ('2', '65', '2019-08-17')
insert into Products (product_id, new_price, change_date) values ('3', '20', '2019-08-18')
1
2
3
4
5
6
7
8

产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
这张表的主键是 (product_id, change_date)。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
1
2
3
4
5
6
7
8
9

写一段 SQL来查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以任意顺序返回结果表。

查询结果格式如下例所示。

示例 1:

输入：
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
输出：
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

题解一

select p1.product_id, ifnull(p2.new_price, 10) as price
from (
    select distinct product_id
    from products
) as p1 -- 所有的产品
left join (
    select product_id, new_price 
    from products
    where (product_id, change_date) in (
        select product_id, max(change_date)
        from products
        where change_date <= '2019-08-16'
        group by product_id
    )
) as p2 -- 在 2019-08-16 之前有过修改的产品和最新的价格
on p1.product_id = p2.product_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

题解二

with temp as 
(
    select distinct product_id from Products
)

select a.product_id, ifnull(b.new_price, 10) as price from temp a
left join
(select *, rank() over(partition by product_id order by change_date DESC) as "rk" from Products where change_date<="2019-08-16") b
on a.product_id = b.product_id
where b.rk = 1 or b.rk is NULL
1
2
3
4
5
6
7
8
9
10

题解三

select
    product_id,
    price
from (
    select
        product_id,
        new_price as price,
        rank() over(partition by product_id order by change_date desc) as rk
    from Products
    where change_date <= '2019-08-16'
) t1 where rk = 1
union
select
    product_id,
    10 as price
from Products
group by product_id
having(min(change_date) > '2019-08-16')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1173. 即时食物配送 I

Create table If Not Exists Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date)
Truncate table Delivery
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('1', '1', '2019-08-01', '2019-08-02')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('2', '5', '2019-08-02', '2019-08-02')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('3', '1', '2019-08-11', '2019-08-11')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('4', '3', '2019-08-24', '2019-08-26')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('5', '4', '2019-08-21', '2019-08-22')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('6', '2', '2019-08-11', '2019-08-13')
1
2
3
4
5
6
7
8

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。
1
2
3
4
5
6
7
8
9
10

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

写一条 SQL 查询语句获取即时订单所占的百分比，保留两位小数。

查询结果如下所示。

示例 1:

输入：
Delivery 表:
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 5           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-11                  |
| 4           | 3           | 2019-08-24 | 2019-08-26                  |
| 5           | 4           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
+-------------+-------------+------------+-----------------------------+
输出：
+----------------------+
| immediate_percentage |
+----------------------+
| 33.33                |
+----------------------+
解释：2 和 3 号订单为即时订单，其他的为计划订单。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

题解一

select round (
    (select count(*) from Delivery where order_date = customer_pref_delivery_date) / 
    (select count(*) from Delivery) * 100,
    2
) as immediate_percentage
1
2
3
4
5

1174. 即时食物配送 II

Create table If Not Exists Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date)
Truncate table Delivery
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('1', '1', '2019-08-01', '2019-08-02')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('2', '2', '2019-08-02', '2019-08-02')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('3', '1', '2019-08-11', '2019-08-12')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('4', '3', '2019-08-24', '2019-08-24')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('5', '3', '2019-08-21', '2019-08-22')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('6', '2', '2019-08-11', '2019-08-13')
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('7', '4', '2019-08-09', '2019-08-09')
1
2
3
4
5
6
7
8
9

配送表: Delivery

+-----------------------------+---------+
| Column Name                 | Type    |
+-----------------------------+---------+
| delivery_id                 | int     |
| customer_id                 | int     |
| order_date                  | date    |
| customer_pref_delivery_date | date    |
+-----------------------------+---------+
delivery_id 是表的主键。
该表保存着顾客的食物配送信息，顾客在某个日期下了订单，并指定了一个期望的配送日期（和下单日期相同或者在那之后）。
1
2
3
4
5
6
7
8
9
10

如果顾客期望的配送日期和下单日期相同，则该订单称为「即时订单」，否则称为「计划订单」。

「首次订单」是顾客最早创建的订单。我们保证一个顾客只会有一个「首次订单」。

写一条 SQL 查询语句获取即时订单在所有用户的首次订单中的比例。保留两位小数。

查询结果如下所示：

Delivery 表：
+-------------+-------------+------------+-----------------------------+
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
+-------------+-------------+------------+-----------------------------+
| 1           | 1           | 2019-08-01 | 2019-08-02                  |
| 2           | 2           | 2019-08-02 | 2019-08-02                  |
| 3           | 1           | 2019-08-11 | 2019-08-12                  |
| 4           | 3           | 2019-08-24 | 2019-08-24                  |
| 5           | 3           | 2019-08-21 | 2019-08-22                  |
| 6           | 2           | 2019-08-11 | 2019-08-13                  |
| 7           | 4           | 2019-08-09 | 2019-08-09                  |
+-------------+-------------+------------+-----------------------------+

Result 表：
+----------------------+
| immediate_percentage |
+----------------------+
| 50.00                |
+----------------------+
1 号顾客的 1 号订单是首次订单，并且是计划订单。
2 号顾客的 2 号订单是首次订单，并且是即时订单。
3 号顾客的 5 号订单是首次订单，并且是计划订单。
4 号顾客的 7 号订单是首次订单，并且是即时订单。
因此，一半顾客的首次订单是即时的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

题解一

select round (
    sum(order_date = customer_pref_delivery_date) * 100 /
    count(*),
    2
) as immediate_percentage
from Delivery
where (customer_id, order_date) in (
    select customer_id, min(order_date)
    from delivery
    group by customer_id
)
1
2
3
4
5
6
7
8
9
10
11

题解二

SELECT ROUND(100*SUM(IF(customer_pref_delivery_date=order_date AND
                        (order_date, customer_id) IN(
                            SELECT MIN(order_date), customer_id 
                            FROM Delivery 
                            GROUP BY customer_id),1,0)) / COUNT(DISTINCT customer_id),2) AS immediate_percentage
FROM Delivery;
1
2
3
4
5
6

1193. 每月交易 I

Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Truncate table Transactions
insert into Transactions (id, country, state, amount, trans_date) values ('121', 'US', 'approved', '1000', '2018-12-18')
insert into Transactions (id, country, state, amount, trans_date) values ('122', 'US', 'declined', '2000', '2018-12-19')
insert into Transactions (id, country, state, amount, trans_date) values ('123', 'US', 'approved', '2000', '2019-01-01')
insert into Transactions (id, country, state, amount, trans_date) values ('124', 'DE', 'approved', '2000', '2019-01-07')
1
2
3
4
5
6

Table: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| country       | varchar |
| state         | enum    |
| amount        | int     |
| trans_date    | date    |
+---------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
state 列类型为 “[”批准“，”拒绝“] 之一。
1
2
3
4
5
6
7
8
9
10
11
12

编写一个 sql 查询来查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额。

以任意顺序返回结果表。

查询结果格式如下所示。

示例 1:

输入：
Transactions table:
+------+---------+----------+--------+------------+
| id   | country | state    | amount | trans_date |
+------+---------+----------+--------+------------+
| 121  | US      | approved | 1000   | 2018-12-18 |
| 122  | US      | declined | 2000   | 2018-12-19 |
| 123  | US      | approved | 2000   | 2019-01-01 |
| 124  | DE      | approved | 2000   | 2019-01-07 |
+------+---------+----------+--------+------------+
输出：
+----------+---------+-------------+----------------+--------------------+-----------------------+
| month    | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
+----------+---------+-------------+----------------+--------------------+-----------------------+
| 2018-12  | US      | 2           | 1              | 3000               | 1000                  |
| 2019-01  | US      | 1           | 1              | 2000               | 2000                  |
| 2019-01  | DE      | 1           | 1              | 2000               | 2000                  |
+----------+---------+-------------+----------------+--------------------+-----------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

题解一

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country
1
2
3
4
5
6
7
8

1194. 锦标赛优胜者

Create table If Not Exists Players (player_id int, group_id int)
Create table If Not Exists Matches (match_id int, first_player int, second_player int, first_score int, second_score int)
Truncate table Players
insert into Players (player_id, group_id) values ('10', '2')
insert into Players (player_id, group_id) values ('15', '1')
insert into Players (player_id, group_id) values ('20', '3')
insert into Players (player_id, group_id) values ('25', '1')
insert into Players (player_id, group_id) values ('30', '1')
insert into Players (player_id, group_id) values ('35', '2')
insert into Players (player_id, group_id) values ('40', '3')
insert into Players (player_id, group_id) values ('45', '1')
insert into Players (player_id, group_id) values ('50', '2')
Truncate table Matches
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('1', '15', '45', '3', '0')
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('2', '30', '25', '1', '2')
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('3', '30', '15', '2', '0')
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('4', '40', '20', '5', '2')
insert into Matches (match_id, first_player, second_player, first_score, second_score) values ('5', '35', '50', '1', '1')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Players 玩家表

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| player_id   | int   |
| group_id    | int   |
+-------------+-------+
player_id 是此表的主键。
此表的每一行表示每个玩家的组。
1
2
3
4
5
6
7
8

Matches 赛事表

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| first_player  | int     |
| second_player | int     | 
| first_score   | int     |
| second_score  | int     |
+---------------+---------+
match_id 是此表的主键。
每一行是一场比赛的记录，first_player 和 second_player 表示该场比赛的球员 ID。
first_score 和 second_score 分别表示 first_player 和 second_player 的得分。
你可以假设，在每一场比赛中，球员都属于同一组。
1
2
3
4
5
6
7
8
9
10
11
12
13

每组的获胜者是在组内累积得分最高的选手。如果平局，player_id 最小的选手获胜。

编写一个 SQL 查询来查找每组中的获胜者。

返回的结果表单没有顺序要求。

查询结果格式如下所示。

示例 1:

输入：
Players 表:
+-----------+------------+
| player_id | group_id   |
+-----------+------------+
| 15        | 1          |
| 25        | 1          |
| 30        | 1          |
| 45        | 1          |
| 10        | 2          |
| 35        | 2          |
| 50        | 2          |
| 20        | 3          |
| 40        | 3          |
+-----------+------------+
Matches 表:
+------------+--------------+---------------+-------------+--------------+
| match_id   | first_player | second_player | first_score | second_score |
+------------+--------------+---------------+-------------+--------------+
| 1          | 15           | 45            | 3           | 0            |
| 2          | 30           | 25            | 1           | 2            |
| 3          | 30           | 15            | 2           | 0            |
| 4          | 40           | 20            | 5           | 2            |
| 5          | 35           | 50            | 1           | 1            |
+------------+--------------+---------------+-------------+--------------+
输出：
+-----------+------------+
| group_id  | player_id  |
+-----------+------------+ 
| 1         | 15         |
| 2         | 35         |
| 3         | 40         |
+-----------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

题解一

select group_id, player_id from (
	select
		group_id,
		t2.player_id,
		rank() over(partition by group_id order by score desc, t2.player_id) rk
	from (
		select player_id, sum(score) score from (
			select first_player player_id, first_score score from matches
			union all
			select second_player, second_score from matches
		) t1 group by player_id
	) t2 left join players on t2.player_id = players.player_id
) t3 where rk = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13

题解二

select group_id, player_id from (
	select
		group_id,
		player_id,
		rank() over(
			partition by group_id 
			order by sum(
				if(player_id = first_player, first_score, second_score)
			) desc, player_id
		) rk
	from players, matches 
	where players.player_id = matches.first_player
	or players.player_id = matches.second_player
	group by group_id, player_id
) t1 where rk = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

1204. 最后一个能进入电梯的人

Create table If Not Exists Queue (person_id int, person_name varchar(30), weight int, turn int)
Truncate table Queue
insert into Queue (person_id, person_name, weight, turn) values ('5', 'Alice', '250', '1')
insert into Queue (person_id, person_name, weight, turn) values ('4', 'Bob', '175', '5')
insert into Queue (person_id, person_name, weight, turn) values ('3', 'Alex', '350', '2')
insert into Queue (person_id, person_name, weight, turn) values ('6', 'John Cena', '400', '3')
insert into Queue (person_id, person_name, weight, turn) values ('1', 'Winston', '500', '6')
insert into Queue (person_id, person_name, weight, turn) values ('2', 'Marie', '200', '4')
1
2
3
4
5
6
7
8

表: Queue

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| person_id   | int     |
| person_name | varchar |
| weight      | int     |
| turn        | int     |
+-------------+---------+
person_id 是这个表的主键。
该表展示了所有等待电梯的人的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
1
2
3
4
5
6
7
8
9
10
11

有一群人在等着上公共汽车。然而，巴士有1000 公斤的重量限制，所以可能会有一些人不能上。

写一条 SQL 查询语句查找最后一个能进入电梯且不超过重量限制的 person_name 。题目确保队列中第一位的人可以进入电梯，不会超重。

查询结果如下所示。

示例 1:

输入：
Queue 表
+-----------+-------------------+--------+------+
| person_id | person_name       | weight | turn |
+-----------+-------------------+--------+------+
| 5         | George Washington | 250    | 1    |
| 3         | John Adams        | 350    | 2    |
| 6         | Thomas Jefferson  | 400    | 3    |
| 2         | Will Johnliams    | 200    | 4    |
| 4         | Thomas Jefferson  | 175    | 5    |
| 1         | James Elephant    | 500    | 6    |
+-----------+-------------------+--------+------+
输出：
+-------------------+
| person_name       |
+-------------------+
| Thomas Jefferson  |
+-------------------+
解释：
为了简化，Queue 表按 turn 列由小到大排序。
上例中 George Washington(id 5), John Adams(id 3) 和 Thomas Jefferson(id 6) 将可以进入电梯,因为他们的体重和为 250 + 350 + 400 = 1000。
Thomas Jefferson(id 6) 是最后一个体重合适并进入电梯的人。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

题解一

SELECT a.person_name
FROM Queue a, Queue b
WHERE a.turn >= b.turn
GROUP BY a.person_id HAVING SUM(b.weight) <= 1000
ORDER BY a.turn DESC
LIMIT 1
1
2
3
4
5
6

题解二

SELECT a.person_name
FROM (
	SELECT person_name, @pre := @pre + weight AS weight
	FROM Queue, (SELECT @pre := 0) tmp
	ORDER BY turn
) a
WHERE a.weight <= 1000
ORDER BY a.weight DESC
LIMIT 1
1
2
3
4
5
6
7
8
9

题解三

select b.person_name
from queue a, queue b
where a.turn <= b.turn
group by b.person_id
having sum(a.weight) <= 1000
order by b.turn desc 
limit 1
1
2
3
4
5
6
7

1205. 每月交易II

Create table If Not Exists Transactions (id int, country varchar(4), state enum('approved', 'declined'), amount int, trans_date date)
Create table If Not Exists Chargebacks (trans_id int, trans_date date)
Truncate table Transactionsinsert into Transactions (id, country, state, amount, trans_date) values ('101', 'US', 'approved', '1000', '2019-05-18')insert into Transactions (id, country, state, amount, trans_date) values ('102', 'US', 'declined', '2000', '2019-05-19')insert into Transactions (id, country, state, amount, trans_date) values ('103', 'US', 'approved', '3000', '2019-06-10')insert into Transactions (id, country, state, amount, trans_date) values ('104', 'US', 'declined', '4000', '2019-06-13')insert into Transactions (id, country, state, amount, trans_date) values ('105', 'US', 'approved', '5000', '2019-06-15')Truncate table Chargebacksinsert into Chargebacks (trans_id, trans_date) values ('102', '2019-05-29')insert into Chargebacks (trans_id, trans_date) values ('101', '2019-06-30')insert into Chargebacks (trans_id, trans_date) values ('105', '2019-09-18')
1
2
3

Transactions 记录表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| country        | varchar |
| state          | enum    |
| amount         | int     |
| trans_date     | date    |
+----------------+---------+
id 是这个表的主键。
该表包含有关传入事务的信息。
状态列是类型为 [approved（已批准）、declined（已拒绝）] 的枚举。
1
2
3
4
5
6
7
8
9
10
11
12

Chargebacks 表

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| trans_id       | int     |
| trans_date     | date    |
+----------------+---------+
退单包含有关放置在事务表中的某些事务的传入退单的基本信息。
trans_id 是 transactions 表的 id 列的外键。
每项退单都对应于之前进行的交易，即使未经批准。
1
2
3
4
5
6
7
8
9

编写一个 SQL 查询，以查找每个月和每个国家/地区的信息：已批准交易的数量及其总金额、退单的数量及其总金额。

注意：在您的查询中，只需显示给定月份和国家，忽略所有为零的行。

以任意顺序返回结果表。

查询结果格式如下所示。

示例 1:

输入：
Transactions 表：
+-----+---------+----------+--------+------------+
| id  | country | state    | amount | trans_date |
+-----+---------+----------+--------+------------+
| 101 | US      | approved | 1000   | 2019-05-18 |
| 102 | US      | declined | 2000   | 2019-05-19 |
| 103 | US      | approved | 3000   | 2019-06-10 |
| 104 | US      | declined | 4000   | 2019-06-13 |
| 105 | US      | approved | 5000   | 2019-06-15 |
+-----+---------+----------+--------+------------+
Chargebacks 表：
+----------+------------+
| trans_id | trans_date |
+----------+------------+
| 102      | 2019-05-29 |
| 101      | 2019-06-30 |
| 105      | 2019-09-18 |
+----------+------------+
输出：
+---------+---------+----------------+-----------------+------------------+-------------------+
| month   | country | approved_count | approved_amount | chargeback_count | chargeback_amount |
+---------+---------+----------------+-----------------+------------------+-------------------+
| 2019-05 | US      | 1              | 1000            | 1                | 2000              |
| 2019-06 | US      | 2              | 8000            | 1                | 1000              |
| 2019-09 | US      | 0              | 0               | 1                | 5000              |
+---------+---------+----------------+-----------------+------------------+-------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

题解一

SELECT month,country,
        SUM(IF(tag=1,1,0)) approved_count,
        SUM(IF(tag=1,amount,0)) approved_amount,
        SUM(IF(tag=0,1,0)) chargeback_count,
        SUM(IF(tag=0,amount,0)) chargeback_amount
FROM(
    SELECT LEFT(trans_date,7) month,
        country,
        amount,
        1 tag
    FROM Transactions
    WHERE state='approved'
    UNION ALL
    SELECT LEFT(c.trans_date,7) month,
        country,
        t.amount,
        0 tag
    FROM Chargebacks c
    LEFT JOIN Transactions t ON c.trans_id=t.id
) a
GROUP BY month,country
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

题解二

with base as (
    select 'approved' tag, country, date_format(trans_date, '%Y-%m') month, amount
    from Transactions where state = 'approved'
    union all
    select 'chargeback' tag, t.country, date_format(c.trans_date, '%Y-%m') month, t.amount
    from Chargebacks c join Transactions t on c.trans_id = t.id
)
select month, country, 
       sum(if(tag = 'approved', 1, 0)) approved_count,
       sum(if(tag = 'approved', amount, 0)) approved_amount,
       sum(if(tag = 'chargeback', 1, 0)) chargeback_count,
       sum(if(tag = 'chargeback', amount, 0)) chargeback_amount
from base 
group by month, country;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

题解三

select month,
country,
count(case when state='approved' and tag=0 then 1 else null end ) as approved_count,
sum(case when state='approved' and tag=0 then amount else 0 end ) as approved_amount,
count(case when tag=1 then 1 else null end  ) as chargeback_count,
sum(case when  tag=1 then amount else 0 end ) as chargeback_amount
from(
select country,state,amount,date_format(c.trans_date,'%Y-%m') as month,1 as tag
from Transactions t   
right join Chargebacks c on t.id=c.trans_id
union all
select country,state,amount,date_format(t.trans_date,'%Y-%m') as month,0  as tag
from Transactions t  where state!='declined'
) a group by country,month order by month,country
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1211. 查询结果的质量和占比

Create table If Not Exists Queries (query_name varchar(30), result varchar(50), position int, rating int)
Truncate table Queries
insert into Queries (query_name, result, position, rating) values ('Dog', 'Golden Retriever', '1', '5')
insert into Queries (query_name, result, position, rating) values ('Dog', 'German Shepherd', '2', '5')
insert into Queries (query_name, result, position, rating) values ('Dog', 'Mule', '200', '1')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Shirazi', '5', '2')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Siamese', '3', '3')
insert into Queries (query_name, result, position, rating) values ('Cat', 'Sphynx', '7', '4')
1
2
3
4
5
6
7
8

查询表 Queries：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| query_name  | varchar |
| result      | varchar |
| position    | int     |
| rating      | int     |
+-------------+---------+
此表没有主键，并可能有重复的行。
此表包含了一些从数据库中收集的查询信息。
“位置”（position）列的值为 1 到 500 。
“评分”（rating）列的值为 1 到 5 。评分小于 3 的查询被定义为质量很差的查询。
1
2
3
4
5
6
7
8
9
10
11
12

将查询结果的质量 quality 定义为：

各查询结果的评分与其位置之间比率的平均值。
1

将劣质查询百分比 poor_query_percentage 为：

评分小于 3 的查询结果占全部查询结果的百分比。
1

编写一组 SQL 来查找每次查询的名称(query_name)、质量(quality) 和劣质查询百分比(poor_query_percentage)。

质量(quality) 和劣质查询百分比(poor_query_percentage) 都应四舍五入到小数点后两位。

查询结果格式如下所示：

Queries table:
+------------+-------------------+----------+--------+
| query_name | result            | position | rating |
+------------+-------------------+----------+--------+
| Dog        | Golden Retriever  | 1        | 5      |
| Dog        | German Shepherd   | 2        | 5      |
| Dog        | Mule              | 200      | 1      |
| Cat        | Shirazi           | 5        | 2      |
| Cat        | Siamese           | 3        | 3      |
| Cat        | Sphynx            | 7        | 4      |
+------------+-------------------+----------+--------+

Result table:
+------------+---------+-----------------------+
| query_name | quality | poor_query_percentage |
+------------+---------+-----------------------+
| Dog        | 2.50    | 33.33                 |
| Cat        | 0.66    | 33.33                 |
+------------+---------+-----------------------+

Dog 查询结果的质量为 ((5 / 1) + (5 / 2) + (1 / 200)) / 3 = 2.50
Dog 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33

Cat 查询结果的质量为 ((2 / 5) + (3 / 3) + (4 / 7)) / 3 = 0.66
Cat 查询结果的劣质查询百分比为 (1 / 3) * 100 = 33.33
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

题解一

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
GROUP BY query_name
1
2
3
4
5
6

题解二

select 
    query_name, 
    round(avg(rating/position), 2) as quality, 
    round(sum(case when rating<3 then 1 else 0 end)/count(*)*100, 2) as poor_query_percentage
from queries
group by query_name;
1
2
3
4
5
6

1212. 查询球队积分

Create table If Not Exists Teams (team_id int, team_name varchar(30))
Create table If Not Exists Matches (match_id int, host_team int, guest_team int, host_goals int, guest_goals int)
Truncate table Teams
insert into Teams (team_id, team_name) values ('10', 'Leetcode FC')
insert into Teams (team_id, team_name) values ('20', 'NewYork FC')
insert into Teams (team_id, team_name) values ('30', 'Atlanta FC')
insert into Teams (team_id, team_name) values ('40', 'Chicago FC')
insert into Teams (team_id, team_name) values ('50', 'Toronto FC')
Truncate table Matches
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('1', '10', '20', '3', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('2', '30', '10', '2', '2')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('3', '10', '50', '5', '1')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('4', '20', '30', '1', '0')
insert into Matches (match_id, host_team, guest_team, host_goals, guest_goals) values ('5', '50', '30', '1', '0')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

表: Teams

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
此表的主键是 team_id。
表中的每一行都代表一支独立足球队。
1
2
3
4
5
6
7
8

表: Matches

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| match_id      | int     |
| host_team     | int     |
| guest_team    | int     | 
| host_goals    | int     |
| guest_goals   | int     |
+---------------+---------+
此表的主键是 match_id。
表中的每一行都代表一场已结束的比赛。
比赛的主客队分别由它们自己的 id 表示，他们的进球由 host_goals 和 guest_goals 分别表示。
1
2
3
4
5
6
7
8
9
10
11
12

您希望在所有比赛之后计算所有球队的比分。积分奖励方式如下:

如果球队赢了比赛(即比对手进更多的球)，就得 3 分。
如果双方打成平手(即，与对方得分相同)，则得 1 分。
如果球队输掉了比赛(例如，比对手少进球)，就 不得分 。
1
2
3

写出一条SQL语句以查询每个队的 team_id，team_name 和 num_points。

返回的结果根据 num_points 降序排序，如果有两队积分相同，那么这两队按 team_id 升序排序。

查询结果格式如下。

示例 1:

输入：
Teams table:
+-----------+--------------+
| team_id   | team_name    |
+-----------+--------------+
| 10        | Leetcode FC  |
| 20        | NewYork FC   |
| 30        | Atlanta FC   |
| 40        | Chicago FC   |
| 50        | Toronto FC   |
+-----------+--------------+
Matches table:
+------------+--------------+---------------+-------------+--------------+
| match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
+------------+--------------+---------------+-------------+--------------+
| 1          | 10           | 20            | 3           | 0            |
| 2          | 30           | 10            | 2           | 2            |
| 3          | 10           | 50            | 5           | 1            |
| 4          | 20           | 30            | 1           | 0            |
| 5          | 50           | 30            | 1           | 0            |
+------------+--------------+---------------+-------------+--------------+
输出：
+------------+--------------+---------------+
| team_id    | team_name    | num_points    |
+------------+--------------+---------------+
| 10         | Leetcode FC  | 7             |
| 20         | NewYork FC   | 3             |
| 50         | Toronto FC   | 3             |
| 30         | Atlanta FC   | 1             |
| 40         | Chicago FC   | 0             |
+------------+--------------+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

题解一

SELECT team_id,team_name,IFNULL(SUM(pt),0) num_points
FROM Teams t
LEFT JOIN(
    SELECT host_team team_id,
        (CASE WHEN host_goals>guest_goals THEN 3
            WHEN host_goals=guest_goals THEN 1
            ELSE 0
        END) pt
    FROM Matches
    UNION ALL
    SELECT guest_team team_id,
        (CASE WHEN host_goals>guest_goals THEN 0
            WHEN host_goals=guest_goals THEN 1
            ELSE 3
        END) pt
    FROM Matches
) a    USING(team_id)
GROUP BY team_id
ORDER BY num_points DESC,team_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

题解二

with ta1 as (select match_id, host_team, guest_team, 
case when host_goals > guest_goals then 3
when host_goals < guest_goals then 0
when host_goals = guest_goals then 1
end as host_scores,
case when host_goals > guest_goals then 0
when host_goals < guest_goals then 3
when host_goals = guest_goals then 1
end as guest_scores
from matches)
select t.team_id, team_name, ifnull(sum(teamscores),0) as num_points
from teams t left join 
(select host_team as teamid, sum(host_scores) as teamscores
from ta1
group by host_team
union all
select guest_team as teamid, sum(guest_scores) as teamscores
from ta1
group by guest_team) as ta2
on t.team_id = ta2.teamid
group by t.team_id
order by num_points desc, t.team_id asc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

题解三

SELECT t.team_id, t.team_name, IFNULL(score,0) num_points
FROM
(
    SELECT team_id, SUM(score) score
    FROM (
        SELECT host_team team_id, 
        SUM(CASE
        WHEN host_goals>guest_goals THEN 3
        WHEN host_goals<guest_goals THEN 0
        ELSE 1
        END) score
        FROM matches
        GROUP BY host_team
        UNION ALL
        SELECT guest_team team_id, 
        SUM(CASE
        WHEN host_goals>guest_goals THEN 0
        WHEN host_goals<guest_goals THEN 3
        ELSE 1
        END) score
        FROM matches
        GROUP BY guest_team
    ) b
    GROUP BY team_id
) a
RIGHT JOIN teams t ON t.team_id=a.team_id
ORDER BY num_points DESC, t.team_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

题解四

select team_id, team_name, ifnull(sum(if(team_id=host_team,host_score,guest_score)),0) as num_points
from Teams m  
left join
(
select host_team, guest_team, 
if(host_goals<guest_goals,0,if(host_goals>guest_goals,3,1)) as host_score, 
if(host_goals<guest_goals,3,if(host_goals>guest_goals,0,1)) as guest_score
from Matches) t 
on m.team_id = t.host_team or m.team_id = t.guest_team 
group by team_id
order by num_points desc, team_id
1
2
3
4
5
6
7
8
9
10
11

题解五

SELECT T.TEAM_ID, TEAM_NAME, IFNULL(SUM(POINTS), 0) AS NUM_POINTS
FROM TEAMS AS T
LEFT JOIN (SELECT HOST_TEAM AS TEAM_ID,
                  CASE WHEN HOST_GOALS > GUEST_GOALS THEN 3
                       WHEN HOST_GOALS = GUEST_GOALS THEN 1
                       ELSE 0 END AS POINTS
           FROM MATCHES
           UNION ALL
           SELECT GUEST_TEAM AS TEAM_ID,
                  CASE WHEN HOST_GOALS < GUEST_GOALS THEN 3
                       WHEN HOST_GOALS = GUEST_GOALS THEN 1
                       ELSE 0 END AS POINTS
            FROM MATCHES) AS A
ON T.TEAM_ID = A.TEAM_ID
GROUP BY 1
ORDER BY 3 DESC, 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

1225. 报告系统状态的连续日期

Create table If Not Exists Failed (fail_date date)
Create table If Not Exists Succeeded (success_date date)
Truncate table Failed
insert into Failed (fail_date) values ('2018-12-28')
insert into Failed (fail_date) values ('2018-12-29')
insert into Failed (fail_date) values ('2019-01-04')
insert into Failed (fail_date) values ('2019-01-05')
Truncate table Succeeded
insert into Succeeded (success_date) values ('2018-12-30')
insert into Succeeded (success_date) values ('2018-12-31')
insert into Succeeded (success_date) values ('2019-01-01')
insert into Succeeded (success_date) values ('2019-01-02')
insert into Succeeded (success_date) values ('2019-01-03')
insert into Succeeded (success_date) values ('2019-01-06')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.
1
2
3
4
5
6
7

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.
1
2
3
4
5
6
7

系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+


Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

题解一

SELECT period_state, MIN(date) as start_date, MAX(date) as end_date
FROM (
    SELECT
        success_date AS date,
        "succeeded" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := success_date) = -1, @id, @id := @id+1) AS id 
    FROM Succeeded, (SELECT @id := 0, @pre_date := NULL) AS temp
    UNION
    SELECT
        fail_date AS date,
        "failed" AS period_state,
        IF(DATEDIFF(@pre_date, @pre_date := fail_date) = -1, @id, @id := @id+1) AS id 
    FROM Failed, (SELECT @id := 0, @pre_date := NULL) AS temp
) T  WHERE date BETWEEN "2019-01-01" AND "2019-12-31"
GROUP BY T.id
ORDER BY start_date ASC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

题解二

select period_state,start_date,end_date 
from(
select type as period_state,diff, min(date) as start_date, max(date) as end_date
from
(
    select type, date, subdate(date,row_number()over(partition by type order by date)) as diff,
    row_number()over(partition by type order by date) as rn
    from
    (
        select 'failed' as type, fail_date as date from Failed
        union all
        select 'succeeded' as type, success_date as date from Succeeded
    ) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
) t
order by start_date
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

题解三

with cte as
(select 
    fail_date as date,
    'failed' as period_state 
from Failed
union
select 
    success_date,
    'succeeded'    
from Succeeded 
order by 1)


select 
    period_state,
    min(date) as start_date,
    max(date) as end_date
from
(select
    period_state,
    date,
    row_number() over(order by date) - 
    row_number() over(partition by period_state order by date) as rnk_diff
from cte
where date between '2019-01-01' and '2019-12-31') t
group by period_state, rnk_diff # 不同period_state时rnk_diff一样
order by 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

1241. 每个帖子的评论数

Create table If Not Exists Submissions (sub_id int, parent_id int)
Truncate table Submissions
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('2', 'None')
insert into Submissions (sub_id, parent_id) values ('1', 'None')
insert into Submissions (sub_id, parent_id) values ('12', 'None')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('5', '2')
insert into Submissions (sub_id, parent_id) values ('3', '1')
insert into Submissions (sub_id, parent_id) values ('4', '1')
insert into Submissions (sub_id, parent_id) values ('9', '1')
insert into Submissions (sub_id, parent_id) values ('10', '2')
insert into Submissions (sub_id, parent_id) values ('6', '7')
1
2
3
4
5
6
7
8
9
10
11
12
13

表 Submissions 结构如下：

+---------------+----------+
| 列名           | 类型     |
+---------------+----------+
| sub_id        | int      |
| parent_id     | int      |
+---------------+----------+
上表没有主键, 所以可能会出现重复的行。
每行可以是一个帖子或对该帖子的评论。
如果是帖子的话，parent_id 就是 null。
对于评论来说，parent_id 就是表中对应帖子的 sub_id。
1
2
3
4
5
6
7
8
9
10

编写 SQL 语句以查找每个帖子的评论数。

结果表应包含帖子的 post_id 和对应的评论数 number_of_comments 并且按 post_id 升序排列。

Submissions 可能包含重复的评论。您应该计算每个帖子的唯一评论数。

Submissions 可能包含重复的帖子。您应该将它们视为一个帖子。

结果表应该按 post_id 升序排序。

查询结果格式如下例所示。

示例 1:

输入：
Submissions table:
+---------+------------+
| sub_id  | parent_id  |
+---------+------------+
| 1       | Null       |
| 2       | Null       |
| 1       | Null       |
| 12      | Null       |
| 3       | 1          |
| 5       | 2          |
| 3       | 1          |
| 4       | 1          |
| 9       | 1          |
| 10      | 2          |
| 6       | 7          |
+---------+------------+
输出：
+---------+--------------------+
| post_id | number_of_comments |
+---------+--------------------+
| 1       | 3                  |
| 2       | 2                  |
| 12      | 0                  |
+---------+--------------------+
解释：
表中 ID 为 1 的帖子有 ID 为 3、4 和 9 的三个评论。表中 ID 为 3 的评论重复出现了，所以我们只对它进行了一次计数。
表中 ID 为 2 的帖子有 ID 为 5 和 10 的两个评论。
ID 为 12 的帖子在表中没有评论。
表中 ID 为 6 的评论是对 ID 为 7 的已删除帖子的评论，因此我们将其忽略。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

题解一

SELECT post_id, COUNT(sub_id) AS number_of_comments
FROM (
    SELECT DISTINCT post.sub_id AS post_id, sub.sub_id AS sub_id
    FROM Submissions post
    LEFT JOIN Submissions sub
    ON post.sub_id = sub.parent_id
    WHERE post.parent_id is null
) T
GROUP BY post_id
ORDER BY post_id ASC
1
2
3
4
5
6
7
8
9
10

题解二

SELECT
	post_id,
	COUNT( DISTINCT S2.sub_id ) AS number_of_comments 
FROM
	( SELECT DISTINCT sub_id AS post_id FROM Submissions WHERE parent_id IS NULL ) S1
	LEFT JOIN Submissions S2 ON S1.post_id = S2.parent_id 
GROUP BY S1.post_id
1
2
3
4
5
6
7

相关阅读:
linux-伪首部校验和和 icmpv6 socket组播
 【计算机毕业设计】71.大学生兼职信息系统源码
 【无标题】
不重要的token可以提前停止计算！英伟达提出自适应token的高效视觉Transformer网络A-ViT，提高模型的吞吐量！...
从内存角度聊聊虚函数
 贪心算法（2）--衍生问题
 截图工具分享（可截成gif动图）
简易SSM框架转账
 mysql select语句中from 之后跟查询语句
 jmeter阶梯式线程组
原文地址：https://blog.csdn.net/weixin_45682261/article/details/126065323