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

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

-- 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

-- 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

-- 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,‘,‘))
)

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
)

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
)