如何在SQL查询中只显示失败的CASE语句

Question

我只需要在下面的查询中显示失败的记录，

SELECT
E.MEMNO, 
'' ,
E.EXITDATE, 
S.STASTART, 
E.STATUS, 
S.SSTA, 
E.STATUSDATE,
S.RN,
CASE 
WHEN E.EXITDATE >= S.STASTART 
THEN 'Pass' 
ELSE 'Fail'  
END 
FROM
(SELECT * 
FROM EXITRETIREMENT 
WHERE STATUS IN (35, 45, 55, 75))  AS E 
INNER JOIN
(SELECT 
*, 
ROW_NUMBER() OVER (PARTITION BY MEMNO ORDER BY STASTART DESC) Rn  
FROM STATHIS) S ON E.MEMNO = S.MEMNO
WHERE       
rn = 2 

Answer 1

试试这个：

Select E.MEMNO, 
'' ,
E.EXITDATE, 
S.STASTART, 
E.STATUS, 
S.SSTA, 
E.STATUSDATE,
S.RN,
CASE 
WHEN E.EXITDATE >= S.STASTART 
then 'Pass' else 'Fail' 
END 
FROM   (Select * from EXITRETIREMENT where STATUS in  (35, 45, 55, 75))  AS E 
INNER JOIN
(SELECT * , ROW_NUMBER()Over(PARTITION by MEMNO Order by STASTART desc) Rn  
FROM STATHIS ) S 
ON E.MEMNO = S.MEMNO and E.EXITDATE < S.STASTART
WHERE      
rn = 2