sql-server - SQL 有条件地将两个数据库列之一的结果返回到单个结果列

Question

您好，我有以下 SQL 查询，我希望有条件地将两个数据库列(DEA 和 DEA_ALT)之一的结果返回到一个结果列，具体取决于这两列中哪一列被赋值(不为空)。数据库设置为有两个字段可以存储名为 DEA# 的字段，但这两个字段具有相同的用途，因此我想根据哪一列被赋值(可以是其中之一)从任一列返回结果。

SELECT Distinct t1.UserName,
            t1.[LoginID],
            t7.FirstName, 
            t7.LastName, 
            t7.Title ,
            t7.EndDateOfValidity,      
            NPI = ISNULL(HStaffIdentifiers.Value,''),
            Type= HStaffIdentifiers.Type, 
            DEA=ISNULL(t5.PrescriberCode,''),
            --
            --
            DEA_ALT = ISNULL((select CASE ISNULL(PC.PrescriberCode,'')
                          when '' then ISNULL(HS.Value,'')
                          end
                          from HStaff PC
                          left outer join HStaffIdentifiers HS
                          ON PC.ObjectID = HS.Staff_oid
                          where HS.Type = 'DEA'
                          and HS.Staff_oid = HStaffIdentifiers.Staff_oid
                          ),ISNULL(t5.PrescriberCode,'')),
            --
            --
            Speciality = ISNULL(HDataDictionaryItem3.Description,''),
            Preference_Group = HDataDictionaryItem1.Name
        FROM HSUser t1  with (nolock)
             Left Outer JOIN HStaff t5  with (nolock) 
             ON t1.UserName=t5.MSINumber
             LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
             ON t5.ObjectID = HStaffIdentifiers.Staff_oid
             AND HStaffIdentifiers.Type = 'NPI'
             JOIN HPerson t6  with (nolock) 
             ON t5.ObjectID=t6.ObjectID
             JOIN HName t7  with (nolock) 
             ON t6.ObjectID=t7.Person_oid   
             and (t7.EndDateOfValidity < '2010-01-01 00:00:00.000'
                or  t7.EndDateOfValidity is null)

             JOIN HDataDictionaryItem HDataDictionaryItem1 WITH (NOLOCK)
             ON t1.PreferenceGroup_oid = HDataDictionaryItem1.ObjectID
                 AND HDataDictionaryItem1.ItemType_oid = 98

left outer join HDataDictionaryItem HDataDictionaryItem3 WITH (NOLOCK)
             ON t5.PrimarySpeciality_oid = HDataDictionaryItem3.ObjectID
             AND HDataDictionaryItem3.ItemType_oid = 43

             WHERE 

              t1.EndTime >= GETDATE()
             AND t1.BeginTime<=GETDATE()  
             AND t5.Active=1
             AND t6.IsDeleted=0
             AND t5.StaffType = 0 
order by  t7.LastName,  t7.FirstName

以下链接的结果片段: http://s7.postimg.org/i5xlsjhnv/12_31_2014_10_00_13_AM.jpg

正如您从查询结果(以红色突出显示)中看到的，在某些情况下，DEA 可能仅填充在这两列之一中，但是如果满足以下条件，我希望将结果返回到 DEA 列中的单个列:它被赋值，如果没有，那么我想检查 DEA_ALT 是否被赋值，如果是，则将结果返回到这一列。我怎样才能做到这一点？

Answer 1

正如@NoDisplayName所说，您应该只使用coalesce。据我所知，我认为它可以更简单一些。具体来说，如果您希望合并 DEA 和 DEA_ALT，您可以阅读本节

        DEA=ISNULL(t5.PrescriberCode,''),
        --
        --
        DEA_ALT = ISNULL((select CASE ISNULL(PC.PrescriberCode,'')
                      when '' then ISNULL(HS.Value,'')
                      end
                      from HStaff PC
                      left outer join HStaffIdentifiers HS
                      ON PC.ObjectID = HS.Staff_oid
                      where HS.Type = 'DEA'
                      and HS.Staff_oid = HStaffIdentifiers.Staff_oid
                      ),ISNULL(t5.PrescriberCode,'')),

然后这样写

Coalesce(t5.PrescriberCode, HStaffIden.Value) As DEA,

您需要为 DEA 值添加一个新表，如下所示

LEFT OUTER JOIN HStaffIdentifiers HStaffIden WITH (NOLOCK)
         ON HStaffIdentifiers.Staff_oid = HStaffIden.Staff_oid

然后您可以采用此加入条件

LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
         ON t5.ObjectID = HStaffIdentifiers.Staff_oid
         AND HStaffIdentifiers.Type = 'NPI'

并像这样删除 AND 条件

LEFT OUTER JOIN HStaffIdentifiers HStaffIdentifiers WITH (NOLOCK)
         ON t5.ObjectID = HStaffIdentifiers.Staff_oid

然后将此条件添加到where语句中以完成更改

AND HStaffIdentifiers.Type = 'NPI'
And HStaffIden = 'DEA'

编辑:修复了结果集，使其正确匹配您的原始查询