多表关联下按条件动态返回字段的SQL查询实现问询

Absolutely—this requirement is fully achievable with standard SQL. We can break down each conditional logic branch into separate query blocks and combine them using UNION ALL, ensuring all result sets share a consistent column structure by using NULL for columns that aren't relevant to a given scenario.

-- Scenario 1: Type 'A' with matching $A and $D parameters
SELECT 
    a.ValA2,
    a.ValA3,
    NULL AS ValB2,
    NULL AS ValB3,
    d.ValD
FROM TableC c
INNER JOIN TableA a ON c.A_Id = a.A_Id
INNER JOIN TableD d ON c.D_Id = d.D_Id
WHERE c.Type = 'A'
  AND a.ValA1 = $A
  AND d.ValD = $D

UNION ALL

-- Scenario 2: Type 'B' with matching $B and $D parameters
SELECT 
    NULL AS ValA2,
    NULL AS ValA3,
    b.ValB2,
    b.ValB3,
    d.ValD
FROM TableC c
INNER JOIN TableB b ON c.B_Id = b.B_Id
INNER JOIN TableD d ON c.D_Id = d.D_Id
WHERE c.Type = 'B'
  AND b.ValB1 = $B
  AND d.ValD = $D

UNION ALL

-- Scenario 3: Type 'C' or 'D' with matching $D parameter
SELECT 
    a.ValA2,
    a.ValA3,
    b.ValB2,
    b.ValB3,
    d.ValD
FROM TableC c
INNER JOIN TableA a ON c.A_Id = a.A_Id
INNER JOIN TableB b ON c.B_Id = b.B_Id
INNER JOIN TableD d ON c.D_Id = d.D_Id
WHERE c.Type IN ('C', 'D')
  AND d.ValD = $D;

• Consistent Column Structure: Each query block returns 5 columns. For scenarios 1 and 2, we use NULL for columns that don't apply (e.g., B-related columns in scenario 1) to match the output of scenario 3.
• Inner Joins: We use INNER JOIN to ensure only records with matching entries across linked tables are included. If you need to retain TableC records even when there's no matching A/B/D record, replace INNER JOIN with LEFT JOIN and adjust the WHERE clauses accordingly.
• Parameter Safety: The $A, $B, and $D placeholders should be used with parameterized queries (avoid string concatenation) to prevent SQL injection vulnerabilities.
• Efficiency: UNION ALL is used instead of UNION because there's no overlap between the scenarios (each filters for distinct Type values), so it avoids unnecessary duplicate-checking overhead.

内容的提问来源于stack exchange，提问作者A.bakker