如何在联接中使用datediff函数

Question

 create procedure fine()
 begin
 declare a integer(10);
 declare b integer(10);
 declare c integer(10);
 declare d integer(10);
 declare cal_fine cursor for select book_id,datediff(return_date,issue_date) 
from return_book where datediff(return_date,issue_date)>10;
open cal_fine;
myloop:loop
fetch cal_fine into a,b;
set c=(b-10)*5;
update return_book set fine=c where book_id=a;
end loop;
close cal_fine;
end;  

我有这段代码可以找到，但问题是我的issue_date不在return_book表中，所以我必须加入issue_book和return_book，但是如何使用datediff函数呢？请帮帮我

Answer 1

给定液滴表issue_book，return_book；

    create table issue_book (book_id int, issue_date date);
    create table return_book (book_id int, return_date date);

    insert into issue_book values(1,'2017-01-01'), (2,'2017-09-02'),(3,'2017-09-01');
    insert into return_book values(1,'2017-10-08'), (2,'2017-10-08');

这个查询

select ib.issue_date,rb.return_date ,datediff(return_date,issue_date) ddiff
from return_book rb
join issue_book ib on rb.book_id=ib.book_id 
where datediff(return_date,issue_date)>10; 

结果在

+------------+-------------+-------+
| issue_date | return_date | ddiff |
+------------+-------------+-------+
| 2017-01-01 | 2017-10-08  |   280 |
| 2017-09-02 | 2017-10-08  |    36 |
+------------+-------------+-------+
2 rows in set (0.00 sec)

但是，您的逻辑中可能有一个缺陷，即如果没有返回一本书会发生什么-- return_books中将没有条目。

另外，不需要过程或游标，这可以通过以下更新语句来实现

update return_book rb join issue_book ib on rb.book_id=ib.book_id 
 set fine = (datediff(return_date,issue_date) - 10) * 5
 where datediff(return_date,issue_date)>10;