题解 | 三表连接

旧SQL语句:
SELECT DISTINCT
	( m.music_name ) 
FROM
	follow AS f
	LEFT JOIN music_likes AS ml ON f.follower_id = ml.user_id
	INNER JOIN music AS m ON ml.music_id = m.id 
WHERE
	f.user_id = 1 
	AND ml.music_id NOT IN ( SELECT music_id FROM music_likes WHERE user_id = 1 ) 
ORDER BY
	m.id ASC

如果你们遇到这个报错，SQL_ERROR_INFO: "Expression #1 of ORDER BY clause is not in SELECT list, 
references column 'm.id' which is not in SELECT list; this is incompatible with DISTINCT"，
原因是mysql5.7.5及以上版本将sql_mode的ONLY_FULL_GROUP_BY模式默认设置为打开状态，会导致一些错误。

之前代码我是能通过的，不知道为什么现在不能了。你们就用以下代码，我提交通过了，虽然这样使用，效率会变低:

新SQL语句:
SELECT music_name from 
(
    SELECT DISTINCT
        ( m.music_name ) , m.id
    FROM
        follow AS f
        LEFT JOIN music_likes AS ml ON f.follower_id = ml.user_id
        INNER JOIN music AS m ON ml.music_id = m.id
    WHERE
        f.user_id = 1
        AND ml.music_id NOT IN ( SELECT music_id FROM music_likes WHERE user_id = 1 )
) as t1        
ORDER BY id ASC

新SQL语句提交结果图片链接（输入以下小括号网址即可看到图片）:
![alt](https://uploadfiles.nowcoder.com/images/20220214/145242520_1644829205614/4D49D98C5089EFB59F98358B89B4DF6B)
![alt](https://uploadfiles.nowcoder.com/images/20220214/145242520_1644829345721/B4EAFB920E5B9DB1293CBA26EFD341DE)