min&max与一些连接

v8wbuo2f  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(420)

我试图同时显示最小值和最大值,同时加入几个表以获取它们的信息。我得到的最小值和最大值,但它显示了我的其他领域的错误信息。

SELECT
    SONG.SONG_NAME,
    ALBUM.ALBUM_NAME,
    ARTIST.ARTIST_NAME,
    MAX(SONG.LENGTH_SECONDS) AS LENGTH
FROM SONG
    JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
    JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID
UNION
SELECT
    SONG.SONG_NAME,
    ALBUM.ALBUM_NAME,
    ARTIST.ARTIST_NAME,
    MIN(SONG.LENGTH_SECONDS)
FROM SONG
    JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
    JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID;

以下是我通过查询得到的结果:

| SONG_NAME              | ALBUM_NAME                | ARTIST_NAME    | LENGTH |
+------------------------+---------------------------+----------------+--------+
| Fluorescent Adolescent | Favourite Worst Nightmare | Arctic Monkeys |    390 |
| Fluorescent Adolescent | Favourite Worst Nightmare | Arctic Monkeys |    177 |
wfauudbj

wfauudbj1#

您可以从“选择”菜单中删除“最大值和最小值”,然后添加一个“顶部1”。然后添加order by song.length\u seconds以获得最短的歌曲,并添加order by song.length\u seconds desc以获得最长的歌曲。

SELECT TOP 1
    SONG.SONG_NAME,
    ALBUM.ALBUM_NAME,
    ARTIST.ARTIST_NAME,
    SONG.LENGTH_SECONDS AS LENGTH
FROM SONG
    JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
    JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID
ORDER BY SONG.LENGTH_SECONDS DESC
UNION
SELECT TOP 1
    SONG.SONG_NAME,
    ALBUM.ALBUM_NAME,
    ARTIST.ARTIST_NAME,
    SONG.LENGTH_SECONDS AS LENGTH
FROM SONG
    JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
    JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID;
ORDER BY SONG.LENGTH_SECONDS
cbeh67ev

cbeh67ev2#

假设您正在为每个艺术家和专辑查找歌曲的最小和最大持续时间,并为这些歌曲获取歌曲名称
您可以使用按歌曲分组的联合。album\u id、album.album\u name、artist.artist\u name与歌曲合并以获得正确的歌曲名称

select song.song_name, t.ALBUM_NAME, t.ARTIST_NAME, t.LENGTH
from song
inner join (
  SELECT 
      SONG.ALBUM_ID
      ALBUM.ALBUM_NAME,
      ARTIST.ARTIST_NAME,
      MAX(SONG.LENGTH_SECONDS) AS LENGTH
  FROM SONG
      JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
      JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID 
  GROUP BY SONG.ALBUM_ID,ALBUM.ALBUM_NAME, ARTIST.ARTIST_NAME
) T on song.album_id = t.album_id and t.LENGTH = song.LENGTH_SECONDS 

UNION 

select song.song_name, t.ALBUM_NAME, t.ARTIST_NAME, t.LENGTH
from song
inner join (
  SELECT 
      SONG.ALBUM_ID
      ALBUM.ALBUM_NAME,
      ARTIST.ARTIST_NAME,
      MIN(SONG.LENGTH_SECONDS) AS LENGTH
  FROM SONG
      JOIN ALBUM ON ALBUM.ALBUM_ID = SONG.ALBUM_ID
      JOIN ARTIST ON ARTIST.ARTIST_ID = SONG.ARTIST_ID 
  GROUP BY SONG.ALBUM_ID,ALBUM.ALBUM_NAME, ARTIST.ARTIST_NAME
) T on song.album_id = t.album_id and t.LENGTH = song.LENGTH_SECONDS

相关问题