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