T-SQL中基于布尔列Inner Join构建退休申请文档状态视图求助

嘿Davide，别发愁啦！作为T-SQL新手，刚接触多表关联确实容易卡壳，我来帮你一步步搭建这个视图，完全符合你的需求。

首先咱们先理清楚表之间的关联逻辑：

• tbRegistro是入口，通过ID_ANAGRAFICA关联tbAnagrafica获取员工个人信息；
• 通过ID_DOCUMENTAZIONE关联tbDocumentazione，拿到该员工对应的所有文档ID集合；
• 最后tbDocumentazione里的每个ID_XXX字段关联tbDettaglioDoc，判断文档是否已完整提交（PRESENTE=1且INCOMPLETO=0且DISABILITATO=0），否则标记为0。

接下来就是创建视图的完整T-SQL代码：

CREATE VIEW vwDatiRitiroDipendenti
AS
SELECT 
    a.ID AS ID_ANAGRAFICA,
    a.COGNOME,
    a.NOME,
    a.MATRICOLA,
    -- 判断ISTANZA文档状态：存在且完整提交则为1，否则0
    CASE 
        WHEN d.ID_ISTANZA IS NOT NULL 
             AND dd_istanza.PRESENTE = 1 
             AND dd_istanza.INCOMPLETO = 0 
             AND dd_istanza.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS ISTANZA,
    -- 判断ATTI_DISP文档状态
    CASE 
        WHEN d.ID_ATTIDISP IS NOT NULL 
             AND dd_attidisp.PRESENTE = 1 
             AND dd_attidisp.INCOMPLETO = 0 
             AND dd_attidisp.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS ATTI_DISP,
    -- 判断CEDOLINO文档状态
    CASE 
        WHEN d.ID_CEDOLINO IS NOT NULL 
             AND dd_cedolino.PRESENTE = 1 
             AND dd_cedolino.INCOMPLETO = 0 
             AND dd_cedolino.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS CEDOLINO,
    -- 以下是其他文档类型的状态判断，可根据实际需求调整
    CASE 
        WHEN d.ID_DATISENS IS NOT NULL 
             AND dd_datisens.PRESENTE = 1 
             AND dd_datisens.INCOMPLETO = 0 
             AND dd_datisens.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS DATISENS,
    CASE 
        WHEN d.ID_NULLAOSTA IS NOT NULL 
             AND dd_nullaosta.PRESENTE = 1 
             AND dd_nullaosta.INCOMPLETO = 0 
             AND dd_nullaosta.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS NULLAOSTA,
    CASE 
        WHEN d.ID_INESDEBITI IS NOT NULL 
             AND dd_inesdebiti.PRESENTE = 1 
             AND dd_inesdebiti.INCOMPLETO = 0 
             AND dd_inesdebiti.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS INESDEBITI,
    CASE 
        WHEN d.ID_DUM IS NOT NULL 
             AND dd_dum.PRESENTE = 1 
             AND dd_dum.INCOMPLETO = 0 
             AND dd_dum.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS DUM,
    CASE 
        WHEN d.ID_MOD27 IS NOT NULL 
             AND dd_mod27.PRESENTE = 1 
             AND dd_mod27.INCOMPLETO = 0 
             AND dd_mod27.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS MOD27,
    CASE 
        WHEN d.ID_CODFISC IS NOT NULL 
             AND dd_codfisc.PRESENTE = 1 
             AND dd_codfisc.INCOMPLETO = 0 
             AND dd_codfisc.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS CODFISC,
    CASE 
        WHEN d.ID_DOCIDENTITA IS NOT NULL 
             AND dd_docidentita.PRESENTE = 1 
             AND dd_docidentita.INCOMPLETO = 0 
             AND dd_docidentita.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS DOCIDENTITA,
    CASE 
        WHEN d.ID_CERTMORTE IS NOT NULL 
             AND dd_certmorte.PRESENTE = 1 
             AND dd_certmorte.INCOMPLETO = 0 
             AND dd_certmorte.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS CERTMORTE,
    CASE 
        WHEN d.ID_ATTONOTORIO IS NOT NULL 
             AND dd_attnotorio.PRESENTE = 1 
             AND dd_attnotorio.INCOMPLETO = 0 
             AND dd_attnotorio.DISABILITATO = 0 
        THEN 1 
        ELSE 0 
    END AS ATTONOTORIO
FROM tbRegistro r
INNER JOIN tbAnagrafica a ON r.ID_ANAGRAFICA = a.ID
LEFT JOIN tbDocumentazione d ON r.ID_DOCUMENTAZIONE = d.ID
-- 每个文档类型单独关联详情表，避免因某一个文档缺失导致整行数据丢失
LEFT JOIN tbDettaglioDoc dd_istanza ON d.ID_ISTANZA = dd_istanza.ID
LEFT JOIN tbDettaglioDoc dd_attidisp ON d.ID_ATTIDISP = dd_attidisp.ID
LEFT JOIN tbDettaglioDoc dd_cedolino ON d.ID_CEDOLINO = dd_cedolino.ID
LEFT JOIN tbDettaglioDoc dd_datisens ON d.ID_DATISENS = dd_datisens.ID
LEFT JOIN tbDettaglioDoc dd_nullaosta ON d.ID_NULLAOSTA = dd_nullaosta.ID
LEFT JOIN tbDettaglioDoc dd_inesdebiti ON d.ID_INESDEBITI = dd_inesdebiti.ID
LEFT JOIN tbDettaglioDoc dd_dum ON d.ID_DUM = dd_dum.ID
LEFT JOIN tbDettaglioDoc dd_mod27 ON d.ID_MOD27 = dd_mod27.ID
LEFT JOIN tbDettaglioDoc dd_codfisc ON d.ID_CODFISC = dd_codfisc.ID
LEFT JOIN tbDettaglioDoc dd_docidentita ON d.ID_DOCIDENTITA = dd_docidentita.ID
LEFT JOIN tbDettaglioDoc dd_certmorte ON d.ID_CERTMORTE = dd_certmorte.ID
LEFT JOIN tbDettaglioDoc dd_attnotorio ON d.ID_ATTONOTORIO = dd_attnotorio.ID;

代码说明： #

1. 表关联：

   • 用INNER JOIN关联tbRegistro和tbAnagrafica，确保只返回有退休申请记录的员工；
   • 用LEFT JOIN关联tbDocumentazione和所有tbDettaglioDoc实例，这样即使员工还没提交某些文档，整行数据依然会被返回，不会被过滤掉。

2. 状态判断：

   • 每个CASE语句都会检查三个核心条件：文档ID存在（已提交）、文档标记为PRESENTE=1、文档未标记为INCOMPLETO=0且未禁用（DISABILITATO=0）；
   • 满足所有条件返回1，否则返回0（对应未提交、不完整或禁用的状态）。

创建完视图后，你只需要执行SELECT * FROM vwDatiRitiroDipendenti就能得到你想要的格式结果啦！

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