日期:2014-05-17 浏览次数:20711 次
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