如何从两个表中选择最近的时间戳并按所述时间戳排序？

发布于 2024-10-17 05:30:46 字数 807 浏览 6 评论 0原文

我当前的查询：

SELECT Series.*, Episodes.* FROM Series, Episodes
WHERE EpisodeAirDate > '.time().' AND Episodes.SeriesKey = Series.SerieID
GROUP BY Series.SerieTitle ORDER BY Episodes.EpisodeAirDate;

我想从 Episodes.EpisodeAirDate 最接近当前时间 (time()) 的 Episodes 中检索信息。这样我就得到了数据库中的最后一集。

我尝试过

SELECT Series.*, Episodes.*, MIN(Episodes.EpisodeAirDate) AS EpisodeAirDate FROM Series, Episodes
WHERE EpisodeAirDate > '.time().' AND Episodes.SeriesKey = Series.SerieID
GROUP BY Series.SerieTitle ORDER BY EpisodeAirDate;

哪种作品，但 Episode.EpisodeTitle 等与时间戳行不对应。

这里我使用 MIN() 查询 (http://grab.by/8UNY)。我不被允许发布图像，所以也许一个链接就足够了:)

如您所见，SerieTitle 和“时间直到”是正确的，但 EpisodeTitle、EpNo 和 SeasonNo 是第一个查询的内容。

真的很难解释。我希望你能明白这一点！ :)

原文

My current query:

SELECT Series.*, Episodes.* FROM Series, Episodes
WHERE EpisodeAirDate > '.time().' AND Episodes.SeriesKey = Series.SerieID
GROUP BY Series.SerieTitle ORDER BY Episodes.EpisodeAirDate;

I want to retrieve the information from Episodes where Episodes.EpisodeAirDate is the closest to the current time (time()). With this I just get the last episode in the database.

I've tried with

SELECT Series.*, Episodes.*, MIN(Episodes.EpisodeAirDate) AS EpisodeAirDate FROM Series, Episodes
WHERE EpisodeAirDate > '.time().' AND Episodes.SeriesKey = Series.SerieID
GROUP BY Series.SerieTitle ORDER BY EpisodeAirDate;

which kind of works, but Episode.EpisodeTitle etc. does not correspond with the timestamp row.

Here I'm using the MIN()-query (http://grab.by/8UNY). I wasn't allowed to post an image, so perhaps a link will suffice :)

As you can see, the SerieTitle and "Time until" is correct, but the EpisodeTitle, EpNo and SeasonNo is that of the first query.

It's hard to explain, really. I hope you make sense of this! :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

卸妝后依然美 2024-10-24 05:30:46

请尝试以下操作：

SELECT S.*,E.* FROM Series S
JOIN (SELECT SeriesKey, MIN(EpisodeAirDate) MinDate FROM Episodes
    WHERE EpisodeAirDate > NOW() GROUP BY SeriesKey) M
    ON M.SeriesKey = S.SerieID
JOIN Episodes E ON E.SeriesKey = S.SerieID AND E.EpisodeAirDate = M.MinDate
ORDER BY E.EpisodeAirDate;

编辑：添加了 ORDER BY 子句。

Please try this:

SELECT S.*,E.* FROM Series S
JOIN (SELECT SeriesKey, MIN(EpisodeAirDate) MinDate FROM Episodes
    WHERE EpisodeAirDate > NOW() GROUP BY SeriesKey) M
    ON M.SeriesKey = S.SerieID
JOIN Episodes E ON E.SeriesKey = S.SerieID AND E.EpisodeAirDate = M.MinDate
ORDER BY E.EpisodeAirDate;

Edit: Added the ORDER BY clause.

回复收藏 0 原文

~没有更多了~