There is a table USER_book records information about the books owned by the user, the data of the table is as follows:
Key Userid Bookid
1 1 2
2 1 3
3 1 4
4 2 1
5 2 3
This indicates that this user 1 has 2, 3, 4 three books, and users 2 have 1, 3 books, and so on.
Now use 1 SQL statement to get the following question:
Give an invigilament bookid, requiring users who have all books given. This seems
A simple problem has plagued us for a long time, and after trying many times, finally came to the following approach.
The SQL statement is as follows:
Select a.user from
Select Distinct Userid As User, Count (Distinct Bookid) AS NUM
From user_book where bookid in (1, 3) group by userid) AS A
WHERE A.NUM = 2
The key to this statement is to find that the user has books that are included in the bookid you're looking.
quantity. 2 in a.num = 2 in the WHERE statement is the number of books to be found. This quantity
If the number of books you want is consistent, this user meets the conditions.
The problem is solved
