Pl Sql 中看似简单的查询

Question

我有一个以下格式的“缺陷”表：

id   status  stat_date   line   div  area
1    Open    09/21/09    F      A    cube
1    closed  01/01/10    F      A    cube
2    Open    10/23/09    B      C    Back
3    Open    11/08/09    S      B    Front
3    closed  12/12/09    S      B    Front   

我的问题是我想编写一个仅提取“开放”缺陷的查询。如果我编写一个查询来简单地提取所有未解决的缺陷，那么我会得到错误的结果，因为存在一些缺陷， 有 2 条与之关联的记录。例如，通过我编写的查询，即使它们已关闭，我也会在结果中得到缺陷 id#s 1 和 3。我希望我已经很好地解释了我的问题。谢谢。

Answer 1

用途：

SELECT t.*
  FROM DEFECTS t
  JOIN (SELECT d.id,
               MAX(d.stat_date) 'msd'
          FROM DEFECTS d
      GROUP BY d.id) x ON x.id = t.id
                      AND x.msd = t.stat_date
 WHERE t.status != 'closed'

1. 连接获取每个 id 值的最新日期。
2. 根据 id 和 date 连接回原始表，以便仅获取最新的行。
3. 过滤掉那些处于关闭状态的行，以了解当前打开的行

Answer 2

因此，您希望获取每个 id 的最新行，并且仅选择那些打开的行。这是常见的greatest-n-per-group问题的变体。

我会这样做：

SELECT d1.*
FROM defects d1
LEFT OUTER JOIN defects d2
  ON (d1.id = d2.id AND d1.stat_date < d2.stat_date)
WHERE d2.id IS NULL
  AND d1.status = 'Open';

Answer 3

Select * 
from defects d
where status = 'Open'
and not exists (
   select 1 from defects d1
   where d1.status = 'closed'
   and d1.id = d.id
   and d1.stat_date > d.stat_date
)

Answer 4

这应该得到你想要的。我不会有打开和关闭缺陷的记录，而只有跟踪单个缺陷的单个记录。但这可能不是你可以轻易改变的事情。

SELECT id FROM defects 
WHERE status = 'OPEN' AND id NOT IN 
(SELECT id FROM defects WHERE status = 'closed')

Answer 5

此查询处理多个打开/关闭/打开，并且只传递一次数据（即没有自连接）：

SELECT * FROM
(SELECT DISTINCT
        id
       ,FIRST_VALUE(status)
        OVER (PARTITION BY id
              ORDER BY stat_date desc)
        as last_status
       ,FIRST_VALUE(stat_date)
        over (PARTITION BY id
              ORDER BY stat_date desc)
        AS last_stat_date
       ,line
       ,div
       ,area
 FROM defects)
WHERE last_status = 'Open';