mysql - 带有一些 JOINS 的 MIN 和 MAX

Question

我正在尝试显示 MIN 和 MAX，同时加入几个表以获取它们的信息。我得到了最小值和最大值，但它显示了我的其他字段的错误信息。

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 |

Answer 1

您可以从 SELECT 中删除 MAX 和 MIN，并添加 TOP 1。然后添加 ORDER BY SONG.LENGTH_SECONDS 以获取最短的歌曲，并添加 ORDER BY SONG.LENGTH_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