使用最大和分组两次

本文介绍了使用最大和分组两次的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我阅读了有关此主题的一些文章，包括:在两次中使用GROUP BY MySQL ，并且知道这应该与我需要的逻辑相同，但是我只是不知道如何应用它.

I read some articles on this topic including: Use of GROUP BY twice in MySQL and know this should be the same logic I need but I just don’t know how to apply it.

请注意，我知道架构很糟糕(例如，我仍然不明白为什么版本表和发布表是分开的…….)但我无权对其进行更改.

Please note that I know the schema is terrible (I still don’t understand why the edition and published tables are separate for example….) but I don’t have any power to change it.

(我有一些正在使用的敏感信息，所以我将使用一个使用电影的示例来模拟我的问题.)

(I have some sensitive information that I am working with so I will model my problem using an example using movies instead.)

Tables: Title TitleID MovieTitle 1 Great Movie #1 2 Great Movie #2 Edition StockNo editionID EditionText Media TitleID 1 1 Regular Edition DVD 1 2 1 Regular Edition Blue-ray 1 3 2 Extended Version DVD 1 4 2 Extended Version Blue-ray 1 5 1 Regular Edition DVD 2 6 1 Regular Edition Blue-ray 2 7 2 Extended Version DVD 2 8 2 Extended Version Blue-ray 2 Published StockNo DatePublished 1 1999.01.01 2 2003.01.01 3 2000.01.01 4 1999.01.01 5 1997.01.01 6 1998.01.01 7 2012.01.01 8 2009.01.01

我想返回一行，每一行都是其中一个标题的版本.无论媒体如何，我都想为每个标题版本返回最新的发布日期.

I want to return rows with each row being an edition of one of the titles. For each edition of the title I want to return the latest published date, regardless of the media.

例如:

大动作#1，普通版，最新发布日期

Great move #1, Regular version, Latest_published_date

大电影#1，扩展版本，最新发布日期

Great movie #1, Extended version, Latest_published_date

第2步大动作，普通版，最新发布日期

Great move #2, regular version, Latest_published_date

大电影#2，扩展版本，最新发布日期

Great movie #2, extended version, Latest_published_date

我只是失去了逻辑的海....

I’m just lost in a sea of logic….

WITH Datespublished AS ( Select tt.titleid ,ed.editionID ,pb.datepublished FROM title tt left join edition ed on tt.titleid=ed.titleid left join published pb on pb.stockno=ed.stockno ) select titleid, editionID, max (datepublished) as maxdate from Datespublished group by titleid THEN editionID?!?!?!?

推荐答案

您可以使用逗号将多个列分组以将它们分组.更改:

You can group by on multiple columns by separating them with a comma. Change:

group by titleid THEN editionID

收件人:

group by titleid, editionID