我正在MySQL中处理db,并且需要一行中的几个表中的数据。与相关数据库有关的问题:
CREATE TABLE pet (id INT, name VARCHAR(20));
insert into pet values (1,"Rufus");
insert into pet values (2,"Bali");
insert into pet values (3,"Lolo");
CREATE TABLE own (id INT, own_name VARCHAR(20), own_color VARCHAR(20));
insert into own values (1,"Me", "Red");
insert into own values (2,"Other owners" ,"Green");
CREATE TABLE pet_owner (id INT, id_pet INT, id_own INT);
insert into pet_owner values (1, 1, 1);
insert into pet_owner values (2, 2, 1);
insert into pet_owner values (3, 3, 2);
DROP procedure if exists `pet`;
DELIMITER $$
CREATE procedure `pet`()
BEGIN
set @param = 1;
select
a.own_color as 'color',
(select id_pet from pet_owner where id_own = @param) as 'pets'
from own as a where a.id = @param;
END$$
call pet;
子查询返回1行以上
如何在一行中收集所有宠物id(每个id_pet都可以在另一列中)
您要使用group_concat()
:
select o.own_color as color,
(select group_concat(id_pet) from pet_owner po where po.id_own = @param) as pets
from own o
where o.id = @param;
请注意,我对查询进行了一些更改:
o
对于所有者,而不是a
)。group_concat()
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句