获取一个查询，该查询可以根据表数据而不是当前日期显示30天

Question

现在我想从3个表及其行中提取数据，如下所示:
**RentalAgreement – CustID,AgreementID(PK) and AgreementDate **Movie Rented – AgreementID(PK), RentalAmount and PercentReductionApplied,MovieID(FK) **Movie – MovieID(PK), Name, Released

我需要显示最近30天的数据基于表，而不是当前的数据。我怎样才能做到这一点呢?我的尝试:

SELECT TRIM(CustID),AgreementDate , RentalAmount    
             ,PercentReductionApplied , MovieID , Name , Released
FROM RentalAgreement, MovieRented, Movie
WHERE RentalAgreement.AgreementID = MovieRented.AgreementID
AND Movie.MovieID = MovieRented.MovieID
AND AgreementDate BETWEEN MAX(AgreementDate) AND MAX(AgreementDate)-30;

Answer 1

问题是MAX(agreementdate)。这是一个聚合函数——您只能在处理完WHERE子句之后，在查询的最后阶段(在SELECT子句中)访问它。这意味着您需要一个不同的查询来首先计算MAX -并将其用作总体(外部)查询中的子查询。

最好使用现代的SQL标准连接语法。把这两个放在一起:

SELECT TRIM(CustID),AgreementDate , RentalAmount    
             ,PercentReductionApplied , MovieID , Name , Released
FROM RentalAgreement
     join MovieRented on RentalAgreement.AgreementID = MovieRented.AgreementID
     join Movie       on Movie.MovieID               = MovieRented.MovieID
     cross join (select max(agreementdate) as max_date from rentalagreement) m
where AgreementDate BETWEEN m.max_date AND m.max_date - 30;

为什么需要TRIM(custID)?这很不寻常。同样奇怪的是，在MovieRented中，AgreementID是主键。但这些不是你的查询不工作的原因。

Answer 2

声明@开始日期日期时间，@结束日期日期时间

SELECT @StartDate = MAX(AgreementDate) FROM RentalAgreementSET @EndDate = DATEADD(DAY，-30，@StartDate)

SELECT TRIM(CustID)，AgreementDate, RentalAmount
，PercentReductionApplied, MovieID, Name, Released来自RentalAgreement, MovieRented, MovieRentalAgreement的地方。协议编号= MovieRented。AgreementID和电影。MovieID = MovieRented。MovieID@StartDate和@EndDate之间的协议日期