Oracle SQL：基于相同根变量为两类不同ID生成独立行

需求说明 #

需将4个数据集合并为一个，各表通过person id和sample id关联。其中t3、t4两张表包含specificsampleid字段，要求：

• 为t4的每个specificsampleid值生成独立行，对应t3相关列置空
• 当t3列有值时，t4相关列需为空/Null

示例输入数据 #

理想输出 #

现有SQL代码 #

SELECT DISTINCT
demo.[CPDMID],
DEMO.[DFCIMRN],
DEMO.[SampleProcurementDTS],
DEMO.[RecordID],
DEMO.[PathologyAccessionNBR],
DEMO.[TumorTypeDSC],
DEMO.[PatientTreatmentHistoryDSC],
DEMO.[TissueSiteOfTumorSampleDSC],
DEMO.[OtherTissueSiteOfTumorSampleTXT],
DEMO.[TissueSiteOfPrimaryCancerDSC],
DEMO.[TopLineDiagnosisFromPathologistTXT],
DEMO.[SummarizedDiagnosisTXT],
--Cryo数据段---------
CRYO.[CPDMIDBloodAvailabilityTXT] CryoCpdmidBloodAvailabilityTXT,
CRYO.[CryopreservedCellsFLG] CryopreservedCellsIND,
--PDCL数据段-----------
pdcl.[PDCLID],
PDCL.[MediaTypeDSC] PdclMediaTypeDSC,
PDCL.[CultureSubstrateDSC] PdclCultureSubstrateDSC,
PDCL.[SixMonthUpdateFinalStatusCD] PdclSixMonthUpdateFinalStatCD, 
PDCL.[GrowthVerifiedFLG] PdclGrowthVerifiedIND,
--为UNION生成PDX虚拟列
NULL AS "PdxAttemptID",
NULL AS "PdxStrainOfMouseCD",
NULL AS "PdxTumorLocationCD",
NULL AS "PdxTumorGrowthIND",
NULL AS "PdxSixMonthFinalStatusCD",
DEMO.[LastLoadDTS]
FROM 
[SAM].[CPDM].PatientDemographicsPathology demo 
LEFT OUTER JOIN
([SAM].[CPDM].[LiveCryoPatientCells] cryo INNER JOIN [SAM].[CPDM].[PatientDerivedCellLines] pdcl on CRYO.[CPDMID] = PDCL.[CPDMID] AND CRYO.DFCIMRN = PDCL.DFCIMRN)
ON DEMO.CPDMID = CRYO.CPDMID AND DEMO.DFCIMRN = CRYO.DFCIMRN
UNION 
--此部分获取所有PDX数据，并为PDCL生成虚拟列以支持UNION操作
select DISTINCT
demo.[CPDMID],
DEMO.[DFCIMRN],
DEMO.[SampleProcurementDTS],
DEMO.[RecordID],
DEMO.[PathologyAccessionNBR],
dEMO.[TumorTypeDSC],
DEMO.[PatientTreatmentHistoryDSC],
DEMO.[TissueSiteOfTumorSampleDSC],
DEMO.[OtherTissueSiteOfTumorSampleTXT],
DEMO.[TissueSiteOfPrimaryCancerDSC],
DEMO.[TopLineDiagnosisFromPathologistTXT],
DEMO.[SummarizedDiagnosisTXT],
--Cryo数据段---------
CRYO.[CPDMIDBloodAvailabilityTXT] CryoCpdmidBloodAvailabilityTXT,
CRYO.[CryopreservedCellsFLG] CryopreservedCellsIND,
--PDCL数据段-----------
--PDCL虚拟列
NULL AS "PDCLID",
NULL AS "PdclMediaTypeDSC",
NULL AS "PdclCultureSubstrateDSC",
NULL AS "PdclSixMonthUpdateFinalStatCD",
NULL AS "PdclGrowthVerifiedIND",
--PDX数据段
pdx.[PDXAttemptID] PdxAttemptID,
pdx.[StrainofMouseCD] PdxStrainOfMouseCD ,
pdx.[TumorLocationCD] PdxTumorLocationCD,
pdx.[TumourGrowthFLG] PdxTumorGrowthIND,
pdx.[SixMonthUpdateFinalStatusCD] PdxSixMonthFinalStatusCD,
DEMO.[LastLoadDTS]
FROM 
[SAM].[CPDM].PatientDemographicsPathology demo 
LEFT OUTER JOIN
([SAM].[CPDM].[LiveCryoPatientCells] cryo INNER JOIN [SAM].[CPDM].[PatientDerivedXenografts] PDX on CRYO.[CPDMID] = PDX.[CPDMID] AND CRYO.DFCIMRN = PDX.DFCIMRN)
ON DEMO.CPDMID = CRYO.CPDMID AND DEMO.DFCIMRN = CRYO.DFCIMRN

修改后的SQL解决方案 #

核心思路是用UNION ALL拆分两组数据：一组保留t3的specificsampleid并置空t4相关列，另一组保留t4的specificsampleid并置空t3相关列，同时关联其他表的共同字段。

