如何消除TSQL中的NULL字段 [英] How to eliminate NULL fields in TSQL

问题描述

我正在为 SQL Server 2008 R2 开发 TSQL 查询.我正在尝试开发此查询来识别一个记录/客户.因为其中一些值是 NULL，我目前正在对大多数表进行 LEFT JOINS.但是 LEFT JOIN 的问题在于，现在我为某些客户获得了 > 1 条记录.

I am developing a TSQL query for SQL Server 2008 R2. I am trying to develop this query to identify one record / client. 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 clients.

但是，如果我将其更改为 INNER JOIN，则某些客户端将被完全排除，因为它们对这些列具有 NULL 值.无论 NULL 值如何，如何将查询结果限制为仅一条记录/客户端?如果有非 NULL 值，那么我希望它选择具有非 NULL 值的记录.这是我目前的一些输出:

But if I change this to INNER JOINs then some clients are excluded entirely because they have NULL values for these columns. How do I limit the query result to just one record / client 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 some of my current output:

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    ethnicity_desc  religion
9CD932F1-6BE1-4F80-AB81-0CE32C565BCF    Atreides Foster Home 1  Atreides1               1   Yes Manchester, NH  Gulf Atlantic   Atreides1   00000007                Treatment Foster Home   4042 Arrakis Avenue, Springfield, VT  05156 Open/Re-opened  2011-06-01 00:00:00.000 NULL    NULL    NULL
DCE354D5-A7CC-409F-B5A3-89BF664B7718    Averitte, Leon and Sandra   00000044                1   Yes Birmingham, AL  Gulf Atlantic   AL Averitte, Leon and Sandra    00000044                Treatment Foster Home   3816 5th Avenue, Bessemer, AL  35020, [phone] Open/Re-opened  2011-08-05 00:00:00.000 NULL    NULL    NULL
DCE354D5-A7CC-409F-B5A3-89BF664B7718    Averitte, Leon and Sandra   00000044                1   Yes Birmingham, AL  Gulf Atlantic   AL Averitte, Leon and Sandra    00000044                Treatment Foster Home   3816 5th Avenue, Bessemer, AL  35020, [phone] Open/Re-opened  2011-08-05 00:00:00.000 Caucasian/White Non Hispanic    NULL
AD02A43C-6F38-4F35-8C9E-E12422690BFB    Bass, Matthew and Sarah 00000076                1   Yes Jacks    on, MS Central Gulf Coast  MS Bass, Matthew and Sarah  00000076                Treatment Foster Home   506 Eagelwood Drive, Florence, MS  39073, [phone] Open/Re-opened  2011-04-01 00:00:00.000 NULL    NULL    NULL
AD02A43C-6F38-4F35-8C9E-E12422690BFB    Bass, Matthew and Sarah 00000076                1   Yes Jackson, MS Central Gulf Coast  MS Bass, Matthew and Sarah  00000076                Treatment Foster Home   506 Eagelwood Drive, Florence, MS  39073, [phone] Open/Re-opened  2011-04-01 00:00:00.000 Caucasian/White NULL    Baptist

您可以看到 Averitte 和 Bass 个人资料名称都有一个记录，其中种族、民族、宗教为 NULL.如何消除这些行(第 2 行和第 4 行)?

You can see that both Averitte and Bass profile names have one record with NULL race, ethnicity, religion. How do I eliminate these rows (rows 2 and 4)?

这是我目前的查询:

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,
        ethnicity.description as ethnicity_desc,
        religion.description as religion
from  group_profile gp With (NoLock)
    --Office Information
        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 group_profile_race gpr with (nolock) on gpr.race_info_id = race.race_info_id
    left join race_info ri with (nolock) on ri.race_info_id = gpr.race_info_id
    left join ethnicity With(NoLock) On client.ethnicity = ethnicity.ethnicity_id
    left join religion on client.religion = religion.religion_id

推荐答案

尝试按 group_profile_id 分组并选择每隔一列的 MAX().MAX 将选择每个重复条目的非空值(如果存在一个，或者如果存在多个最大值).尽管乍一看，更有效的解决方案是对数据中的其他 NULL 行进行排序.

Try grouping by group_profile_id and selecting MAX() of every other column. MAX will select the non-null value (if one exists, or the maximum if multiple exist) of each repeating entry. Although the more efficient solution would involve sorting out additional NULL rows in the data, from first glance.

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