首页 / MYSQL / mysql – 如何选择两列为MAXIMUM的位置

mysql – 如何选择两列为MAXIMUM的位置

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何选择两列为MAXIMUM的位置，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2688字，纯文字阅读大概需要4分钟。

内容图文

我创建了一个视图,以便继续使用它,而不必在每次视图称为“数据”时编写这部分代码
第一个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：
这是一个系列观察列表和视图数据来自一个表格,我在其中保存每个用户观看的剧集
通过该视图,我获得了剧集,系列,用户的数据

解决方法:

您可以尝试使用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 )

第一个确保没有新的季节存在,第二个没有新的情节存在.

内容总结

以上是互联网集市为您收集整理的mysql – 如何选择两列为MAXIMUM的位置全部内容，希望文章能够帮你解决mysql – 如何选择两列为MAXIMUM的位置所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/905011.html

来源：【匿名】