-- 第一部分：保留PDCL（t3）的specificsampleid，PDX相关列置空
SELECT DISTINCT
    demo.[CPDMID],
    DEMO.[DFCIMRN],
    DEMO.[SampleProcurementDTS],
    DEMO.[RecordID],
    DEMO.[PathologyAccessionNBR],
    DEMO.[TumorTypeDSC],
    DEMO.[PatientTreatmentHistoryDSC],
    DEMO.[TissueSiteOfTumorSampleDSC],
    DEMO.[OtherTissueSiteOfTumorSampleTXT],
    DEMO.[TissueSiteOfPrimaryCancerDSC],
    DEMO.[TopLineDiagnosisFromPathologistTXT],
    DEMO.[SummarizedDiagnosisTXT],
    -- Cryo数据段
    CRYO.[CPDMIDBloodAvailabilityTXT] AS CryoCpdmidBloodAvailabilityTXT,
    CRYO.[CryopreservedCellsFLG] AS CryopreservedCellsIND,
    -- PDCL数据段
    pdcl.[PDCLID],
    PDCL.[MediaTypeDSC] AS PdclMediaTypeDSC,
    PDCL.[CultureSubstrateDSC] AS PdclCultureSubstrateDSC,
    PDCL.[SixMonthUpdateFinalStatusCD] AS PdclSixMonthUpdateFinalStatCD, 
    PDCL.[GrowthVerifiedFLG] AS PdclGrowthVerifiedIND,
    -- PDX相关列置空
    NULL AS PdxAttemptID,
    NULL AS PdxStrainOfMouseCD,
    NULL AS PdxTumorLocationCD,
    NULL AS PdxTumorGrowthIND,
    NULL AS PdxSixMonthFinalStatusCD,
    DEMO.[LastLoadDTS]
FROM 
    [SAM].[CPDM].PatientDemographicsPathology demo 
LEFT JOIN [SAM].[CPDM].[LiveCryoPatientCells] cryo 
    ON DEMO.CPDMID = CRYO.CPDMID AND DEMO.DFCIMRN = CRYO.DFCIMRN
LEFT JOIN [SAM].[CPDM].[PatientDerivedCellLines] pdcl 
    ON CRYO.[CPDMID] = PDCL.[CPDMID] AND CRYO.DFCIMRN = PDCL.DFCIMRN
-- 可选：过滤PDCL无有效数据的行
WHERE pdcl.specificsampleid IS NOT NULL

UNION ALL

-- 第二部分：保留PDX（t4）的specificsampleid，PDCL相关列置空
SELECT DISTINCT
    demo.[CPDMID],
    DEMO.[DFCIMRN],
    DEMO.[SampleProcurementDTS],
    DEMO.[RecordID],
    DEMO.[PathologyAccessionNBR],
    dEMO.[TumorTypeDSC],
    DEMO.[PatientTreatmentHistoryDSC],
    DEMO.[TissueSiteOfTumorSampleDSC],
    DEMO.[OtherTissueSiteOfTumorSampleTXT],
    DEMO.[TissueSiteOfPrimaryCancerDSC],
    DEMO.[TopLineDiagnosisFromPathologistTXT],
    DEMO.[SummarizedDiagnosisTXT],
    -- Cryo数据段
    CRYO.[CPDMIDBloodAvailabilityTXT] AS CryoCpdmidBloodAvailabilityTXT,
    CRYO.[CryopreservedCellsFLG] AS CryopreservedCellsIND,
    -- PDCL相关列置空
    NULL AS PDCLID,
    NULL AS PdclMediaTypeDSC,
    NULL AS PdclCultureSubstrateDSC,
    NULL AS PdclSixMonthUpdateFinalStatCD,
    NULL AS PdclGrowthVerifiedIND,
    -- PDX数据段
    pdx.[PDXAttemptID] AS PdxAttemptID,
    pdx.[StrainofMouseCD] AS PdxStrainOfMouseCD,
    pdx.[TumorLocationCD] AS PdxTumorLocationCD,
    pdx.[TumourGrowthFLG] AS PdxTumorGrowthIND,
    pdx.[SixMonthUpdateFinalStatusCD] AS PdxSixMonthFinalStatusCD,
    DEMO.[LastLoadDTS]
FROM 
    [SAM].[CPDM].PatientDemographicsPathology demo 
LEFT JOIN [SAM].[CPDM].[LiveCryoPatientCells] cryo 
    ON DEMO.CPDMID = CRYO.CPDMID AND DEMO.DFCIMRN = CRYO.DFCIMRN
LEFT JOIN [SAM].[CPDM].[PatientDerivedXenografts] PDX 
    ON CRYO.[CPDMID] = PDX.[CPDMID] AND CRYO.DFCIMRN = PDX.DFCIMRN
-- 可选：过滤PDX无有效数据的行
WHERE pdx.specificsampleid IS NOT NULL

关键说明 #

1. 用UNION ALL替代UNION，避免自动去重带来的性能损耗，需去重可换回UNION
2. 拆分两个查询分支，分别处理PDCL和PDX数据，对应对方的列置空
3. 将嵌套JOIN拆分为连续LEFT JOIN，提升代码可读性
4. 添加WHERE子句过滤空值（可选，根据实际数据情况调整）

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