代码之家 › 专栏 › 技术社区 › miile7

如果活动且存在,则左联接

mysql

miile7 · 技术社区 · 7 年前

active 国家是一个:

        Table 1
+------+-------+--------+
|  id  | value | active |
+------+-------+--------+
|  1   |   v1  |    1   |
|  2   |   v2  |    1   |
|  3   |   v3  |    1   |
+------+-------+--------+

             Table 2                              Table 3             
+------+-------+-------+--------+    +------+-------+-------+--------+
|  id  | link  | value | active |    |  id  | link  | value | active |
+------+-------+-------+--------+    +------+-------+-------+--------+
|  1   |   1   |  v21  |    1   |    |  1   |   1   |  v31  |    1   |
|  2   |   2   |  v22  |    0   |    |  2   |   2   |  v32  |    1   |
|  3   |   3   |  v23  |    1   |    +------+-------+-------+--------+
+------+-------+-------+--------+

Table 1 Table 2 Table 3 active=1

             Result 
+------+-------+-------+--------+
|  id  | value | tbl 2 | tbl 3  |
+------+-------+-------+--------+
|  1   |   v1  |  v21  |   v31  |  -- values exist in every table
|  2   |   v2  |  NULL |   v32  |  -- active is 0 in Table 2 -> value tbl 2 = NULL
|  3   |   v3  |  v23  |   NULL |  -- value does not exist in Table 3 -> value tbl 3 = NULL
+------+-------+-------+--------+

SELECT SQL_CALC_FOUND_ROWS 
    `Table 1`.*, 
    IF(`Table 2`.`active` = 1, `Table 2`.`value`, NULL) AS tbl2, 
    IF(`Table 3`.`active` = 1, `Table 3`.`value`, NULL) AS tbl3
FROM 
    `Table 1` 
LEFT JOIN 
    `Table 2` ON `Table 1`.`id` = `Table 2`.`link`
LEFT JOIN 
    `Table 3` ON `Table 1`.`id` = `Table 3`.`link`
WHERE 
    `Table 1`.`active` = 1 
LIMIT <limit> OFFSET <offset>

SQL_CALC_FOUND_ROWS 表1 另外还有10张桌子。它们也有2000多个记录。

link 列和 Table 1.id

1 回复 | 直到 7 年前

Robert Kock 7 年前

SELECT          table1.id,
                table1.value AS tab1_value,
                table2.value AS tab2_value,
                table3.value AS tab3_value
FROM            table1
LEFT OUTER JOIN table2
             ON table1.id = table2.link
            AND table2.value IS NOT NULL -- not strictly necessary
            AND table2.active = 1
LEFT OUTER JOIN table3
             ON table1.id = table3.link
            AND table3.value IS NOT NULL -- not strictly necessary
            AND table3.active = 1
WHERE           table1.active = 1;