在mysql中创建一个月内未使用的视频视图

Question

大家好。

这一次，我想创建一个视图，所有的视频在这个月没有被借用。表的结构如下:

<表类> 事务 c_id v_id loan_date return_loan rental_value 期 tbody><<tr>1322021-08-102021-08-1210.002021 - 08年

这将向您展示未借出的视频的所有周期

CREATE VIEW `Video not rented` AS 
SELECT  loan.period, loan.v_id, COUNT(*) AS amount
FROM loan 
GROUP BY loan.v_id,loan.period
HAVING  amount = 0;

可以用来获取最后一个句点

CREATE VIEW `Video not rented` AS 
SELECT  MAX(loan.period), loan.v_id, COUNT(*) AS amount
FROM loan 
GROUP BY loan.v_id
HAVING  amount = 0;

您可以使用left join。假设您有一个视频表:

CREATE VIEW videos_not_rented_this_month
SELECT v.*
FROM videos v LEFT JOIN
loan l
ON l.v_id = v.v_id AND
l.loan_date >= curdate() + interval (1 - day(curdate())) day
WHERE l.v_id IS NULL;   -- videos with no match