使用ifnull和子查询错误选择SQL错误[1242][21000]:子查询返回1行以上

Question

我想从某个子查询中获取数据，但它只返回SQL错误[1242][21000]：子查询返回的行超过1行，我的代码有问题吗？

SELECT
u.id user_id, p.id user_profileid, p.firstname, p.lastname,
IFNULL((SELECT SUM(ROUND((DATEDIFF(end_date, start_date) / 365))) FROM employee_experience WHERE user_profileid = p.id GROUP BY user_profileid),0) AS experience,
(SELECT jobtitle FROM employee_experience WHERE end_date IN (SELECT MAX(end_date) FROM employee_experience GROUP BY user_profileid) AND user_profileid = p.id) AS last_jobtitle,
IFNULL((SELECT lastsalary FROM employee_experience WHERE end_date IN (SELECT MAX(end_date) FROM employee_experience GROUP BY user_profileid) AND user_profileid = p.id),0) AS last_salary
FROM
users u
LEFT JOIN employee_profile p ON (u.id = p.user_id)
LEFT JOIN employee_experience e ON (p.id = e.user_profileid)
WHERE
(u.roleid = '2') and (u.isverified = '1')
GROUP BY
u.id, p.id

Answer 1

ifnull只适用于1个值，并且您的子查询可以返回多个值，要解决此问题，可以在子查询中使用limit 1

SELECT 
u.id user_id, p.id user_profileid, p.firstname, p.lastname, IFNULL((SELECT
SUM(ROUND((DATEDIFF(end_date, start_date) / 365))) FROM
employee_experience WHERE
user_profileid = p.id GROUP BY
user_profileid limit 1),0) AS experience,
(SELECT jobtitle FROM
employee_experience WHERE end_date
IN (SELECT MAX(end_date) FROM
employee_experience GROUP BY
user_profileid) AND user_profileid =
p.id) AS last_jobtitle, IFNULL((SELECT
lastsalary FROM employee_experience
WHERE end_date IN (SELECT
MAX(end_date) FROM employee_experience GROUP BY
user_profileid) AND user_profileid = p.id
limit 1),0) AS last_salary FROM users u
LEFT JOIN employee_profile p ON (u.id =
p.user_id) LEFT JOIN employee_experience e ON (p.id =
e.user_profileid) 
WHERE 
(u.roleid = '2')
and (u.isverified = '1') 
GROUP BY u.id, p.id