问题:
I have a table contains songs. I want to select top 3 of the most favorite songs and 3 of randomly songs which are not in the first one in one query.
E.g: and expected o...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have a table contains songs. I want to select top 3 of the most favorite songs and 3 of randomly songs which are not in the first one in one query.
E.g: and expected output:
song 6, song 8, song 5 then *whatever song*,*whatever song*,*whatever song*
Here what I tried but not working:
SELECT a.`song_id`, a.`name`, a.liked, b.song_id
FROM `song` AS a,
(SELECT song_id FROM song ORDER BY liked DESC LIMIT 3) as b
WHERE a.song_id <> b.song_id LIMIT 3
SELECT
a.`song_id`, a.`name`, a.liked, b.ids
FROM
`song` AS a,
(SELECT substring_index(GROUP_CONCAT(DISTINCT song_id ORDER BY liked DESC SEPARATOR ','), ',', 3) as ids FROM song) as b
WHERE
a.song_id not in(b.ids)
I'm using MariaDB 10.1.29, it isn't compatible with LIMIT
in subquery or WITH
clause.
回答1:
I only have MariaDB 10.3 installed at the moment but I've tried the syntax below on an older MySQL 4.1 and both return the same results.
SELECT * FROM (
(SELECT * FROM song ORDER BY liked DESC LIMIT 3) UNION
(SELECT A.* FROM song A LEFT JOIN (SELECT * FROM song ORDER BY liked DESC LIMIT 3) B ON A.song_id=B.song_id
WHERE B.song_id IS NULL ORDER BY RAND() LIMIT 3)) S ORDER BY liked DESC;
回答2:
Using 2 CTE's with a LIMIT works in MariaDb 10.2 and MySql 8.0
The second CTE does it random, while not selecting those from the first CTE
WITH TOP3SONGS AS
(
SELECT *
FROM song
ORDER BY liked DESC
LIMIT 3
),
RANDOM3SONGS AS
(
SELECT *
FROM song
WHERE song_id NOT IN (select song_id from TOP3SONGS)
ORDER BY rand()
LIMIT 3
)
SELECT *
FROM TOP3SONGS
UNION ALL
SELECT *
FROM RANDOM3SONGS;
And this works in MySql 5.6, so most likely also in MariaDb 10.1.29
(I don't have access to a 10.1 so can't verify)
This time a left join to the top3 is used.
And the order by puts the top3 above the random before the limit 6.
SELECT song.*
FROM song
LEFT JOIN
( SELECT song_id, liked
FROM song
ORDER BY liked DESC
LIMIT 3
) top3songs ON top3songs.song_id = song.song_id
ORDER BY top3songs.liked DESC, RAND()
LIMIT 6;
A test on db<>fiddle here
回答3:
You should split it to two, and union the results: first query to select the top 3:
SELECT song_id FROM song ORDER BY liked DESC LIMIT 3
second query to select random three, not including the above selected songs:
SELECT song_id FROM song WHERE song_id NOT IN (
SELECT song_id FROM song ORDER BY liked DESC LIMIT 3
) ORDER BY RAND() LIMIT 3
and all together now:
SELECT song_id FROM song ORDER BY liked DESC LIMIT 3
UNOION
SELECT song_id FROM song WHERE song_id NOT IN (
SELECT song_id FROM song ORDER BY liked DESC LIMIT 3
) ORDER BY RAND() LIMIT 3