mysql - 如何选择两列最大的位置

Question

我创建了一个 View ，以便继续使用它，而不必在每次 View 调用“数据”时都编写这部分代码
第一个 SQL 语句将为特定用户获取每个系列的最大季节。

SELECT s_imdbID, MAX(ep_season) FROM Data 
WHERE u_ID = 1 
GROUP BY s_imdbID

第二个 SQL 语句 将为我获取特定用户的特定系列的最新一季的最大剧集。

SELECT s_imdbID, ep_season, MAX(ep_episode) FROM Data
WHERE ep_season = (
         SELECT MAX(ep_season) 
         FROM Data 
         WHERE u_ID = 1 
           AND s_imdbID = "tt4158110"
    ) 
  AND s_imdbID = "tt4158110" 
  AND u_ID = 1;

如何将它们整合到一个 SQL 语句中以获得以下结果

seriesID   | Max_Season        | Max_Episode 
-----------| ------------------| ----------- 
Value....  | Value............ |   Value     

这是查看代码在代码中您将能够知道将从中检索的数据类型

SELECT 
    -- episode data
    e.title AS "ep_title", 
    e.year AS "ep_year", 
    e.rated AS "ep_rated", 
    e.released AS "ep_released", 
    e.season AS "ep_season", 
    e.episode AS "ep_episode", 
    e.runtime AS "ep_runtime", 
    e.genre AS "ep_genre", 
    e.director AS "ep_director", 
    e.writer AS "ep_writer", 
    e.actors AS "ep_actors", 
    e.plot AS "ep_plot", 
    e.language AS "ep_language", 
    e.country AS "ep_country", 
    e.awards AS "ep_awards", 
    e.poster AS "ep_poster", 
    e.metascore AS "ep_metascore", 
    e.imdbRating AS "ep_imdbRating", 
    e.imdbVotes AS "ep_imdbVotes", 
    e.imdbID AS "ep_imdbID",
    -- series data
    s.title AS "s_title", 
    s.year AS "s_year", 
    s.rated AS "s_rated", 
    s.released AS "s_released", 
    s.runtime AS "s_runtime", 
    s.genre AS "s_genre", 
    s.director AS "s_director", 
    s.writer AS "s_writer", 
    s.actors AS "s_actors", 
    s.plot AS "s_plot", 
    s.language AS "s_language", 
    s.country AS "s_country", 
    s.awards AS "s_awards", 
    s.poster AS "s_poster", 
    s.metascore AS "s_metascore", 
    s.imdbRating AS "s_imdbRating", 
    s.imdbVotes AS "s_imdbVotes", 
    s.imdbID AS "s_imdbID", 
    -- user data
    u.ID AS "u_ID"/*, */
    /* 
    u.username AS "u_username", 
    u.firstname AS "u_firstname", 
    u.lastname AS "u_lastname", 
    u.password AS "u_password", 
    u.email AS "u_email", 
    u.emailVerificationCode AS "u_emailVerificationCode", 
    u.location AS "u_location", 
    u.accesslevel AS "u_accesslevel", 
    u.disabled AS "u_disabled", 
    u.active AS "u_active"
    */
FROM test w
INNER JOIN users u
     ON u.ID = w.userid
INNER JOIN episode e
     ON w.epid = e.imdbID
INNER JOIN series s
     ON e.seriesID = s.imdbID
WHERE e.seriesID IN (SELECT (imdbID) FROM series);

编辑 1:
这是一个系列观看列表， View 数据来自一个表，我在其中保存每个用户及其观看的剧集
通过那个 View 我得到剧集、系列、用户的数据

Answer 1

您可以尝试使用 NOT EXISTS() :

SELECT * FROM Data t
WHERE NOT EXISTS(SELECT 1 FROM Data s   
                 WHERE t.s_imdbID = s.s_imdbID   
                 AND s.ep_season > t.ep_season)   
 AND NOT EXISTS(SELECT 1 FROM Data p   
                WHERE p.s_imdbID = t.s_imdbID AND p.ep_season = t.ep_season   
                  AND p.ep_episode > t.ep_episode )   

第一个确保不存在更新的季节，第二个确保不存在更新的剧集。