表里结构A B C
2013.08 LDF NULL
2013.07 LDF AA
现在只select B,C FROM TABLE 就是只取字段B,C如何筛掉第一行C=NULL
2013.08 LDF NULL
2013.07 LDF AA
现在只select B,C FROM TABLE 就是只取字段B,C如何筛掉第一行C=NULL
解决方案 »
- sql中空字符的替换问题
- 简单的问题:游标
- 求高手,sql难题
- SQL 2000 根据SQL日志恢复数据库,急急急~~~~~~~~~~~~~~~~~~~~~
- 存储过程在asp页面程序中执行和在查询分析器中执行速度差异的问题
- 对一个表(三个键作为主键)进行分组统计,急!谢谢!
- 按月求每个部门最高工资和最低工资的员工
- SQL2000中文版开发的程序,在SQL7/2000英文版下使用会出哪些错误?
- 请帮忙开发一个软件,要求较高的加密性能,情报价或联系。
- 关于三层体系???问题多多!
- VB6+SQL2008存储过程疑难问题,求帮助
- 求助:MS SQL2000中求SQL语句。allcode。在线等。。。
is not null:不是null这样来过滤数据
select B,C FROM [TABLE] where not(C=NULL)select B,C FROM [TABLE] where 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 --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
B BB
B AA
C AA
D NULL
E BB
E AA
LDF 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--结果:
--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
*/