sql查询不返回最大值

问题描述：

我已经写了下面的查询来获取最大issue_id的值。但我得到了book_id 2的issue_id 14。我想要的是当book_id为2时获取issue_id 16，而当book_id为3时获取issue_id 15等等。sql查询不返回最大值

SELECT issue.issue_id AS issue_id, issue.issue_date, issue.student_id, books.availability, 
    CASE WHEN NOW() > DATE_ADD(issue.issue_date, INTERVAL 20 DAY) 
     THEN 10*DATEDIFF(NOW(), DATE_ADD(issue.issue_date, INTERVAL 20 DAY)) 
     ELSE 0 END AS fine_amount 
     FROM issue 
     INNER JOIN books 
     ON issue.book_id=books.book_id 
     WHERE books.book_id=2 
     HAVING MAX(issue.issue_id)

的Sql MAX实例与'HAVING'和'WHERE'见[这里]（HTTP：//www.w3resource。 COM/SQL /聚集物的功能/最大-having.php）。 –

答

使用ORDER BY ... LIMIT 1获得最高值

SELECT issue.issue_id AS issue_id, issue.issue_date, issue.student_id, books.availability, 
    CASE WHEN NOW() > DATE_ADD(issue.issue_date, INTERVAL 20 DAY) 
     THEN 10*DATEDIFF(NOW(), DATE_ADD(issue.issue_date, INTERVAL 20 DAY)) 
     ELSE 0 END AS fine_amount 
     FROM issue 
     INNER JOIN books 
     ON issue.book_id=books.book_id 
     WHERE books.book_id=2 
     ORDER BY issue.issue_id DESC LIMIT 1

扭转比较会更高效 – Strawberry

sql查询不返回最大值

相关推荐