基于DateTime值的同一表上的有效SQL子查询

Question

下面我有一个简单的查询来获得今天加入"事件"表和"电影"表的所有电影评分。

Select e.*, m.moviename 
From Event e, movie m 
Where e.eventdate >= DATEADD(day, -1, GETDATE()) 
  and e.moviekey = m.moviekey 
order by e.Ratings desc;

问题

在上面的示例中，您将如何从1周前（1个月前）检索评分。因此，查询将返回2个额外的列CratingoneMonthago，RatingsOneWeekago等。

我研究了子征服，它没有单击任何帮助。

谢谢

Answer 1

您可以使用ctes将此信息提取（类似于使用子查询）。

以下查询假设您每天都有评分，并且没有重复（同一天的同一电影的多个评分）：

WITH cteOneWeekAgo
AS
(
    SELECT
        moviekey
        , Ratings
    FROM Event
    WHERE CAST(eventdate AS date) = DATEADD(WEEK, -1, CAST(GETDATE() AS date))
)
,
cteOneMonthAgo
AS
(
    SELECT
        moviekey
        , Ratings
    FROM Event
    WHERE CAST(eventdate AS date) = DATEADD(MONTH, -1, CAST(GETDATE() AS date))
)
SELECT
    e.*
    , m.moviename
    , w.Ratings Ratings_OneWeekAgo
    , mth.Ratings Ratings_OneMonthAgo
FROM
    Event e
    JOIN movie m ON e.moviekey = m.moviekey
    LEFT JOIN cteOneWeekAgo w ON e.moviekey = w.moviekey
    LEFT JOIN cteOneMonthAgo mth ON e.moviekey = mth.moviekey
WHERE e.eventdate >= DATEADD(DAY, -1, GETDATE())
ORDER BY e.Ratings DESC

我还写了一个更复杂的查询，如果不存在该日期的评分，它将在您寻找的日期之前吸引电影的最新评分。

WITH cteOneWeekAgo
AS
(
    SELECT
        moviekey
        , Ratings
        , eventdate
    FROM
        (
            SELECT
                moviekey
                , Ratings
                , eventdate
                , ROW_NUMBER() OVER (PARTITION BY moviekey ORDER BY eventdate DESC) R
            FROM Event
            WHERE CAST(eventdate AS date) <= DATEADD(WEEK, -1, CAST(GETDATE() AS date))
        ) Q
    WHERE R = 1
)
,
cteOneMonthAgo
AS
(
    SELECT
        moviekey
        , Ratings
        , eventdate
    FROM
        (
            SELECT
                moviekey
                , Ratings
                , eventdate
                , ROW_NUMBER() OVER (PARTITION BY moviekey ORDER BY eventdate DESC) R
            FROM Event
            WHERE CAST(eventdate AS date) <= DATEADD(MONTH, -1, CAST(GETDATE() AS date))
        ) Q
    WHERE R = 1
)
SELECT
    e.*
    , m.moviename
    , w.eventdate Ratings_OneWeekAgo_MostRecentDate
    , w.Ratings Ratings_OneWeekAgo
    , mth.eventdate Ratings_OneMonthAgo_MostRecentDate
    , mth.Ratings Ratings_OneMonthAgo
FROM
    Event e
    JOIN movie m ON e.moviekey = m.moviekey
    LEFT JOIN cteOneWeekAgo w ON e.moviekey = w.moviekey
    LEFT JOIN cteOneMonthAgo mth ON e.moviekey = mth.moviekey
WHERE e.eventdate >= DATEADD(DAY, -1, GETDATE())
ORDER BY e.Ratings DESC