时间戳

首页 > 解决方案 > 每个属性值的 SQL 最大值

mysql - 每个属性值的 SQL 最大值

问题描述

我在 SO 上看到了几篇关于选择由另一个属性分组的属性中的最大值的帖子，但我无法复制结果，我不确定我做错了什么。

我收到一个 SQL 异常，提示我检查我的 SQL 版本的手册（使用 MySQL）。

关系

我正在尝试SELECT使用 2018 年的用户 ID 和播放次数最多的歌曲（按名称）。我的尝试如下。

我的理由是获取按用户 ID UID（回答与类似内容相关的其他 SO 帖子并没有太大帮助SIDMAX

SELECT UID, SName
FROM 
(SELECT UID, SName, COUNT(listenDate) AS listen_date_count
    FROM record
    LEFT JOIN song ON song.SID = record.SID
    WHERE YEAR(Date) = 2018
    GROUP BY UID, SName) AS records_2018
GROUP BY UID, SName
HAVING MAX(listen_date_count);

标签： mysqlsql

解决方案

您可以首先限制歌曲。

然后对于前 1 首歌曲，为最高用户设置 LIMIT。

SELECT usermost.UID, song.SName
FROM
(
   SELECT songmost.SID, rec.UID
   FROM
   (
      SELECT SID
      FROM record 
      WHERE YEAR(`Date`) = 2018
      GROUP BY SID
      ORDER BY COUNT(*) DESC, COUNT(DISTINCT UID) DESC
      LIMIT 1
  ) songmost
  JOIN record rec 
    ON rec.SID = songmost.SID AND YEAR(rec.`Date`) = 2018
  GROUP BY rec.SID, rec.UID
  ORDER BY COUNT(*) DESC
  LIMIT 1
) usermost
LEFT JOIN song ON song.SID = usermost.SID

例子：

CREATE TABLE user (
  UID int primary key not null auto_increment, 
  UName varchar(30) not null, 
  State int not null default 0
);

CREATE TABLE artist (
  AID int primary key not null auto_increment, 
  AName varchar(30) not null, 
  Description  varchar(300)
);

create table song (
  SID int primary key not null auto_increment, 
  AID int not null,
  SName varchar(30) not null, 
  ReleaseDate date not null,
  constraint fk_song_aid foreign key (AID) references artist(AID)
);

create table record (
  SID int not null, 
  UID int not null, 
  `Date` date not null,
  constraint fk_record_sid foreign key (SID) references song(SID)
);

insert into user (UName, State) values 
('John Doe', 0), 
('Jane Sheppard', 1);

insert into artist (AName, Description) values 
('Imagine Dragons', 'American pop rock band'),
('Yuki Kajiura', 'Japanese musician, composer and record producer');

insert into song (AID, SName, ReleaseDate) values
(1, 'Whatever it takes', '2017-10-06'),
(2, 'I talk to the rain', '2005-07-05');

insert into  record (SID, UID, `Date`) values 
(1,1,'2018-01-01'),(1,1,'2018-02-01'),(1,2,'2018-03-01'),
(2,1,'2018-04-01'),(2,2,'2018-01-01');

select rec.SID, song.SName, user.UName, artist.AName, COUNT(*) As Total
from record rec
left join song on song.SID = rec.SID
left join user on user.UID = rec.UID
left join artist on artist.AID = song.AID
group by rec.SID, rec.UID

西德 | 姓名 | 姓名 | 姓名 | 全部的
--: | :----------------- | :------------ | :---------------- | ----:
  1 | 不惜一切| 约翰·多伊 | 想象龙 | 2
  1 | 不惜一切| 简·谢泼德 | 想象龙 | 1
  2 | 我对雨说话 | 约翰·多伊 | 梶浦由纪 | 1
  2 | 我对雨说话 | 简·谢泼德 | 梶浦由纪 | 1

SELECT usermost.UID, song.SName
FROM
(
   SELECT songmost.SID, rec.UID
   FROM
   (
      SELECT SID
      FROM record 
      WHERE YEAR(`Date`) = 2018
      GROUP BY SID
      ORDER BY COUNT(*) DESC, COUNT(DISTINCT UID) DESC
      LIMIT 1
  ) songmost
  JOIN record rec 
    ON rec.SID = songmost.SID AND YEAR(rec.`Date`) = 2018
  GROUP BY rec.SID, rec.UID
  ORDER BY COUNT(*) DESC
  LIMIT 1
) usermost
LEFT JOIN song ON song.SID = usermost.SID

识别码 | 名字            
--: | :----------------
  1 | 不惜一切代价

db<>在这里摆弄

推荐阅读