데이터가 존재하거나 존재하지 않을 수 있는 경우 표 결합
먼저 제가 이 데이터베이스를 설계한 것이 아니라 사용하려고 노력한 것이라고 말씀드리겠습니다.
자전거 세트의 고장을 복구하려고 합니다. 자전거의 부품에 특정 속성이 있는지 여부가 가장 중요한 결정 요인입니다.속성은 요소 테이블에 설정됩니다.부품은 더 큰 어셈블리를 참조하는 어셈블리의 일부입니다.어셈블리에 특정 자전거 유형이 할당되어 있을 수 있습니다. 그렇지 않은 경우 모든 자전거 유형이 어셈블리에 할당되어 있다고 가정합니다.부품에는 일련 번호로 식별되는 특정 자전거가 할당될 수도 있습니다.
따라서 다음과 같이 가정할 수 있습니다.
- 고장 표의 레코드에는 항상 일련 번호, 상위 어셈블리 및 자전거 유형이 포함됩니다.
- 부품의 어셈블리는 항상 상위 어셈블리에 대한 참조를 가집니다.
- 부품 어셈블리에 자전거 유형에 대한 참조가 있을 수도 있고 없을 수도 있습니다.
- 부품에 특정 일련 번호에 대한 참조가 있을 수도 있고 없을 수도 있습니다.
특정 속성을 가진 부품이 있는 고장을 검색할 때 부품에 특정 자전거에 대한 참조가 있는 경우 고장만 찾으려고 합니다.그렇지 않고 부품의 어셈블리에 특정 자전거 유형에 대한 참조가 있는 경우 해당 유형에 대한 참조가 있고 해당 부품이 포함된 어셈블리와 관련된 고장만 찾으려고 합니다.그렇지 않으면 부품이 포함된 상위 어셈블리와 관련된 모든 고장을 찾으려고 합니다.
제 문제는 제가 일련번호에 가입하면 항상 일련번호가 할당된 부품만 받고, 자전거 종류에 가입하면 조립품에 유형이 할당된 부품만 받는다는 것입니다.데이터베이스 설계를 고려할 때 현실적이지 않은 것을 시도하는 것인지, 아니면 조인에 잘못 접근하는 것인지 잘 모르겠습니다.
지금까지의 문의 내용은 다음과 같습니다.
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
저는 이것이 할당되지 않은 조립품이 모든 자전거에 속하는 문제를 해결한다고 생각하지는 않지만, 샘플 데이터에서 어떻게 작동하는지는 명확하지 않습니다.
이 쿼리는 부품, 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
'programing' 카테고리의 다른 글
FOR UPDATE OF와 FOR UPDATE의 차이 (0) | 2023.08.26 |
---|---|
php - 날짜 형식으로 + 7일 추가 mm dd, YYYY (0) | 2023.08.26 |
문자열에서 텍스트를 제거하는 PowerShell (0) | 2023.08.26 |
ASP.NET: ModalPopupExtender는 버튼 클릭 이벤트가 발생하지 않도록 방지합니다. (0) | 2023.08.26 |
텍스트별 링크 선택(정확히 일치) (0) | 2023.08.26 |