你好,我是SQL的新手,我被要求在租金期限超过平均租金期限的情况下获得租金期限,但我似乎做得不对。我做错了什么?
这是我尝试过的代码,但正在显示错误:
发布于 2020-05-30 20:44:26
您可以使用窗口函数计算总体平均租金,例如:
SELECT RentDuration,
LEFT(StaffName, CHARINDEX(' ', StaffName)-1) AS StaffName,
GroupName,
LineRented,
-- calculate overall average duration:
AVG(RentDuration) OVER () AS AverageRentDuration
FROM RentTransaction AS RT
INNER JOIN Staff AS S ON RT.StaffID=S.StaffID
INNER JOIN CustomerGroup AS CG ON RT.GroupID=CG.GroupID
WHERE DATEPART(month, RentDate)%2=1 -- only Jan, Mar, May, Jul, etc.
但是,您不能在WHERE
子句中放置一个窗口函数,所以我会将上面的查询放在子查询或CTE中。我个人更喜欢CTE,因为它更容易读懂:
WITH rents AS (
SELECT RentDuration,
LEFT(StaffName, CHARINDEX(' ', StaffName)-1) AS StaffName,
GroupName,
LineRented,
-- calculate overall average duration:
AVG(RentDuration) OVER () AS AverageRentDuration
FROM RentTransaction AS RT
INNER JOIN Staff AS S ON RT.StaffID=S.StaffID
INNER JOIN CustomerGroup AS CG ON RT.GroupID=CG.GroupID
WHERE DATEPART(month, RentDate)%2=1 -- only Jan, Mar, May, Jul, etc.
)
SELECT *
FROM rents
WHERE AverageRentDuration>RentDuration; -- only rows with above-average duration.
窗口函数方法还允许您定义窗口;例如,您可能希望将本月的平均租金进行比较,在这种情况下,您可能会使用以下内容
AVG(RentDuration) OVER (PARTITION BY DATEPART(month, RentDate)) AS AverageRentDuration
https://dba.stackexchange.com/questions/268190
复制相似问题