选择不在选定字段中的字段- MySQL

Question

我有一张包含歌曲的桌子。我想选择前3首最喜欢的歌曲和3首随机歌曲，这不是在第一个在一个查询。

例如：

和预期产出：

song 6, song 8, song 5 then *whatever song*,*whatever song*,*whatever song*

在这里，我试过但不起作用：

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)

我使用的是MariaDB 10.1.29，它与子查询或WITH子句中的LIMIT不兼容。

Answer 1

目前我只安装了MariaDB 10.3，但是我已经在旧版的MySQL 4.1上尝试了下面的语法，这两个语法都返回相同的结果。

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;

Answer 2

在MariaDb 10.2和MySql 8.0中使用2个有限制的CTE

第二个CTE是随机的，而不是从第一个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;

这在MySql 5.6中是可行的，所以在MariaDb 10.1.29中也很可能如此。

(我无法访问10.1，所以无法验证)

这一次使用到top3的左联接。

然后，将top3置于极限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;

db<>fiddle https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=401f69377cd85223e5173c147a294e50的测试

Answer 3

您应该将其拆分为两个，并合并结果:第一个查询选择前3：

SELECT song_id FROM song ORDER BY liked DESC LIMIT 3

第二个查询选择随机三首，不包括以上所选歌曲：

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

现在大家齐心协力：

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