日期:2014-05-17  浏览次数:20711 次

SQL 如何过滤符合条件的行
有三个字段
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 ALL
SELECT 3,'2012-09-06 18:20:28.897','kyc'
UNION ALL
SELECT 4,null,'kyc'
UNION ALL
SELECT  5,NULL,'kyd'
UNION ALL
select 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.NUM
FROM t t1 
LEFT JOIN
(SELECT username, COUNT(username) AS num FROM t GROUP BY userName)
AS t2 
ON t2.username=t1.username
) AS B
WHERE (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           3
1           2012-09-06 18:20:28.897 kyb      1           3
3           2012-09-06 18:20:28.897 kyc      1           2
5           NULL                    kyd      0           1