时间戳

首页 > 解决方案 > SQL查找哪个表填充了diag_code，然后在lookup_table中查找

sql-server - SQL查找哪个表填充了diag_code，然后在lookup_table中查找

问题描述

我有一个查询，并且 diag_code 在一个表 (UM_SERVICE) 或另一个 (LOS) 中，但我无法连接两个表以获取我能想到的不为空的 diag_code。这看起来可以用于查找 diag_code 是否在表和查找表之一中吗？LOS 和 UM_SERVICE 可能在不同的行上都有一个诊断代码，它们可能不同，并且两者或一个都可能在查找表中。我在互联网搜索中没有看到任何内容。

这是一个简化的存储过程：

SELECT distinct
      c.id
      ,uc.id
      ,c.person_id 
    FROM dbo.CASES c 
    INNER JOIN dbo.UM_CASE uc with (NOLOCK) ON uc.case_id = c.id 
    LEFT JOIN dbo.UM_SERVICE sv (NOLOCK) ON sv.case_id =  omc.case_id 
    LEFT JOIN dbo.UM_SERVICE_CERT usc on usc.service_id = sv.id  
    LEFT JOIN dbo.LOS S WITH (NOLOCK) ON S.case_id = UC.case_id 
    LEFT JOIN dbo.LOS_EXTENSION SC WITH (NOLOCK) ON SC.los_id = S.id 
    INNER JOIN dbo.PERSON op with (NOLOCK) on op.id = c.Person_id
    WHERE
        (sv.diag_code is not null and c.case_id = sv.case_id
        or
        s.diag_code is not null and c.case_id = s.case_id)
      and     
        (sv.diag_code is not null and sv.diag_code in (select diag_code from TABLE_LOOKUP)
        or
        s.diag_code is not null and s.diag_code in (select diag_code from TABLE_LOOKUP)

Table setups like this:

    CASES
    id   person_id   
    
    UM_CASE
    case_id  
    
    LOS
    case_id  id
    
    LOS_EXTENSION
    los_id
    
    Person
    id    cid
    
    UM_SERVICE
    case_id     diag_code

    UM_SERVICE_CERT
    service_id    id

    TABLE_LOOKUP
    diag_code

标签： sql-serverstored-procedures

解决方案

由于您正在运行两个不同的搜索，因此通过单独编写搜索然后使用运算符将​​两个结果集放在一起来编写/读取会容易得多UNION。这将以与您对单个结果集UNION的使用类似的方式消除两个结果集中的重复项。SELECT DISTINCT

像这样：

/*first part of union performs seach using filter on dbo.UM_SERVICE*/
SELECT 
    c.id
    ,uc.id
    ,c.person_id 
FROM 
    dbo.CASES AS c 
    INNER JOIN dbo.UM_CASE AS uc ON uc.case_id=c.id 
    LEFT JOIN dbo.UM_SERVICE AS sv ON sv.case_id =  omc.case_id 
    LEFT JOIN dbo.UM_SERVICE_CERT AS usc on usc.service_id=sv.id  
    LEFT JOIN dbo.LOS AS S ON S.case_id =  UC.case_id 
    LEFT JOIN dbo.LOS_EXTENSION AS SC ON SC.los_id= S.id 
    INNER JOIN dbo.PERSON AS op on op.id=c.Person_id
WHERE 
    sv.diag_code in (select diag_code from TABLE_LOOKUP) /*will eliminate null values in sv.diag_code*/
UNION /*deduplicate result sets*/
/*second part of union performs search using filter on dbo.LOS*/
SELECT 
    c.id
    ,uc.id
    ,c.person_id 
FROM 
    dbo.CASES AS c 
    INNER JOIN dbo.UM_CASE AS uc ON uc.case_id=c.id 
    LEFT JOIN dbo.UM_SERVICE AS sv ON sv.case_id =  omc.case_id 
    LEFT JOIN dbo.UM_SERVICE_CERT AS usc on usc.service_id=sv.id  
    LEFT JOIN dbo.LOS AS S ON S.case_id =  UC.case_id 
    LEFT JOIN dbo.LOS_EXTENSION AS SC ON SC.los_id= S.id 
    INNER JOIN dbo.PERSON AS op on op.id=c.Person_id
WHERE 
    s.diag_code in (select diag_code from TABLE_LOOKUP); /*will eliminate null values in s.diag_code*/

推荐阅读