有三个字段
ID ProccessTime UserName
1 2012-09-06 18:20:28.897 kyb
2 NULL kyb
3 2012-09-06 18:20:28.897 kyc
4 NULL kyc
5 NULL kyd
现在我要的结果是：
ID ProccessTime UserName
1 2012-09-06 18:20:28.897 kyb
3 2012-09-06 18:20:28.897 kyc
5 NULL kyd
过滤的条件是：同一个UserName，如果存在ProccessTime不为空的记录，则把该UserName下面ProccessTime为空的记录去掉
每个UserName都会有一条ProccessTime为空的记录，并且仅有一条。
比如上面过滤的ID为2、4的行原因是：
因为kyb 和kyc 已经存在有ProccessTime不为空的记录
------解决方案--------------------
这个是我的，当kyb还有一条正常的数据时，看结果
- SQL code
WITH t(id,proccessTime,userName)AS(SELECT 1,'2012-09-06 18:20:28.897','kyb'UNION ALL SELECT 2,NULL,'kyb'UNION ALLSELECT 3,'2012-09-06 18:20:28.897','kyc'UNION ALLSELECT 4,null,'kyc'UNION ALLSELECT 5,NULL,'kyd'UNION ALLselect 6,'2012-09-02 18:20:28.897','kyb' )--select * from t--except--select * from t where t.proccessTime is null --and exists(select 1 from t where t.UserName=UserName and proccessTime is not null);SELECT * FROM (SELECT t1.id,t1.proccessTime,t1.userName,CASE WHEN ISNULL(t1.proccessTime,'')='' THEN 0 ELSE 1 END AS SS,T2.NUMFROM t t1 LEFT JOIN(SELECT username, COUNT(username) AS num FROM t GROUP BY userName)AS t2 ON t2.username=t1.username) AS BWHERE (B.num=1) OR (B.num>1 AND B.SS<>0);id proccessTime userName SS NUM----------- ----------------------- -------- ----------- -----------6 2012-09-02 18:20:28.897 kyb 1 31 2012-09-06 18:20:28.897 kyb 1 33 2012-09-06 18:20:28.897 kyc 1 25 NULL kyd 0 1