首先让我说我没有设计这个数据库；只是尝试使用它。

我正在尝试检索一组自行车的故障，其中最重要的决定因素是自行车中的任何零件是否具有特定属性。该属性在零件表中设置。该零件是装配体的一部分，它引用了更大的装配体。该组件可能分配有特定的自行车类型；如果没有，我们假设所有自行车类型都分配给了装配体。这些零件也可能分配有特定的自行车，并通过序列号识别。

因此，我们可以假设:

1. 故障表中的记录将始终包含序列号、更高的组件和自行车类型。
2. 一个部件的装配将始终引用更高的装配
3. 零件的装配可能会或可能不会引用自行车类型。
4. 零件可能会或可能不会引用特定的序列号

在搜索具有特定属性的零件的故障时，如果该零件引用了特定的自行车，我们只想找到那些。如果没有，并且零件的装配有对特定自行车类型的引用，我们只想查找与引用了这些类型并包含这些零件的装配相关的故障。否则，我们希望找到与包含该部件的更高组件相关的所有故障。

我的问题是，如果我加入序列号，我总是只能得到分配了序列号的零件，如果我加入自行车类型，我只能得到其装配有分配类型的零件。我不确定我是否正在尝试一些不符合数据库设计的现实，或者我是否正在错误地处理连接。

以下是到目前为止的查询。

SELECT f_bicycle_type, f_serial_number, f_big_assembly
FROM ( 
    SELECT DISTINCT f.f_bicycle_type, f.f_serial_number, f.f_big_assembly, p_important_attr 
    from failures f 
    left outer join (    
        select distinct bt.bt_bicycle_type, b_serial_number, a_big_assembly, p_important_attr  
        from (          
            select distinct b.b_serial_number, a.a_big_assembly, p.p_assembly_id, p.p_important_attr
            from parts p
            join assemblies a on p.p_assembly_id = a.a_assembly_id
            left outer join parts_bicycles b on b.b_part_id = p.p_id  
            where p.p_important_attr = 'awesome'
        ) p_join_a_and_b 
        left outer join assembly_bicycle_types bt on bt.bt_assembly_id = p_join_a_and_b.p_assembly_id 
    ) p_join_a_and_b_join_bt 
    on f.f_big_assembly = p_join_a_and_b_join_bt.a_big_assembly 
    -- problem join clause - if an explicit type has not been assigned to the assembly, we want to include ALL types
    and f_bicycle_type = p_join_a_and_b_join_bt.bt_bicycle_type
    -- problem join clause - there may not be explicit serial numbers assigned to a given part
    and f_serial_number = b_serial_number
) z
WHERE p_important_attr = 'awesome';

测试用例 sql (for Oracle):

CREATE TABLE failures (
f_bicycle_type VARCHAR(20),
f_serial_number NUMBER(20),
f_big_assembly VARCHAR(5)); 

CREATE TABLE parts (
p_id NUMBER(20),
p_assembly_id NUMBER(20),
p_important_attr VARCHAR(20));

CREATE TABLE assemblies (
a_assembly_id NUMBER(20),
a_big_assembly VARCHAR(5)); 

CREATE TABLE parts_bicycles (
b_part_id NUMBER(20),
b_serial_number NUMBER(20));    

CREATE TABLE assembly_bicycle_types (
bt_assembly_id NUMBER(20),
bt_bicycle_type VARCHAR(20));

