NULL时SQL消除行 [英] SQL eliminate row when null
本文介绍了NULL时SQL消除行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有来自表1的数据:
AgeCount Age GenderCount Gender RaceCount Race
-----------------------------------------------------------------------------
12 1-10 null null null null
10 11-20 null null null null
null null 3 M null null
null null 5 F null null
null null null null 20 American Indian
null null null null 10 Africa
我喜欢删除NULL
的查询,所以我会得到:
I like a query to remove the NULL
's so I will get:
AgeCount Age GenderCount Gender RaceCount Race
----------------------------------------------------------------------------
12 1-10 3 M 20 American Indian
10 11-20 5 F 10 Africa
非null值可以按任何顺序连接.
The non-null values can be joined in any order.
我尝试过:
select *
from table1
where age is not null
or agecount is not null
or gendercount is not null
or gender is not null
or racecount is not null
or race is not null
但是没有给出理想的结果.
But its not giving the desired results.
推荐答案
基于提供的信息,以下查询将产生所需的结果:
Based on the information provided, the following query produces the desired results:
- 它加入3个子查询,每个非null组件(年龄,种族,性别)都包含1个子查询
- 它使用
row_number()
给出任意连接条件 - 它使用
full outer join
处理一种类型的值可能比另一种类型的值更多的情况.如果您添加了另一行年龄计数
- It joins 3 sub-queries, 1 for each of the non-null components (Age, Race, Gender)
- It uses a
row_number()
to give an arbitrary join condition - It uses a
full outer join
to handle the case when there might be more on one type of value than another e.g. if you added another row of of age count
declare @Test table (AgeCount int, Age varchar(16), GenderCount int, Gender varchar(1), RaceCount int, Race varchar(64));
insert into @Test (AgeCount, Age, GenderCount, Gender, RaceCount, Race)
values (12, '1-10', null, null, null, null)
, (10, '11-20', null, null, null, null)
, (null, null, 3, 'M', null, null)
, (null, null, 5, 'F', null, null)
, (null, null, null, null, 20, 'American Indian')
, (null, null, null, null, 10, 'Africa');
select AgeCount, Age, GenderCount, Gender, RaceCount, Race
from (
select AgeCount, Age
, row_number() over (order by AgeCount) row#
from @Test
where AgeCount is not null
) X
full outer join
(
select GenderCount, Gender
, row_number() over (order by GenderCount) row#
from @Test
where GenderCount is not null
) Y on Y.row# = X.row#
full outer join (
select RaceCount, Race
, row_number() over (order by RaceCount) row#
from @Test
where RaceCount is not null
) Z on Z.row# = X.row#;
给出以下内容:
AgeCount Age GenderCount Gender RaceCount Race
-------------------------------------------------------------------
10 11-20 3 M 10 Africa
12 1-10 5 F 20 American Indian
这篇关于NULL时SQL消除行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文