表里结构A B C
2013.08 LDF NULL
2013.07 LDF AA
现在只select B,C FROM TABLE 就是只取字段B,C如何筛掉第一行C=NULL
------解决方案--------------------
;WITH MyTable AS
(
select '2013.08' AS a ,'LDF' AS b,NULL AS c union all
select '2013.07','B','AA' union all
select '2013.07','B','BB' union all
select '2013.09','B',NULL union all --b=‘B’,保留C非NULL的条目
select '2013.01','C','AA' union all
select '2013.08','D',NULL union all --b=‘D’,只保留一条Null
select '2013.08','D',NULL union all
select '2013.07','E','AA' union all
select '2013.07','E','BB' --b=‘E’,保留所有条目
), C1 AS
(
SELECT *,first_value(rn) OVER(PARTITION BY b ORDER BY rn DESC) mrn
FROM
(
SELECT b,c,rn=ROW_NUMBER() OVER(PARTITION BY b ORDER BY c)
FROM MyTable
) T
)
SELECT b,c
FROM C1
WHERE (c IS NULL AND rn=mrn) OR c IS NOT NULL
------解决方案--------------------
借用一下7楼的表
;WITH MyTable AS
(
select '2013.08' AS a ,'LDF' AS b,NULL AS c union all
select '2013.07','B','AA' union all
select '2013.07','B','BB' union all
select '2013.09','B',NULL union all
select '2013.01','C','AA' union all
select '2013.08','D',NULL union all
select '2013.08','D',NULL union all
select '2013.07','E','AA' union all
select '2013.07','E','BB'
)
,tb as
(
select ROW_NUMBER() over(order by b) as RN,* from mytable
)
select a.b,a.c from tb a where not exists
(
select 1 from tb b where a.b = b.b and a.RN >b.RN
)
union
select b,c from tb where c is not null
--结果:
--B AA
--B BB
--C AA
--D NULL
--E AA
--E BB
--LDF NULL
------解决方案--------------------
;WITH T AS
(
select '2013.08' as a,'LDF' as b,NULL as c union all
--select '2013.07' ,'LDF' ,'AA' union all
select '2013.07' ,'DDD' ,null union all
select '2013.07' ,'DDD' ,'AA' union all
select '2013.07' ,'DDD' ,'BB'
)
select a,b,c
from
(
select a,b,c,
dense_rank() over(partition by b
order by case when c is not null then 1 else 2 end) as rownum
from T
)a
where rownum = 1
/*
a b c
2013.07 DDD AA
2013.07 DDD BB
2013.08 LDF NULL
*/