消除 TSQL 查询中的 NULL 行 [英] Eliminating NULL rows in TSQL query

问题描述

可能的重复:
如何消除TSQL中的NULL字段

我正在使用 SSMS 2008 R2 并且正在开发 TSQL 查询.我只想要 1 条记录/profile_name.因为其中一些值是 NULL，我目前正在对大多数表进行 LEFT JOINS.但是 LEFT JOIN 的问题在于，现在我为某些 profile_names 获得了 > 1 条记录！

I am using SSMS 2008 R2 and am developing a TSQL query. I want just 1 record / profile_name. Because some of these values are NULL, I am currently doing LEFT JOINS on most of the tables. But the problem with the LEFT JOINs is that now I get > 1 record for some profile_names!

但如果我将其更改为 INNER JOINs，那么一些 profile_names 将被完全排除，因为它们对这些列具有 NULL 值.无论 NULL 值如何，如何将查询结果限制为仅一条记录/profile_name?如果有非 NULL 值，那么我希望它选择具有非 NULL 值的记录.这是初始查询:

But if I change this to INNER JOINs then some profile_names are excluded entirely because they have NULL values for these columns. How do I limit the query result to just one record / profile_name regardless of NULL values? And if there are non-NULL values then I want it to choose the record with non-NULL values. Here is initial query:

select distinct
        gp.group_profile_id,
        gp.profile_name,
        gp.license_number,
        gp.is_accepting,
        case when gp.is_accepting = 1 then 'Yes'
            when gp.is_accepting = 0 then 'No '
            end as is_accepting_placement,
        mo.profile_name as managing_office,
        regions.[region_description] as region,     
        pv.vendor_name,
        pv.id as vendor_id,
        at.description as applicant_type,
        dbo.GetGroupAddress(gp.group_profile_id, null, 0) as [Office Address],
        gsv.status_description
from  group_profile gp With (NoLock)
    inner join group_profile_type gpt With (NoLock) on gp.group_profile_type_id = gpt.group_profile_type_id and gpt.type_code = 'FOSTERHOME' and gp.agency_id = @agency_id and gp.is_deleted = 0
    inner join group_profile mo With (NoLock) on gp.managing_office_id = mo.group_profile_id
    left outer join payor_vendor pv With (NoLock) on gp.payor_vendor_id = pv.payor_vendor_id
    left outer join applicant_type at With (NoLock) on gp.applicant_type_id = at.applicant_type_id and at.is_foster_home = 1
    inner join group_status_view gsv With (NoLock) on gp.group_profile_id = gsv.group_profile_id and gsv.status_value = 'OPEN' and gsv.effective_date =  
    (Select max(b.effective_date) from  group_status_view b  With (NoLock)
    where gp.group_profile_id = b.group_profile_id)
    left outer join regions With (NoLock) on isnull(mo.regions_id, gp.regions_id) = regions.regions_id
    left join enrollment en on en.group_profile_id = gp.group_profile_id
    join event_log el on el.event_log_id = en.event_log_id
    left join people client on client.people_id = el.people_id

如你所见，上面查询的结果是1行/profile_name:

As you can see, the results of the above query is 1 row / profile_name:

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2

但是现在看看当我添加 2 个 LEFT JOIN 和 1 个附加列时会发生什么:

But now watch what happens when I add in 2 LEFT JOINs and 1 additional column:

select distinct
        gp.group_profile_id,
        gp.profile_name,
        gp.license_number,
        gp.is_accepting,
        case when gp.is_accepting = 1 then 'Yes'
             when gp.is_accepting = 0 then 'No '
            end as is_accepting_placement,
        mo.profile_name as managing_office,
        regions.[region_description] as region,     
        pv.vendor_name,
        pv.id as vendor_id,
        at.description as applicant_type,
        dbo.GetGroupAddress(gp.group_profile_id, null, 0) as [Office Address],
        gsv.status_description,
            ri.[description] as race
from  group_profile gp With (NoLock)
    inner join group_profile_type gpt With (NoLock) on gp.group_profile_type_id = gpt.group_profile_type_id and gpt.type_code = 'FOSTERHOME' and gp.agency_id = @agency_id and gp.is_deleted = 0
    inner join group_profile mo With (NoLock) on gp.managing_office_id = mo.group_profile_id
    left outer join payor_vendor pv With (NoLock) on gp.payor_vendor_id = pv.payor_vendor_id
    left outer join applicant_type at With (NoLock) on gp.applicant_type_id = at.applicant_type_id and at.is_foster_home = 1
    inner join group_status_view gsv With (NoLock) on gp.group_profile_id = gsv.group_profile_id and gsv.status_value = 'OPEN' and gsv.effective_date =  
    (Select max(b.effective_date) from  group_status_view b  With (NoLock)
    where gp.group_profile_id = b.group_profile_id)
    left outer join regions With (NoLock) on isnull(mo.regions_id, gp.regions_id) = regions.regions_id
    left join enrollment en on en.group_profile_id = gp.group_profile_id
    join event_log el on el.event_log_id = en.event_log_id
    left join people client on client.people_id = el.people_id
    left join race With (NoLock) on el.people_id = race.people_id
    left join race_info ri with (nolock) on ri.race_info_id = race.race_info_id

上述查询的结果是所有的 profile_names 都相同，但有些是 NULL 种族值:

The above query results in all of the same profile_names, but some with NULL race values:

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2  race

不幸的是，我需要为这个额外的字段值(种族)加入 2 个额外的表，这让事情变得复杂.如果我只是将上面的最后两个 LEFT JOIN 更改为 INNER JOIN，那么我将消除上面的 NULL 行.但我也删除了一些 profile_names:

Unfortunately it complicates matters that I need to join in 2 additional tables for this one additional field value (race). If I simply change the last two LEFT JOINs above to INNER JOINs then I eliminate the NULL rows above. But I also eliminate some of the profile_names:

group_profile_id    profile_name    license_number  is_accepting    is_accepting_placement  managing_office region  vendor_name vendor_id   applicant_type  Office Address  status_description  Cert Date2  race

希望我已经提供了您回答这个问题所需的所有详细信息.

Hopefully I have provided all of the details that you need for this question.

推荐答案

不是最优雅的解决方案，但可行的解决方案:

Not the most elegant solution, but one that will work:

select [stuff]
from  group_profile gp With (NoLock) 
  inner join group_profile_type gpt With (NoLock) on gp.group_profile_type_id = gpt.group_profile_type_id and gpt.type_code = 'FOSTERHOME' and gp.agency_id = @agency_id and gp.is_deleted = 0 
  inner join group_profile mo With (NoLock) on gp.managing_office_id = mo.group_profile_id 
  join payor_vendor pv on ISNULL(gp.payor_vendor_id, 'THISVALUEWILLNEVEROCCUR') = ISNULL(pv.payor_vendor_id, 'THISVALUEWILLNEVEROCCUR')
...etc...

我发布的最大问题是您将进行大量表格扫描.

Biggest issue with what I posted is that you'll be doing a whole lot of table scans.

这篇关于消除 TSQL 查询中的 NULL 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！