Select fields that are not in selected fields - MySQL

问题: 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: table of songs 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
  • 发表于 2019-03-13 18:51
  • 阅读 ( 199 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除