选择具有相同ID的行，但在另一列SQL Server中选择不同的值

Question

我有一张表，显示了一件证据的监护链。证据可以在一个位置进行分析，但也可以转移到不同的实验室进行分析。我正在尝试编写一个查询，如果在不同的实验室中分析了案例编号。

这是数据的一个示例：Casenumber 1在化学实验室开始，然后转移到DNA实验室，然后转回化学实验室。案例2仅在化学实验室中相关联(在查询结果中无需查看此情况(。

ID   CaseNumber     Lab        ActionDate   Action
-------------------------------------------------------------------
1     1             Chem       1/1/2019     case created
2     1             Chem       1/2/2019     container created
3     1             DNA        2/1/2019     container routed to DNA
4     1             DNA        2/3/2019     evidence analyzed
5     1             Chem       2/3/2019     edit route
6     2             Chem       2/4/2019     create case
7     2             Chem       2/5/2019     analyze evidence

这是我到目前为止所拥有的。这返回Casenumber和独特的实验室，但我想以某种方式合并动作日期，以显示其路由到新位置的实际日期。

SELECT DISTINCT
    casenumber, lab 
FROM 
    summary 
WHERE 
    casenumber IN (SELECT a.casenumber 
                   FROM summary a
                   JOIN summary b on b.casenumber = a.casenumber AND b.lab <> a.lab)
    AND actiondate BETWEEN '2019-01-02' AND '2019-01-04'
ORDER BY
    casenumber

我希望查询的结果看起来如下。我想查看每个实验室的第一个条目(因为那是它实际上是路由到新位置的日期(

ID   CaseNumber     Lab        ActionDate   Action
-------------------------------------------------------------------
1     1              Chem       1/1/2019    case created
3     1              DNA        2/1/2019    container routed to DNA
5     1              Chem       2/3/2019    container routed to Chem

Answer 1

有很多方法可以实现这一目标，这是其中之一。请注意，这仅适用于SQL Server 2012 。

create table #Something
(
    ID int
    , CaseNumber int
    , Lab varchar(10)
    , ActionDate date
    , Action varchar(50)
)
insert #Something values
(1, 1, 'Chem', '1/1/2019', 'case created')
, (2, 1, 'Chem', '1/2/2019', 'container created')
, (3, 1, 'DNA', '2/1/2019', 'container routed to DNA')
, (4, 1, 'DNA', '2/3/2019', 'evidence analyzed')
, (5, 1, 'Chem', '2/3/2019', 'edit route')
, (6, 2, 'Chem', '2/4/2019', 'create case')
, (7, 2, 'Chem', '2/5/2019', 'analyze evidence')
;
with SortedResults as
(
    select s.ID
        , s.CaseNumber
        , s.Lab
        , s.ActionDate
        , Action = case when LAG(Lab, 1) over(partition by CaseNumber order by ActionDate) is null then Action 
            when LAG(Lab, 1) over(partition by CaseNumber order by ActionDate) = Lab then NULL
            else 'container routed to ' + Lab end
    from #Something s
)
select *
from SortedResults
where Action > ''
order by CaseNumber, ActionDate
drop table #Something