编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE A (
	a_id int,
    name varchar(255),
	type int
);

CREATE TABLE B (
	b_id int,
    name varchar(255),
	a_id int
);

INSERT INTO A VALUES (1, '张三',1);
INSERT INTO A VALUES (2, '张三',2);
INSERT INTO A VALUES (3, '张三',1);


INSERT INTO B VALUES (1,'张三',1);
INSERT INTO B VALUES (2,'张三',3);
INSERT INTO B VALUES (3,'张三',3);

-- 第一种
SELECT 
    A.a_id,
    A.name,
    A.type,
    B.b_id,
    B.a_id as b_a_id
FROM A
LEFT JOIN B ON (B.a_id = A.a_id and A.type=1)
 or (B.name = A.name and A.type=2)
ORDER BY A.a_id, B.b_id;

-- 第二种
SELECT 
    A.a_id,
    A.name,
    A.type,
    B.b_id,
    B.a_id as b_a_id
FROM A
LEFT JOIN B ON B.a_id = CASE
    WHEN A.type = 1 THEN A.a_id
    WHEN A.type = 2 THEN (
        SELECT a_id 
        FROM A AS A_ref 
        WHERE A_ref.name = A.name 
          AND A_ref.type = 1 
        LIMIT 1
    )
END
ORDER BY A.a_id, B.b_id;

-- 第三种
SELECT 
    A_main.a_id,
    A_main.name,
    A_main.type,
    B.b_id,
    B.a_id as b_a_id
FROM A AS A_main
LEFT JOIN (
    -- 为type=2的记录找到对应的type=1记录的a_id
    SELECT 
        A2.a_id AS original_a_id,
        A1.a_id AS linked_a_id
    FROM A AS A2
    INNER JOIN A AS A1 
        ON A2.name = A1.name 
        AND A1.type = 1
    WHERE A2.type = 2
) AS link_table ON A_main.type = 2 AND A_main.a_id = link_table.original_a_id
LEFT JOIN B ON 
    (A_main.type = 1 AND B.a_id = A_main.a_id) OR
    (A_main.type = 2 AND B.a_id = link_table.linked_a_id)
ORDER BY A_main.a_id, B.b_id;