mysql - MYSQL 中的 COALESCE 函数与 select 出现错误

Question

我想知道我的查询有什么问题，这对我来说很简单，请也看看，如果我错过了什么，请告诉我

SELECT users.*, p.*, 
COALESCE(SELECT picture_location FROM pictures 
WHERE user_id = 'patient' AND default_pic = 1, 
SELECT picture_location FROM pictures 
WHERE user_id = 'patient' ORDER BY id ASC LIMIT 1) AS default_pic, 
pic.picture_location, MONTHNAME(users.registered_on) AS month_reg,
YEAR(users.registered_on) AS year_reg FROM users 
LEFT JOIN profile p ON p.profile_id = users.profile_id 
LEFT JOIN pictures pic ON pic.user_id = p.profile_id 
WHERE users.pseudo = 'patient' GROUP BY users.pseudo 
ORDER BY pic.default_pic DESC LIMIT 1

你看到什么了吗？它返回给我这个:

1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行 'SELECT picture_location FROM pictures WHERE user_id = 'patent' AND default_pic ' 附近使用的正确语法

Answer 1

嵌套的select需要额外的一层括号:

SELECT users.*, p.*,
       COALESCE((SELECT picture_location FROM pictures WHERE user_id = 'patient' AND default_pic = 1),
                (SELECT picture_location FROM pictures WHERE user_id = 'patient' ORDER BY id ASC LIMIT 1)) AS default_pic,
       pic.picture_location,
       MONTHNAME(users.registered_on) AS month_reg, YEAR(users.registered_on) AS year_reg
FROM users LEFT JOIN
     profile p
     ON p.profile_id = users.profile_id LEFT JOIN
     pictures pic
     ON pic.user_id = p.profile_id
WHERE users.pseudo = 'patient'
GROUP BY users.pseudo
ORDER BY pic.default_pic DESC
LIMIT 1;