SQL 基于共享列有条件地选择一行或两行

Question

我有尝试通过测试的设备。当他们这样做时，我会插入设备的数据库，测试，通过或失败，以及时间戳。我想要的是选择所有通过并失败，但如果有通过，我不希望失败。如果一个测试多次失败并且没有通过，我只想要最近的失败。

两个例子。表格有 4 行：

Device Test Pass TimeStamp

X       T1   0   some time
X       T1   1   some time
Y       T2   0   some time
Y       T2   0   some time

我只想在它通过时选择 X，我不希望失败，因为它现在已经过去了。对于 Y，我想要最近的失败。我甚至希望朝着正确的方向推动。

Answer 1

这是一个

带有扭曲的优先级查询，可以使用窗口函数或union all来做到这一点。 让我们采用第二种方法：

select t.*
from t
where t.pass = 1
union all
(select top (1) with ties t.*
 from t
 where t.pass = 0 and
       not exists (select 1 from t t2 where t2.device = t.device and t2.test = t.test and t2.pass = 1)
 order by device, test, row_number() over (order by timestamp desc)
)

或者，更简单地说：

select t.*
from (select t.*,
             rank() over (partition by by device, test
                          order by pass desc,
                                   (case when pass = 0 then timestamp end) desc
                         ) as seqnum
      from t
     ) t
where seqnum = 1;

rank()的目的是所有通关都将是第一个，并且排名值为 1（如果存在）。 如果没有，则只有最近的失败的等级为 1。

Answer 2

与

CTE 联合所有方法的变体，通过单独的分组和过滤来提高可读性，假设数据集大小合理。

;with LatestPassingTests as
(
    select  
        Device,
        Test,
        Pass,
        max([Timestamp]) as 'Timestamp'
    from t                
    where Pass = 1
    group by Device, Test, Pass
),
DeduplicatedTestResults
as
(
    select 
        Device,
        Test,
        Pass,
        [Timestamp]
    from LatestPassingTests        
    union all
    select  
        t.Device,
        t.Test,
        t.Pass,
        max(t.[Timestamp]) as 'Timestamp'
    from @tempstuff as t
        left outer join LatestPassingTests as p on (t.Device = p.Device)
    where p.Device is null
    group by t.Device, t.Test, t.Pass
)
select  
    Device,
    Test,
    Pass,
    [Timestamp] 
from DeduplicatedTestResults

Answer 3

如果在同一设备中通过并测试后没有失败的可能性，只需使用具有最大时间的简单内部查询：

select Device, Test, Pass, TimeStamp from table 
join (
select Device, Test, max(Timstamp) as TimeStamp from table group by 1,2 ) t1
on t1.Device=table.Device and t1.Test=table.Test and t1.TimeStamp =table.TimeStamp

如果在同一设备和测试中通过后有可能失败，并且您只想通过，则需要 2 个内部联接：

select Device, Test, Pass, TimeStamp from table join (
select Device, Test, Pass, max(TimeStamp) as TimeStamp from table 
join (
select Device, Test, max(Pass) as Pass from table group by 1,2 ) t1
on t1.Device=table.Device and t1.Test=table.Test and t1.Pass =table.Pass
group by 1,2,3 ) t2
on t2.Device=table.Device and t2.Test=table.Test and t2.Pass =table.Pass and t2.TimeStamp =table.TimeStamp