能够获得该序列号的最近失败进程(Prcs)的查询,如果最近的进程(Prcs)被传递,它将不会显示。但是我的问题是,如果最后一个进程通过了,不要显示串行number.Now im能够得到最近失败的进程,但是如果最后一个进程是pass.Thank you,我想忽略它
我尝试的查询-:
SELECT tt.id,tt.serialnumber,tt.prcs,tt.result
FROM tblTest tt
INNER JOIN
(SELECT serialnumber,prcs MAX(id) AS MaxID
FROM tblTest
GROUP BY serialnumber,prcs) groupedtt
ON tt.prcs = groupedtt.prcs AND tt.id = groupedtt.MaxID
WHERE result='FAIL' AND tt.sn='A01'
ORDER by serialnumber
发布于 2018-10-02 05:05:06
尝试:
WITH cte AS ( SELECT id,
serialnumber,
prcs,
result,
ROW_NUMBER() OVER (PARTITION BY serialnumber
ORDER BY id DESC) rn
FROM tblTest )
SELECT id, serialnumber, prcs, result
FROM cte
WHERE rn = 1
AND result = 'FAIL'
如果“最后进程”和“第五进程”失败,应同时显示两者
尝试:
SELECT t1.*
FROM tblTest t1
WHERE t1.result = 'FAIL'
AND NOT EXISTS ( SELECT 1
FROM tblTest t2
WHERE t1.serialnumber = t2.serialnumber
AND t1.id < t2.id
AND t2.result != 'FAIL' )
https://dba.stackexchange.com/questions/219034
复制相似问题