mysql - SQL连接三个表，一个表返回未知数量的结果，不想要重复的行

Question

好的，我正在使用 Node 和 MySQL，但遇到查询问题。

我有三个表，我们将它们称为 T1、T2 和 T3。 T1 有一个主键，我们将其称为 T1.id。 T3 还有一个主键 T3.id。

T2 有一个主键 T2.id 和指向 T1.id (FK1) 和 T3.id (FK3) 的外键。现在让我们看一个示例数据集:

T1_____     T2________              T3_____
id -name    | id -FK1 -FK3          | id  -name
1  johnny   | 1  .... 1 ....  1  .. |  1  ..  MN
2  william  | 2 ....  1 ....  2  .. |  2  ..  FL
3  joseph   | 3  .... 1 ....  3  .. |  3  ..  CA
4  bobbie   | 4 ....  2 ....  2  
------------| 5 ....  2  .... 3
------------| 6 ....  3 ....  1
------------| 7 ....  3  .... 2
------------| 8  .... 3  .... 3

我想要一个查询，该查询将返回 T1 中的列，然后为每个匹配连接 T2 的(未知数量)列，然后对于每个 T2 匹配，连接 T3 中相应匹配的列。

例如，第一行是:

T1.name  T2.1.id T2.1.T3.name T2.2.id T2.2.T3.name T2.3.id T2.3.T3.name
johnny   1       MN           2       FL           3       CA

*** 抱歉，格式不正确。这是可行的，还是我需要使用非关系数据库，如 Mongo 或 Couchbase？

Answer 1

简化的解决方案:将T3表中的所有数据合并到结果集中的一列中。

SELECT      t1.name,
            GROUP_CONCAT(t3.id,' ',t3.name SEPARATOR ', ') as data
    FROM    t1
    JOIN    t2
        ON  t2.fk1 = t1.id
    JOIN    t3
        ON  t3.id = t2.fk2
GROUP BY    t1.id;

它会产生结果

T1.name         | data
johnny          | 1 MN, 2 FL, 3 CA

带有可配置的分隔符。

扩展解决方案:利用数据透视准备动态查询:

SELECT  GROUP_CONCAT('MAX(CASE WHEN ind=', @i := @i+1, ' THEN x.t3id ELSE NULL END) AS col', @i, 'id,MAX(CASE WHEN ind=', @i, ' THEN x.name ELSE NULL END) AS col', @i, 'name') 
FROM    t2,
        (SELECT @i := 0) init
WHERE   t2.fk1 = (
    SELECT      fk1
        FROM    (
            SELECT      t2.fk1, 
                        COUNT(t2.fk2) as cnt
                FROM    t2
            GROUP BY    t2.fk1
            ORDER BY    cnt DESC
                LIMIT   1
            ) tmp
        ) 
INTO @sql;

SET @sql = CONCAT('SELECT x.t1name,', @sql, ' FROM ( SELECT @i := IF(@id = tmp.id, @i+1, 1) as ind, @id := tmp.id as id, tmp.t1name, tmp.t3id, tmp.name FROM (SELECT t1.id, t1.name as t1name, t3.id as t3id, t3.name FROM t1 JOIN t2 ON t2.fk1 = t1.id JOIN t3 ON t3.id = t2.fk2,(SELECT @i := 1, @id := 0) init ORDER BY t1.id) tmp) x GROUP BY x.id');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

此方法将创建并执行如下所示的查询:

SELECT      x.t1name,
            MAX(CASE WHEN ind = 1 THEN x.t3id ELSE NULL END) AS col1id,
            MAX(CASE WHEN ind = 1 THEN x.name ELSE NULL END) AS col1name,
            MAX(CASE WHEN ind = 2 THEN x.t3id ELSE NULL END) AS col2id,
            MAX(CASE WHEN ind = 2 THEN x.name ELSE NULL END) AS col2name,
            MAX(CASE WHEN ind = 3 THEN x.t3id ELSE NULL END) AS col3id,
            MAX(CASE WHEN ind = 3 THEN x.name ELSE NULL END) AS col3name
    FROM    (
        SELECT      @i := IF(@id = tmp.id, @i+1, 1) as ind,
                    @id := tmp.id as id,
                    tmp.t1name,
                    tmp.t3id,
                    tmp.name
            FROM    (
                SELECT      t1.id,
                            t1.name as t1name,
                            t3.id as t3id,
                            t3.name
                    FROM    t1
                    JOIN    t2
                        ON  t2.fk1 = t1.id
                    JOIN    t3
                        ON  t3.id = t2.fk2,
                            (SELECT @i := 1, @id := 0) init
                ORDER BY    t1.id
            ) tmp
        ) x
GROUP BY    x.id;

这给出了以下结果:

 t1name  | col1id | col1name | col2id | col2name | col3id | col3name 
 johnny  |      1 | MN       |      2 | FL       |      3 | CA       
 william |      2 | FL       |      3 | CA       |   NULL | NULL     

但是，关于group_concat，这里有一个限制。功能

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024.