这个结果怎么筛？
表里结构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
;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
--结果：
--BAA
--BBB
--CAA
--DNULL
--EAA
--EBB
--LDFNULL
;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
/*
abc
2013.07DDDAA
2013.07DDDBB
2013.08LDFNULL
*/
