An example of a joint query about a database

The final result required by the question is like this

Isn’t it very simple?

However, the database is like this

Ignore the renewal operation and only focus on the borrowing and returning books .

That is to say, the same table and the same book can be deposited, borrowed, and returned, and the same book may be lent or returned multiple times.

Other attributes are easy to talk about, but the more troublesome one is the return time. For the same book, the return operation immediately following the borrowing operation is its return time.

The solution is as follows:

Record the lending operation as table A and the return operation as table B: there are 22 rows in table A and 19 rows in table B

The connection condition should be the number of the book collection and the reader's ID, and the result of the connection is 58 lines. Since there are several numbers for the same book title in the data, I removed the book title. The result of the connection is like this

It can be seen that if the record block diagram above What’s framed is the problem! The box diagram shows the same book borrowing record, which is connected to each book return record. It can be seen from the records that I asked this person to borrow the same book six times and made six connections!

The following rules can help us query the correct results

1) In the same group of borrowing and returning operations, the borrowing operation must be before the returning operation

2) Exclude the borrowing operation time >After recording the return operation time, the earliest return time of the book is this person. The return time of this book

The final result is like this

The final SQL code is like this

select A.*,min(B.操作时间) 还书时间 from aleft join 
 bon a.书籍的典藏编号 = b.书籍的典藏编号 and a.读者id = b.读者id 
and a.操作时间<b.操作时间group by a.书籍的典藏编号,a.操作名称,a.操作时间,a.读者id

is using a left outer join, and the return time for unreturned books is of course NULL. In this way, other subqueries, unions, and paging are no longer problems.

The above is the detailed content of An example of a joint query about a database. For more information, please follow other related articles on the PHP Chinese website!