INSERT ALL
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000001, 'A1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000002, 'A1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000003, 'B1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000004, 'B1000')  
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000005, 'C1000')  
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('motocross', 1000006, 'C1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000007, 'C1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000008, 'D1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000009, 'D1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000010, 'E1000')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (1, 1001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (2, 1001, 'ordinary')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (3, 2001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (4, 3001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (5, 4001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (6, 5001, 'ordinary')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (1001, 'A1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (2001, 'B1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (3001, 'C1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (4001, 'D1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (5001, 'E1000')
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000005)
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000006)
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (5, 1000008)
INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (02001, 'tandem')
INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (04001, 'bmx')
SELECT * FROM DUAL;

对于 MySQL:

 CREATE TABLE failures (
f_bicycle_type VARCHAR(20),
f_serial_number INTEGER(20),
f_big_assembly VARCHAR(5));
CREATE TABLE parts(
p_id INTEGER( 20 ) ,
p_assembly_id INTEGER( 20 ) ,
p_important_attr VARCHAR( 20 )
);
CREATE TABLE assemblies(
a_assembly_id INTEGER( 20 ) ,
a_big_assembly VARCHAR( 5 )
);
CREATE TABLE parts_bicycles(
b_part_id INTEGER( 20 ) ,
b_serial_number INTEGER( 20 )
);
CREATE TABLE assembly_bicycle_types(
bt_assembly_id INTEGER( 20 ) ,
bt_bicycle_type VARCHAR( 20 )
);

INSERT INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000001, 'A1000'),('bmx', 1000002, 'A1000'), ('tandem', 1000003, 'B1000'),    ('cruiser', 1000004, 'B1000') ,('bmx', 1000005, 'C1000'), ('motocross', 1000006, 'C1000')
,('cruiser', 1000007, 'C1000')
,('bmx', 1000008, 'D1000')
,('bmx', 1000009, 'D1000')
, ('cruiser', 1000010, 'E1000');
insert INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (1, 1001, 'awesome'), (2, 1001, 'ordinary'), (3, 2001, 'awesome'), (4, 3001, 'awesome'), (5, 4001, 'awesome'),(6, 5001, 'ordinary');
INSERT INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (1001, 'A1000'), (2001, 'B1000'), (3001, 'C1000'), (4001, 'D1000'),(5001, 'E1000');
    INSERT INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000005),(4, 1000006),(5, 1000008)
INSERT INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (02001, 'tandem'), (04001, 'bmx');

示例数据和预期结果:

-- failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  tandem               1000001             A1000
  bmx                  1000002             A1000
  tandem               1000003             B1000
  cruiser              1000004             B1000
  bmx                  1000005             C1000
  motocross            1000006             C1000
  cruiser              1000007             C1000
  bmx                  1000008             D1000
  bmx                  1000009             D1000
  cruiser              1000010             E1000

  -- parts table
  -- p_id   || p_assembly_id    || p_important_attr
  ------------------------------------------------
     1          1001                awesome
     2          1001                ordinary
     3          2001                awesome
     4          3001                awesome
     5          4001                awesome
     6          5001                ordinary

  -- assemblies table
  -- a_assembly_id  || a_big_assembly
  -----------------------------------
     1001              A1000
     2001              B1000
     3001              C1000
     4001              D1000
     5001              E1000

  -- parts_bicycles table
  -- b_part_id  || b_serial_number
  --------------------------------
     4              1000005
     4              1000006
     5              1000008

  -- assembly_bicycle_types table
  -- bt_assembly_id || bt_bicycle_type
  ------------------------------------
     02001             tandem
     04001             bmx

-- desired results from failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  tandem               1000001             A1000
  bmx                  1000002             A1000
  tandem               1000003             B1000
  bmx                  1000005             C1000
      motocross            1000006             C1000
  bmx                  1000008             D1000

与实际结果、问题结合到位:

-- actual results from failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  bmx                  1000008             D1000

最佳答案

好了(PostgreSQL 风格):

WITH chosen_parts AS (
  SELECT * FROM parts LEFT JOIN parts_bicycles ON b_part_id = p_id 
    WHERE p_important_attr = 'awesome'
), chosen_assemblies AS (
  SELECT * FROM assemblies JOIN chosen_parts ON p_assembly_id = a_assembly_id 
    LEFT JOIN assembly_bicycle_types ON bt_assembly_id = a_assembly_id 
  WHERE b_serial_number IS NULL
)
SELECT failures.* FROM chosen_parts JOIN failures 
  ON f_serial_number = b_serial_number
UNION
SELECT failures.* FROM chosen_assemblies JOIN failures 
  ON f_big_assembly = a_big_assembly 
  WHERE bt_bicycle_type = f_bicycle_type
    OR bt_bicycle_type IS NULL;

如果担心重复，请随意添加外部 SELECT DISTINCT * FROM。

关于mysql - 在数据可能存在或可能不存在时连接表，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/13499797/