WHERE子句中的MYSQL MIN [英] MYSQL MIN in WHERE clause

问题描述

好，所以我正在研究家庭影院系统，并且要显示电视连续剧的清单，我一直在选择第一季的第一集，并仅显示该集的相关信息，然后您可以向下钻取给别人.

OK, so I'm working on a home theater system, and to display the list of TV series I have been selecting the first episode of the first season and displaying the associated information for just that episode, then you can drill down to others.

这样，我可以将所有电影和所有电视节目保存在一个表中，这使播放功能更加容易.

That way I can keep all the movies and all the TV shows in a single table which makes the playback functions much easier.

问题是，某些系列我的数据库中没有第一集，而且我还是希望它们出现，所以我想选择最小的季节/系列而不是第一季.

The problem is, some of the series I don't have the first episode in the database, and I want them to appear anyway, so I want to select the minimum season/series instead of the first.

我不能在选择子句中放置MIN，因为它只能选择一个系列，并且我不能在WHERE子句中放置LEAST，因为它说这只是一个元素，因为它们的方式被分组了，我不能使用LIMIT，因为再一次它只会选择一个系列.

I can't put MIN in the select clause as then it only selects one series, and I can't put LEAST in the WHERE clause because it says it's only one element because of the way they're grouped, and I can't use LIMIT because again then it will only select one series.

是否可以在没有多个复杂子查询的情况下做到这一点?

Is there a way to do this without multiple complicated subqueries?

这是我目前正在使用的查询，CASE子句是从系列标题中删除A/An/The以正确的字母顺序:

Here's the query I'm using at the moment, the CASE clause is to remove A/An/The from the series title for proper alphabetical order:

SELECT *, CASE when substring(series,1,4) = 'The ' then substring(series, 5)
               when substring(series,1,3) = 'An ' then substring(series, 4)
               when substring(series,1,2) = 'A ' then substring(series, 3)
               else series END AS sorttitle 
FROM Theater.Videos WHERE type='TV' and season=1 and episode=1 ORDER BY sorttitle ASC

这实际上是我想要的:

SELECT *, CASE when substring(series,1,4) = 'The ' then substring(series, 5)
               when substring(series,1,3) = 'An ' then substring(series, 4)
               when substring(series,1,2) = 'A ' then substring(series, 3)
               else series END AS sorttitle 
FROM Theater.Videos WHERE type='TV' and season=MIN(season) and episode=MIN(episode) ORDER BY sorttitle ASC

推荐答案

尝试这些查询(经过适当的修改)...

Try these queries (after suitable modification)...

要获取所有系列的清单以及第一(最短)季的第一集，请使用此...

To get list of all series along with first episode of the first (min) season use this...

select distinct v1.*
from videos v1,
    (select series, min(season) as min_season from videos group by series) as v2,
    (select series, season, min(episode) as min_episode from videos group by series, season) as v3
where v1.series = v2.series
and v1.season = v2.min_season
and v2.series = v3.series
and v2.min_season = v3.season
and v1.episode = v3.min_episode

要获取所有系列的清单以及每个季节的第一集，请使用此...

To get list of all series along with first episode of each season use this...

select distinct v1.*
from videos v1,
    (select series, season, min(episode) as min_episode from videos group by series, season) as v3
where v1.series = v3.series
and v1.season = v3.season
and v1.episode = v3.min_episode

这篇关于WHERE子句中的MYSQL MIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！