我有一个表(系列),我想获得系列第一集信息以及其上一个可用的季节和情节。我能够使用以下查询来完成此操作:
SELECT
`video`.*,
`cat`.`en_title` categoryName,
`cat`.`id` categoryNb,
`cat`.`ar_title` category_ar_title,
`cat`.`en_title` category_en_title,
MAX(`video2`.`season`) LatestSeason,
MAX(`video2`.`episodeNumber`) LatestEpisode
FROM
(`video_item` video,
`category` cat,
`video_item_category` video_cat,
`video_item` video2)
WHERE
`video2`.`rootSeries` = `video`.`id`
AND `video_cat`.`video_itemId` = video.id
AND `video_cat`.`categoryId` = cat.id
AND (`video`.published = 1)
AND `video`.`rootSeries` = 0
AND (video.kind =2)
AND (video.seriesRating not in (-1))
GROUP BY
`video`.`id`
ORDER BY
`video`.`season` asc, `video`.`episodeNumber` asc, `video`.`updateDateTime` desc
LIMIT
15
我使用自我加入和最大功能将上一季和情节附加到第一集信息结果中。但是数字是很正确的,您会发现我有一个"已发布"列,并且返回的最大值未被其过滤。
关于如何执行此操作的任何建议?
我使用自加入和一个子查询解决了它:
SELECT
`video`.*,
`cat`.`en_title` categoryName,
`cat`.`nb` categoryNb,
`cat`.`ar_title` category_ar_title,
`cat`.`en_title` category_en_title,
`video2`.`LatestSeason` ,
`video2`.`LatestEpisode`
FROM
(`video_item` video,
`category` cat,
`video_item_category` video_cat)
join (select
rootSeries,
MAX(season) LatestSeason,
MAX(episodeNummer)LatestEpisode
from
video_item
where
published = 1
GROUP BY
id
) video2 on `video2`.`rootSeries` = `video`.`id`
WHERE
`video_cat`.`video_itemId` = video.id
AND `video_cat`.`category_Id` = cat.id
AND (`video`.published = 1)
AND `video`.`rootSeries` = 0
AND (video.kind =2)
AND (video.seriesRating not in (-1))
GROUP BY
`video`.`id`
ORDER BY
`video`.`season` asc, `video`.`episodeNummer` asc, `video`.`updateDateTime` desc
LIMIT
15