- Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
describe
Somewhere there are boxes marked with IDS, some of which are filled and some of which are free. Write an SQL statement to find empty and contiguous boxes, sort them by increasing ID, and return them.
Table definition: boxes
| The column name | type | annotation |
|---|---|---|
| id | int | A primary key |
| is_empty | int | Box status (0 means box occupied, 1 means box free and available) |
**
Tip:
- Consecutive empty boxes refer to the number of consecutive empty boxes
- The data is guaranteed to be 0 or 1 for is_empty
- The boxes have consecutive ids
The sample
A sample:
Table contents: boxes
| id | is_empty |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
After running your SQL statement, the table should return:
| id |
|---|
| 3 |
| 4 |
| 5 |
Example 2:
Table contents: boxes
| id | is_empty |
|---|---|
| 1 | 1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 1 |
| 5 | 0 |
After running your SQL statement, the table should return:
| id |
|---|
| 3 |
| 4 |
Answer key
What’s the point of this problem? Continuous. What’s the definition of continuous? 1, 2, 3, 4 so it’s a continuous ID, and the connection between these numbers is that they differ by 1. If it’s a difference of 1, b minus a is equal to 1. There is also a point that is distinct removed. B1.id-b2.id =1; abs(b1.id-b2.id)=1; abs(b1.id-b2.id)=1; But once abs is used, it is equivalent to running for on both sides, which will be repeated, so it needs to be reweighted here.
select distinct b1.id
from boxes b1, boxes b2
where abs(b1.id - b2.id)=1 and b1.is_empty=1 and b2.is_empty=1 order by b1.id
``
Copy the code