mysql - sql - 聚合最大函数和分组依据

Question

select city, title, releasedate
from movies join shownat on shownat.movietitle = movies.title join theatres on theatres.theatrename = shownat.theatrename
group by city, title, releasedate
order by city, max(releasedate) desc;

以上是我的查询，以及要解决的问题。

查找每个城市上映的最新电影的标题。按城市名称和电影标题排序显示城市名称和最新电影标题。

标准化数据有 3 个表，分别称为“theatres”、“showat”和“movies”。是的，我意识到发布日期在显示表上可能会更有效，对我来说这更有意义，因为视频并不总是在同一日期在同一地区或城市发布。然而，这是我最后一个被困住的作业问题。需要的是该城市应该只有 1 个列表。我认为按城市分组会处理这个问题。对于每个城市，我需要数据集中每个特定城市(4)具有最新发行日期的电影的标题。我不确定为什么当我有此功能的分组及其用于处理聚合最大函数时，我会在这里得到重复项。 max 函数应该只给我最新版本，是吗？

"CITY"          "TITLE"        "RELEASEDATE"
"Cincinnati"    "Interstellar"  07-NOV-14
"Cincinnati"    "Big Hero 6"    07-NOV-14
"Cincinnati"    "Nightcrawler"  31-OCT-14
"Cincinnati"    "Gone Girl"     03-OCT-14
"Cincinnati"    "The Pianist"   03-JAN-03
"Cincinnati"    "Fargo" 05-APR-96
"Cincinnati"    "Schindler's List"  04-FEB-94
"Florence"      "Big Hero 6"    07-NOV-14
"Florence"      "Interstellar"  07-NOV-14
"Florence"      "Nightcrawler"  31-OCT-14
"Florence"  "Gone Girl" 03-OCT-14
"Florence"  "District 9"    14-AUG-09
"Florence"  "A Perfect Getaway" 07-AUG-09
"Florence"  "Aliens in the Attic"   31-JUL-09
"Florence"  "Away We Go"    26-JUN-09
"Florence"  "Up"    29-MAY-09
"Florence"  "Star Trek" 08-MAY-09
"Florence"  "The Hurt Locker"   10-OCT-08
"Florence"  "The Dark Knight"   18-JUL-08
"Florence"  "The Departed"  06-OCT-06
"Florence"  "The Green Mile"    10-DEC-99
"Newport"   "Interstellar"  07-NOV-14
"Newport"   "Big Hero 6"    07-NOV-14
"Newport"   "Gone Girl" 03-OCT-14
"Newport"   "District 9"    14-AUG-09
"Newport"   "A Perfect Getaway" 07-AUG-09
"Newport"   "Away We Go"    26-JUN-09
"Newport"   "Up"    29-MAY-09
"Newport"   "The Departed"  06-OCT-06
"Wilder"    "Big Hero 6"    07-NOV-14
"Wilder"    "Interstellar"  07-NOV-14
"Wilder"    "Gone Girl" 03-OCT-14
"Wilder"    "Public Enemies"    01-JUL-09
"Wilder"    "The Departed"  06-OCT-06

Answer 1

这将是查询，但我不太确定 max 函数是否适用于日期变量:

SELECT city, title, max (releasedate) as max_dateRelease
FROM movies inner join shownat on shownat.movietitle = movies.title join theatres 
on theatres.theatrename = shownat.theatrename
GROUP BY city, title, releasedate
ORDER BY city, max_dateRelease desc