leetcode-mysql 2021-05-07

来源：Leetcode  地址：https://leetcode-cn.com/problems/ad-free-sessions/  & https://leetcode-cn.com/problems/customers-who-never-order/ & https://leetcode-cn.com/problems/consecutive-available-seats/ & https://leetcode-cn.com/problems/triangle-judgement/【侵删】

 

ExampleA: (Ad-Free Sessions)

Table: Playback

+-------------+------+
| Column Name | Type |
+-------------+------+
| session_id  | int  |
| customer_id | int  |
| start_time  | int  |
| end_time    | int  |
+-------------+------+
session_id is the primary key for this table.
customer_id is the ID of the customer watching this session.
The session runs during the inclusive interval between start_time and end_time.
It is guaranteed that start_time <= end_time and that two sessions for the same customer do not intersect.

 

Table: Ads

+-------------+------+
| Column Name | Type |
+-------------+------+
| ad_id       | int  |
| customer_id | int  |
| timestamp   | int  |
+-------------+------+
ad_id is the primary key for this table.
customer_id is the ID of the customer viewing this ad.
timestamp is the moment of time at which the ad was shown.

 

Write an SQL query to report all the sessions that did not get shown any ads.

Return the result table in any order.

The query result format is in the following example:

 

Playback table:
+------------+-------------+------------+----------+
| session_id | customer_id | start_time | end_time |
+------------+-------------+------------+----------+
| 1          | 1           | 1          | 5        |
| 2          | 1           | 15         | 23       |
| 3          | 2           | 10         | 12       |
| 4          | 2           | 17         | 28       |
| 5          | 2           | 2          | 8        |
+------------+-------------+------------+----------+

Ads table:
+-------+-------------+-----------+
| ad_id | customer_id | timestamp |
+-------+-------------+-----------+
| 1     | 1           | 5         |
| 2     | 2           | 17        |
| 3     | 2           | 20        |
+-------+-------------+-----------+

Result table:
+------------+
| session_id |
+------------+
| 2          |
| 3          |
| 5          |
+------------+
The ad with ID 1 was shown to user 1 at time 5 while they were in session 1.
The ad with ID 2 was shown to user 2 at time 17 while they were in session 4.
The ad with ID 3 was shown to user 2 at time 20 while they were in session 4.
We can see that sessions 1 and 4 had at least one ad. Sessions 2, 3, and 5 did not have any ads, so we return them.

解题思路（not in left join）

# select distinct p.session_id from playback p left join ads a on a.customer_id=p.customer_id where timestamp not between start_time and end_time; 【错误答案】

{"headers": ["session_id", "start_time", "end_time", "timestamp"], "values": [[1, 1, 5, 5], [2, 15, 23, 5], [3, 10, 12, 20], [3, 10, 12, 17], [4, 17, 28, 20], [4, 17, 28, 17], [5, 2, 8, 20], [5, 2, 8, 17]]}

这个是去掉了where条件的搜索结果，可以看出来session_id有可能有多条纪录，按照错误答案走的话 假设有一条session符合where的话就会被纪录，但是实际结果不能这样；所以需要取出在里面的session然后做排除法

select session_id from playback where session_id not in( select session_id from playback p join ads a on p.customer_id=a.customer_id where a.timestamp between start_time and end_time);

 

今天提前做第二天的题目（明天需要取入职体检报告以及再去面试一下，估计很难有时间了）

 

ExampleB (从不订购的客户)

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

 

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

 

解题思路（not in）

select name customers from customers where customers.id not in (select customerid from orders);

趁着女朋友刷抖音再练习一道题目

 

 

ExampleC (连续空余座位)

几个朋友来到电影院的售票处，准备预约连续空余座位。

你能利用表 cinema ，帮他们写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回吗？

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

对于如上样例，你的查询语句应该返回如下结果。

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |

注意：

seat_id 字段是一个自增的整数，free 字段是布尔类型（'1' 表示空余， '0' 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

 

解题思路（这道题多刷几次找找感觉，虽然最后看懂了是咋回事，但是思路还是欠缺的）

select distinct c1.seat_id from cinema c1 join cinema c2 on

abs(c1.seat_id - c2.seat_id)=1 where c1.free=1 and c2.free=1 order by c1.seat_id;

 

第一步：表连接使用on后的abs条件（座位ID绝对值的差额为1）

select distict c1.seat_id,c2.seat_id from cinema c1 join cinema c2 on abs(c1.seat_id - c2.seat_id)=1;

组合后的表

{"headers":{"cinema":["seat_id","free"]},"rows":{"cinema":[[1,1],[2,0],[3,1],[4,1],[5,1]]}}

输出

{"headers": ["seat_id", "seat_id"], "values": [[2, 1], [3, 2], [1, 2], [4, 3], [2, 3], [5, 4], [3, 4], [4, 5]]}

第二步：进行相邻作为空余筛选

where c1.free=1 and c2.free=1 

第三步：排序

order by 

 

ExampleD (判断三角形)

一个小学生 Tim 的作业是判断三条线段是否能形成一个三角形。

然而，这个作业非常繁重，因为有几百组线段需要判断。

假设表 triangle 保存了所有三条线段的长度 x、y、z ，请你帮 Tim 写一个查询语句，来判断每组 x、y、z 是否可以组成一个三角形？

| x  | y  | z  |
|----|----|----|
| 13 | 15 | 30 |
| 10 | 20 | 15 |

对于如上样例数据，你的查询语句应该返回如下结果：

| x  | y  | z  | triangle |
|----|----|----|----------|
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |

 

解题思路（case 判定）

select x,y,z, CASE WHEN x+y>z AND x+z>y AND y+z>x THEN 'Yes' else 'No' end triangle from triangle; 【这里出错点在于yes记得字符串】

【早上起来活跃一下脑子】