来源：Leetcode  地址：https://leetcode-cn.com/problems/ad-free-sessions/ 【侵删】

 

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);

 

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