MySQL在连续剧中找到前一集？

Question

SET @current_episode = 1;
SET @current_season = 2;
SET @current_serial = 2;

SELECT * FROM `episode` _episode 
    LEFT JOIN season _season ON _episode.season_id = _season.id
    LEFT JOIN serial _serial ON _season.serial_id  = _serial.id 
WHERE 

    (_episode.episode < @current_episode AND _season.season = @current_season)
        OR 
    (_episode.episode > @current_episode AND _season.season = (@current_season - 1))

ORDER BY _season.season DESC, _episode.episode ASC
LIMIT 0,1

集表结构：

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| season_id   | int(11)      | YES  | MUL | NULL    |                              |
| poster_id   | int(11)      | YES  | MUL | NULL    |                |
| episode     | int(11)      | NO   |     | NULL    |                              |
+-------------+--------------+------+-----+---------+----------------+

季节表结构：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| serial_id  | int(11)      | YES  | MUL | NULL    |                |
| season     | int(11)      | NO   |     | NULL    |                |

串行表结构：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |

我的SQL无法正确找到前一集，因为如果我尝试找到它的季节DESC，它将是近似值，而插集号不是第一集的季节。

Answer 1

查找前一集

只需在WHERE之后更新OR子句，并在_episode.episode上对DESC进行排序

SET @current_episode = 1;
SET @current_season = 2;
SET @current_serial = 2;

SELECT * FROM `episode` _episode 
    LEFT JOIN season _season ON _episode.season_id = _season.id
    LEFT JOIN serial _serial ON _season.serial_id  = _serial.id 
WHERE 

    (_episode.episode < @current_episode AND _season.season = @current_season)
        OR 
    (_season.season < @current_season)

ORDER BY _season.season DESC, _episode.episode DESC
LIMIT 0,1