데이터가 존재하거나 존재하지 않을 수 있는 경우 표 결합

데이터가 존재하거나 존재하지 않을 수 있는 경우 표 결합

먼저 제가 이 데이터베이스를 설계한 것이 아니라 사용하려고 노력한 것이라고 말씀드리겠습니다.

자전거 세트의 고장을 복구하려고 합니다. 자전거의 부품에 특정 속성이 있는지 여부가 가장 중요한 결정 요인입니다.속성은 요소 테이블에 설정됩니다.부품은 더 큰 어셈블리를 참조하는 어셈블리의 일부입니다.어셈블리에 특정 자전거 유형이 할당되어 있을 수 있습니다. 그렇지 않은 경우 모든 자전거 유형이 어셈블리에 할당되어 있다고 가정합니다.부품에는 일련 번호로 식별되는 특정 자전거가 할당될 수도 있습니다.

따라서 다음과 같이 가정할 수 있습니다.

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(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

됐어요 (포스트그리)SQL 버전):

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중복이 우려되는 경우.

다음 쿼리는 원하는 결과 집합을 반환합니다.기본적으로, 이는 부품, 조립품 및 자전거 유형 간의 관계를 먼저 구축한 다음, 실제 결과를 얻기 위해 고장에 대해 우선순위가 지정된 복잡한 결합을 수행합니다.

SELECT DISTINCT f.f_bicycle_type, f.f_serial_number, f.f_big_assembly
FROM  parts p
      LEFT JOIN parts_bicycles pb
         ON p.p_id = pb.b_part_id
      LEFT JOIN assemblies a
         ON p.p_assembly_id = a.a_assembly_id
      LEFT JOIN assembly_bicycle_types abt
         ON a.a_assembly_id = abt.bt_assembly_id
      LEFT JOIN failures f
         ON -- First priority is parts that map directly
            pb.b_serial_number = f.f_serial_number 
            -- Second priority is assemblies that map to type
            OR (pb.b_serial_number IS NULL 
                AND abt.bt_bicycle_type = f.f_bicycle_type) 
            -- Third priority is assemblies that map directly
            OR (pb.b_serial_number IS NULL 
                AND abt.bt_bicycle_type IS NULL 
                AND a.a_big_assembly = f.f_big_assembly)
WHERE  p.p_important_attr = 'awesome'
ORDER BY f.f_serial_number  

SQL 피들

저는 이것이 할당되지 않은 조립품이 모든 자전거에 속하는 문제를 해결한다고 생각하지는 않지만, 샘플 데이터에서 어떻게 작동하는지는 명확하지 않습니다.

이 쿼리는 부품, parts_bicycle, 어셈블리, 어셈블리_bicycle_type을 결합합니다.보기로 저장합니다.

create view j_parts as
select p_important_attr, b_serial_number, a_big_assembly, bt_bicycle_type
from
  parts left join parts_bicycles
    on parts.p_id = parts_bicycles.b_part_id
  left join assemblies
    on parts.p_assembly_id=assemblies.a_assembly_id
  left join assembly_bicycle_types
    on assemblies.a_assembly_id =assembly_bicycle_types.bt_assembly_id

그리고 이것(제 생각에는!)은 당신이 원하는 결과를 제공하는 쿼리입니다.

SELECT failures.*
FROM
  failures inner join j_parts
  on f_serial_number=b_serial_number
     and p_important_attr = 'awesome'
UNION
SELECT failures.*
FROM
  failures inner join j_parts
  on f_big_assembly=a_big_assembly
     and b_serial_number is null
     and j_parts.bt_bicycle_type=f_bicycle_type
     and p_important_attr = 'awesome'
UNION
SELECT failures.*
FROM
  failures inner join j_parts
  on f_big_assembly=a_big_assembly
     and b_serial_number is null
     and j_parts.bt_bicycle_type is null
     and p_important_attr = 'awesome'

편집: 읽기 쉽고 유지하기 쉽기 때문에 이렇게 쓰고 싶었습니다.그러면 쿼리를 최적화할 수 있습니다.다음은 단 한 번의 선택으로 모든 조건을 충족한 것입니다.

SELECT failures.*
FROM
  failures inner join
  (parts left join parts_bicycles
   on parts.p_id = parts_bicycles.b_part_id
   left join assemblies
   on parts.p_assembly_id=assemblies.a_assembly_id
   left join assembly_bicycle_types
   on assemblies.a_assembly_id =assembly_bicycle_types.bt_assembly_id)
  on f_serial_number=b_serial_number
     or (f_big_assembly=a_big_assembly
         and b_serial_number is null
         and (bt_bicycle_type=f_bicycle_type
              or bt_bicycle_type is null))
  and p_important_attr = 'awesome'

수정된 쿼리는 다음과 같습니다.

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 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 or p_join_a_and_b_join_bt.bt_bicycle_type is null)
-- problem join clause - there may not be explicit serial numbers assigned to a given part
and (f_serial_number = b_serial_number or b_serial_number is null)
) z
WHERE p_important_attr = 'awesome';

언급URL : https://stackoverflow.com/questions/13499797/joining-tables-when-data-may-or-may-not-exist