MYSQL-子查询返回1行以上-如何复制具有相同userId的行

Question

这是我的子查询，我无法理解错误：1242-子查询返回超过1行

SELECT DISTINCT u.id as userId,  
(
SELECT a10.description 
FROM Export exp 
INNER JOIN Account a10 ON (a10.id = exp.accountId)
WHERE exp.isActive = 1 
AND exp.platformId = 14
) as structur
FROM User u
LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
LEFT JOIN Account a ON a.id = au.accountId
WHERE u.id > 0
ORDER BY u.id ASC
LIMIT 10

错误：1242-子查询返回超过1行

SELECT DISTINCT u.id as userId,  
(
SELECT a10.description 
FROM Export exp

感谢您的帮助

Answer 1

您的子查询返回多行，但行单元格只接受一个值，因此如果您确实需要此子查询，则应限制结果

SELECT DISTINCT u.id as userId,  
(
SELECT a10.description 
FROM Export exp 
INNER JOIN Account a10 ON (a10.id = exp.accountId)
WHERE exp.isActive = 1 
AND exp.platformId = 14
LIMIT 1
) as structur
FROM User u
LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
LEFT JOIN Account a ON a.id = au.accountId
WHERE u.id > 0
ORDER BY u.id ASC
LIMIT 10

但是根据您的代码，您还可以避免子查询在主查询中添加联接

SELECT DISTINCT u.id as userId,  Account.description structur
FROM User u
LEFT JOIN AccountUser au ON au.userId = u.id and au.isDefault = 1
LEFT JOIN Account a ON a.id = au.accountId
LEFT JOIN exp ON ON Account.id = exp.accountId
AND exp.isActive = 1 
AND exp.platformId = 14
WHERE u.id > 0
ORDER BY u.id ASC
LIMIT 10