SQL 两表一对多关联，主表某字段保存所有关联的id

表 a, b

CREATE table a (
    id int,
    name varchar(20),
    age int 
);

CREATE table b (
    id int,
    aids varchar(50),
    name varchar(20),
    age int 
);

表a

id	name	age
1	a	11
2	b	22
3	c	33
4	a	11

     

表b

id	aids	name	age
1	1,4	a	11
2		b	22

1、查询操作

一、查询表a 中 name、age的字段值和表b 一样的 id，select 结果 单行展示：返回一条记录（id多个时以逗号隔开），为更新服务

-- mysql
select 
(
    select 
    -- group_concat([DISTINCT] a.id [order by a.id asc] [separator ','])  默认逗号分隔
    group_concat(a.id)
    from a
    where a.name=b.name and a.age=b.age
    group by a.name, a.age
) 
from b   
where b.id=1
-- 结果: 1,4
-- oracle
select 
(
    select 
    (listagg(a.id,',') within group (order by a.id))
    from a
    where a.name=b2.name and a.age=b2.age
) 
from b b2
where b.id=1

二、根据表b 的 aids 字段值查询表a的数据

1、aids 为多个，select 结果 多行展示

mysql 借鉴链接：https://blog.csdn.net/a2899202/article/details/101438213

-- mysql
SELECT id,
substring_index( substring_index( aids, ',', b.help_topic_id + 1 ), ',',- 1 ) aid
FROM b 
JOIN mysql.help_topic b ON b.help_topic_id < ( length( aids ) - length( REPLACE ( aids, ',', '' ) ) + 1 ) 
ORDER BY id
-- oracle
select * from (
    SELECT DISTINCT id,to_char(REGEXP_SUBSTR(aids, '[^,]+', 1, LEVEL, 'i')) AS STR FROM b
    CONNECT BY LEVEL <= length(replace(aids,','))
) tmp
where tmp.STR is not null

 2、查询

-- mysql
select * from a
where id in (
    SELECT 
    substring_index( substring_index( aids, ',', b.help_topic_id + 1 ), ',',- 1 ) aid
    FROM b 
    JOIN mysql.help_topic b ON b.help_topic_id < ( length( aids ) - length( REPLACE ( aids, ',', '' ) ) + 1 ) 
    where id = 1
)
-- oracle
select * from a
where id in (
    SELECT DISTINCT to_char(REGEXP_SUBSTR(aids, '[^,]+', 1, LEVEL, 'i')) AS STR FROM b
    WHERE ID = 1
    CONNECT BY LEVEL <= length(replace(aids,','))
)

2、更新操作

mysql

update b b1 set b1.aids=
(
　　-- 没加这层select 执行SQL语句时会报错。原因是在更新这个表和数据时又查询了它，而查询的数据又做了更新的条件。
    select rp from (
        select 
        (
            select 
            group_concat(a.id separator ',')
            from a
            where a.name=b2.name and a.age=b2.age
            group by a.name, a.age
        ) rp, b2.*
        from b b2    
    ) tmp
    where b1.id=tmp.id
)

 oracle

update b b1 set b1.aids=
(
    select 
    (
        select 
        (listagg(a.id,',') within group (order by a.id))
        from a
        where a.name=b2.name and a.age=b2.age
        group by a.name, a.age
    ) 
    from b b2    
    where b1.id=b2.id
)