如果子查询有多个结果,如何从select语句中的子查询返回一行?

dkqlctbz  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(420)

我有个问题:

select a.*, b.*, (select c.* from tableC c where c.id_tableA = a.id) from tableA a inner join tableB b on a.id = b.id_tableA where b.id_user = 50;

子查询(即tablec)按预期返回了不止一行。如何从tablec中只返回一行,以便与查询的其余部分匹配?
到目前为止,我已经尝试过:

(select c.* from tableC c where c.id_tableA = a.id limit 1)

它没有像mysql说的那样工作:
“操作数应包含1列”

mcdcgff0

mcdcgff01#

如果表c中的列id是主键,那么它应该没有问题,但是如果不是,请尝试添加另一个条件来过滤子查询结果,例如,
例如,以下是开始日期:

SELECT a.column_1, b.column_2, 
        (SELECT column_3 FROM tableC  
        WHERE (id = a.id 
        AND (start_date = (SELECT MAX(b.start_date) 
             from tableC as c
             where a.id = c.id ))) AS column_3
FROM tableA as a INNER JOIN
     tableB as b ON b.id = a.id
WHERE b.id_user = 50;
of1yzvn4

of1yzvn42#

你在混合两种东西。中的标量子查询 SELECT 列表应该只返回一个值(行和列)。使用 LIMIT 1 将得到一行,但仍有许多列。因此可以指定列名:

select a.*, b.*,
  (select c.col_name from tableC c where c.id_tableA = a.id order by .. limit 1) 
from tableA a 
inner join tableB b on a.id = b.id_tableA 
where b.id_user = 50;

或者用普通的 JOIN :

select a.*, b.*, c.*
from tableA a
inner join tableB b 
  on a.id = b.id_tableA 
left join tableC c
  on c.id_tableA = a.id
where b.id_user = 50;

相关问题