mysql – 如何选择两列为MAXIMUM的位置
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – 如何选择两列为MAXIMUM的位置,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2688字,纯文字阅读大概需要4分钟。
内容图文
![mysql – 如何选择两列为MAXIMUM的位置](/upload/InfoBanner/zyjiaocheng/906/1ce4be329b3a4b51999886483c86ec9e.jpg)
我创建了一个视图,以便继续使用它,而不必在每次视图称为“数据”时编写这部分代码
第一个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 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。