如何返回满足特定条件的特定行

Question

我对SQL完全陌生。我正在处理一个应该只从表返回特定行的查询。(看。示例源表和下面所需的输出)。有人能帮我弄清楚这个吗？我尝试过在迭代行时搜索相关的主题，但是没有找到我想要的答案。谢谢。

样本表

Machine   Station   Defect Code CreateTime
1         LU        f1          8/20/2013 7:07
1         LU        f2          8/20/2013 7:07
1         SLC       d1          8/20/2013 7:08
1         SLC       d2          8/20/2013 7:09
1         SLC       d3          8/20/2013 7:10
1         SLC       d1          8/20/2013 7:10
1         SLC       d2          8/20/2013 7:11
1         SLC       d1          8/20/2013 7:17
1         SLC       d1          8/20/2013 7:20
1         UL        f3          8/20/2013 7:26
1         UL        f4          8/20/2013 7:26
1         UL        f3          8/20/2013 7:26
1         UL        f4          8/20/2013 7:26
2         LU        f2          8/20/2013 7:29
2         SLC       d1          8/20/2013 7:31
2         SLC       d1          8/20/2013 7:38
2         SLC       d2          8/20/2013 7:38
2         SLC       d3          8/20/2013 7:39
2         SLC       d2          8/20/2013 7:39
2         SLC       d4          8/20/2013 7:39
2         SLC       d5          8/20/2013 7:42
2         SLC       d1          8/20/2013 7:43
2         SLC       d1          8/20/2013 7:52
2         SLC       d1          8/20/2013 7:53
2         SLC       d2          8/20/2013 7:53
2         SLC       d2          8/20/2013 7:54
2         SLC       d4          8/20/2013 7:55
2         SLC       d5          8/20/2013 7:56

所需结果：

在Machine1上触发OCAP。d1多次发生

Machine Station Defect Code CreateTime
1       SLC     d1          8/20/2013 7:08
1       SLC     d1          8/20/2013 7:10
1       SLC     d1          8/20/2013 7:17
1       SLC     d1          8/20/2013 7:20

在Machine1上触发OCAP。d1连续发生

Machine Station Defect Code CreateTime
1       SLC     d1          8/20/2013 7:17
1       SLC     d1          8/20/2013 7:20

在机器2. d1连续发生时触发OCAP

Machine Station Defect Code CreateTime
2       SLC     d1          8/20/2013 7:31
2       SLC     d1          8/20/2013 7:38

机器上触发的OCAP 2. d2多次发生

Machine Station Defect Code CreateTime
2       SLC     d2          8/20/2013 7:38
2       SLC     d2          8/20/2013 7:39
2       SLC     d2          8/20/2013 7:53

Answer 1

多次发生将是最容易的。计算每个组合中可以找到多少个，并选择计数大于3的组合。

SELECT S.Machine,
       S.Station,
       S.DefectCode,
       S.CreateTime
FROM ( SELECT Machine,
              Station,
              DefectCode,
              CreateTime,
              COUNT(CreateTime) OVER (PARTITION BY Machine, Station, DefectCode) AS DefectCount
       FROM SAMPLE
       WHERE Machine = :machine ) S
WHERE S.DefectCount > 3

连续发生有点困难(下面的查询可能不是最优的)。假设连续日期是“连续日期”，则可以尝试以下操作：

WITH OrderedSample
AS ( SELECT Machine,
            Station,
            DefectCode,
            CreateTime,
            ROW_NUMBER() OVER (ORDER BY CreateTime) AS RowNr
     FROM SAMPLE
     WHERE Machine = :machine )

SELECT Current.Machine,
       Current.Station,
       Current.DefectCode,
       Current.CreateTime
FROM OrderedSample Current
WHERE EXISTS ( SELECT 1
               FROM OrderedSample Next
               WHERE Current.Machine = Next.Machine
               AND   Current.Station = Next.Station
               AND   Current.DefectCode = Next.DefectCode
               AND   Current.RowNr = Next.RowNr - 1 )
OR    EXISTS ( SELECT 1
               FROM OrderedSample Prev
               WHERE Current.Machine = Prev.Machine
               AND   Current.Station = Prev.Station
               AND   Current.DefectCode = Prev.DefectCode
               AND   Current.RowNr = Prev.RowNr + 1 )

编辑

刚刚意识到第二个查询变得更容易一些，因为您使用的是固定机器：

WITH OrderedSample
AS ( SELECT Machine,
            Station,
            DefectCode,
            CreateTime,
            ROW_NUMBER() OVER (ORDER BY CreateTime) AS RowNr
     FROM SAMPLE
     WHERE Machine = :machine )

SELECT Curr.Machine,
       Curr.Station,
       Curr.DefectCode,
       Curr.CreateTime, RowNr
FROM OrderedSample Curr
WHERE EXISTS ( SELECT 1
               FROM OrderedSample Next
               WHERE Curr.Station = Next.Station
               AND   Curr.DefectCode = Next.DefectCode
               AND   Curr.RowNr = Next.RowNr - 1 )
OR    EXISTS ( SELECT 1
               FROM OrderedSample Prev
               WHERE Curr.Station = Prev.Station
               AND   Curr.DefectCode = Prev.DefectCode
               AND   Curr.RowNr = Prev.RowNr + 1 )

编辑2：注意到机器和站点是两列，编辑

编辑3：基于Sql示例的一些修复