mysql - 如何将选择查询与 'negated ranges' 结合起来？

Question

我的问题可以分解为以下几点:

我有四张 table

CREATE TABLE table_1 (
  table_1_id int,
  table_1_name varchar(45),
  table_1_team varchar(45)
);

CREATE TABLE table_2 (
  table_2_id int,
  table_1_id int,
  table_2_name varchar(45)
);

CREATE TABLE table_3 (
  table_4_id int,
  table_1_id int
);

CREATE TABLE table_4 (
  table_4_id int,
  table_4_name varchar(45)
);

INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (1, 't1-name-1-t4', 'team1');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (2, 't1-name-2-t1', 'team1');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (3, 't1-name-3-t2', 'team1');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (4, 't1-name-4', 'team dont-regard-me-1');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (5, 't1-name-5-t1', 'team2');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (6, 't1-name-6-t2', 'team2');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (7, 't1-name-7', 'team dont-regard-me-2');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (8, 't1-name-8-t4', 'team3');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (9, 't1-name-9-t1', 'team3');
INSERT INTO table_1 (table_1_id, table_1_name, table_1_team) VALUES (10, 't1-name-10-t2', 'team3');

INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (1, 3, 't2-name-1');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (2, 3, 't2-name-2');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (3, 6, 't2-name-3');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (4, 6, 't2-name-4');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (5, 6, 't2-name-5');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (6, 10, 't2-name-6');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (7, 10, 't2-name-7');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (8, 66,'t2-name-8');
INSERT INTO table_2 (table_2_id, table_1_id, table_2_name) VALUES (9, 77, 't2-name-9');

INSERT INTO table_3 (table_4_id, table_1_id) VALUES (1, 1);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (2, 1);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (3, 1);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (6, 1);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (7, 8);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (8, 8);
INSERT INTO table_3 (table_4_id, table_1_id) VALUES (9, 8);

INSERT INTO table_4 (table_4_id, table_4_name) VALUES (1, 't4-name-1');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (2, 't4-name-2');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (3, 't4-name-3');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (4, 't4-name-4');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (5, 't4-name-5');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (6, 't4-name-6');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (7, 't4-name-7');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (8, 't4-name-8');
INSERT INTO table_4 (table_4_id, table_4_name) VALUES (9, 't4-name-9');

和我想组合的三个选择查询

SELECT
  t1.table_1_id AS t1_id,
  t1.table_1_name AS t1_name
FROM
  table_1 t1
WHERE
  t1.table_1_team IN ('team1', 'team2', 'team3') AND
  t1.table_1_id NOT IN (1,3,6,8,10);

SELECT
  t1.table_1_id AS t1_id,
  t4.table_4_id AS t4_id,
  t4.table_4_name AS t4_name  
FROM
   table_4 t4,
   table_1 t1,
   table_3 t3
WHERE
   t1.table_1_id IN (1,3,6,8,10) AND
   t3.table_1_id = t1.table_1_id AND
   t3.table_4_id = t4.table_4_id;

SELECT
  t1.table_1_id,
  t2.table_2_id,
  t2.table_2_name
FROM
  table_2 t2,
  table_1 t1
WHERE
  t1.table_1_id IN (1,3,6,8,10) AND
  t1.table_1_id = t2.table_1_id;

我想要的输出如下:

| t1_id | t1_name      | t1_team | t2_id  | t2_name   | t4_id  | t4_name   |
+-------+--------------+---------+--------+-----------+--------+-----------+
| 1     | t1-name-1-t4 | team1   | (null) | (null)    | 6      | t4-name-6 |
| 1     | t1-name-1-t4 | team1   | (null) | (null)    | 1      | t4-name-1 |
| 1     | t1-name-1-t4 | team1   | (null) | (null)    | 3      | t4-name-3 |
| 1     | t1-name-1-t4 | team1   | (null) | (null)    | 2      | t4-name-2 |
| 2     | t1-name-2-t1 | team1   | (null) | (null)    | (null) | (null)    |
| 3     | t1-name-3-t2 | team1   | 1      | t2-name-1 | (null) | (null)    |
| 3     | t1-name-3-t2 | team1   | 2      | t2-name-2 | (null) | (null)    |
| 5     | t1-name-5-t1 | team2   | (null) | (null)    | (null) | (null)    |
| 6     | t1-name-6-t2 | team2   | 4      | t2-name-4 | (null) | (null)    |
...

这是我到目前为止得到的:

SELECT
  t1.table_1_id AS t1_id,
  t1.table_1_name AS t1_name,
  linkTable1.table_2_id AS t2_id,
  linkTable1.table_2_name AS t2_name,
  linkTable2.table_4_id AS t4_id,
  linkTable2.table_4_name AS t4_name  
FROM
  table_1 t1
LEFT JOIN (
  SELECT
    table_2.table_2_id,
    table_2.table_2_name,
    table_2.table_1_id
  FROM
    table_2) linkTable1
ON
  t1.table_1_id = linkTable1.table_1_id
LEFT JOIN (
  SELECT
    table_3.table_1_id,
    table_4.table_4_id,
    table_4.table_4_name
  FROM
    table_4
  INNER JOIN 
     table_3
  ON
     table_4.table_4_id = table_3.table_4_id) linkTable2
ON
  t1.table_1_id = linkTable2.table_1_id;

但这种方法会导致不需要的行:

...
| 4 | t1-name-4 |team dont-regard-me-1 | (null) | (null) | (null) | (null) |
...

所以我必须找到一种方法来正确地集成 where 子句，即一方面

t1.table_1_team IN ('team1', 'team2', 'team3') AND
t1.table_1_id NOT IN (1,3,6,8,10);

另一边

t1.table_1_id IN (1,3,6,8,10)

有人可以帮助我吗？

Answer 1

我认为这符合您的要求。 至少它稍微简化了您的查询。

---

SELECT
  t1.table_1_id AS t1_id , t1.table_1_name AS t1_name , t1.table_1_team AS t1team
  , t2.table_2_id AS t2_id , t2.table_2_name AS t2_name
  , t4.table_4_id AS t4_id , t4.table_4_name AS t4_name
FROM table_1 t1
LEFT JOIN table_2 t2 ON t2.table_1_id = t1.table_1_id
LEFT JOIN table_4 t4 ON EXISTS ( -- keep the junction table out of the main query
        SELECT 1
        FROM table_3 t3
        WHERE t3.table_1_id = t1.table_1_id
        AND t3.table_4_id = t4.table_4_id
        )
WHERE 1=1
   AND t1.table_1_team IN ('team1', 'team2', 'team3')
   -- AND t1.table_1_id IN (1,3,6,8,10)
   -- AT LEAST ONE of the two left joins must have matching rows.
   -- OR t2.table_2_id IS NOT NULL OR t4.table_4_id IS NOT NULL
ORDER BY 1,4,6
        ;