In this series, I will post all the articles that I and my colleagues encounter. it is the requirements encountered in the working process, it is not too difficult. I note it to share and save for myself:
1. Requirements
I have 2 tables like this: services
| id | name |
|---|---|
| first | service 1 |
| 2 | service 2 |
| 3 | service 3 |
room_services Value in the table
| room_id | service_id |
|---|---|
| first | 2 |
| first | 3 |
| 2 | 2 |
| 3 | 2 |
| first | first |
| 2 | 3 |
Each relationship is as follows: 1 room has many services , each service can be performed in many rooms
Problem requirement: Find out the id of all the rooms that perform all the services : As above, I will find room 1 (id = 1) it performs all 3 services (1,2,3 )
To better understand the problem, we should proactively create data, try to read the test request then read it more effectively.
2. Analysis:
- Based on the services table, we get a list of all service_id
1 2 | select id from rooms; |
- Based on the table room_services , when grouped by service_id , we will group the service groups according to room_id, the service_id can only be obtained based on the aggrate function operator. list Id from the room table
3. Solution
Method 1 : Based on the sum operator
1 2 3 4 5 | select room_id from room_services group by room_id having sum(service_id) = (select sum(id) from services); |
Method 2 : If the field service_id is not an integer, then we can use the group_concat function to create strings , pay attention to the order, then we need to sort first.
1 2 3 4 5 | select room_id from room_services group by room_id having group_concat(service_id order by service_id) = (select group_concat(id order by id) from services); |
If you need to talk, please comment below. Thanks for reading my post.