如何根据条件基于不同字段实现INNER JOIN关联

Hey there, let's work through this conditional join problem. Based on your table structures and requirements, here's a straightforward way to implement the matching logic you need:

Solution SQL #

SELECT p.*, d.*
FROM Person p
INNER JOIN Document d
  -- Match when doc_type is '01' (only type and series)
  ON (p.doc_type = '01' 
      AND p.doc_type = d.type 
      AND p.doc_series = d.series)
  -- Match when doc_type is '02' (all four fields)
  OR (p.doc_type = '02' 
      AND p.doc_type = d.type 
      AND p.doc_series = d.series 
      AND p.doc_number = d.number 
      AND p.doc_date = d.date);

How This Works #

• For records where Person.doc_type = '01', the join only checks that the document type and series match between both tables.
• For records where Person.doc_type = '02', the join enforces a full match on type, series, number, and date.
• Any Person records with a doc_type that isn't '01' or '02' will be excluded from the result set since they won't satisfy either join condition (this aligns with your INNER JOIN requirement).

Performance Tip #

To make this query run efficiently, consider adding composite indexes on the Document table tailored to each condition:

• For '01' matches: CREATE INDEX idx_doc_type_series ON Document(type, series);
• For '02' matches: CREATE INDEX idx_doc_full ON Document(type, series, number, date);

These indexes will help the database quickly locate matching records without scanning the entire table.

内容的提问来源于stack exchange，提问作者Kirill