java - 查询多对多关系数据库表

Question

我有数据列表和如下表。
数据:{ABC111,ABC112,ABC113,111111,111112,111113}
表:

Column 1 Column 2
ABC111   ABC115
ABC115   111333
111111   ABC112
111111   111112
ABC123   111113
111113   ABC113

我的结果应该如下所示
结果:{ABC115,111333,111113,ABC113,ABC123,ABC112,111112}

解释:
数据 - ABC111 与 ABC115 相关联，而 ABC115 也与 111333 相关联。因此结果 为 ABC115,111333。同样，ABC113 与 111113 相关联，而 111113 也与 ABC123 相关联。 结果 = 111113,ABC123。

是否可以使用查询来实现上述内容。寻找准备好的语句，我将向其传递上面提到的数据。

Answer 1

试试这个:-

/*CREATING RECORDS FOR THE MAIN TABLE*/

CREATE TABLE TABLES(Column1 text, Column2 text);

INSERT INTO TABLES VALUES('ABC111','ABC115');
INSERT INTO TABLES VALUES('ABC115','111333');
INSERT INTO TABLES VALUES('111111','ABC112');
INSERT INTO TABLES VALUES('111111','111112');
INSERT INTO TABLES VALUES('ABC123','111113');
INSERT INTO TABLES VALUES('111113','ABC113');
COMMIT;


/*CREATE LIST TO BE SUPPLIED*/

CREATE TABLE LIST(Column1 text);

INSERT INTO LIST VALUES('ABC111');
INSERT INTO LIST VALUES('ABC112');
INSERT INTO LIST VALUES('ABC113');
INSERT INTO LIST VALUES('111111');
INSERT INTO LIST VALUES('111112');
INSERT INTO LIST VALUES('111113');
COMMIT;


/*CODE TO GET THE RESULT*/

SELECT CONCAT('{',GROUP_CONCAT(RESULT),'}') AS RESULT FROM 
(
SELECT DISTINCT COLUMN1 AS RESULT FROM
  (
            Select a.column2 as column1,b.column2
            from
            (
            Select * from
            tables 
            where column1 in (SELECT COLUMN1 FROM LIST)
            ) a
            inner join
            tables b
            on a.column2=b.column1

            UNION ALL

            Select b.column2 as column1,b.column1 as Column2
            from
            (
            Select * from
            tables a
            where column2 in (SELECT COLUMN1 FROM LIST)
            ) a
            inner join
            tables b
            on a.column1=b.column2
) a

UNION ALL

SELECT DISTINCT COLUMN2 AS RESULT FROM 
(
            Select a.column2 as column1,b.column2
            from
            (
            Select * from
            tables 
            where column1 in (SELECT COLUMN1 FROM LIST)
            ) a
            inner join
            tables b
            on a.column2=b.column1

            UNION ALL

            Select b.column2 as column1,b.column1 as Column2
            from
            (
            Select * from
            tables a
            where column2 in (SELECT COLUMN1 FROM LIST)
            ) a
            inner join
            tables b
            on a.column1=b.column2
) a

UNION ALL

SELECT DISTINCT COLUMN2 AS RESULT FROM
(

        Select distinct a.Column1, a.column2  
        from
        (
        Select * from
        tables a
        where column2 in (SELECT COLUMN1 FROM LIST)
        ) a
        inner join
        (
        Select * from
        tables a
        where column2 in (SELECT COLUMN1 FROM LIST)
        ) b
        on a.column1=b.column1
        where a.column2 not in 
    (
    Select column1 from 
        (
            Select b.column2 as column1,b.column1 as Column2
            from
            (
            Select * from
            tables a
            where column2 in (SELECT COLUMN1 FROM LIST)
            ) a
            inner join
            tables b
            on a.column1=b.column2
         ) a
    )       
  ) a
) a;

希望这有帮助:-)