在Pl Sql中看似简单的查询

I have a table "defects" in the following format:

我有一个表格“缺陷”，格式如下：

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

My problem is that I want to write a query that just extracts the "Open" defects. If I write a query to simply extract all open defects, then I get the wrong result because there are some defects, that have 2 records associated with it. For example, with the query that I wrote I would get defect id#s 1 and 3 in my result even though they are closed. I hope I have explained my problem well. Thank you.

我的问题是我想写一个只提取“开放”缺陷的查询。如果我编写一个查询来简单地提取所有打开的缺陷，那么我得到错误的结果，因为有一些缺陷，有2个记录与之关联。例如，使用我编写的查询，即使它们已关闭，我的结果中也会出现缺陷ID＃s 1和3。我希望我能很好地解释我的问题。谢谢。

5 个解决方案

#1

Use:

使用：

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'

The join is getting the most recent date for each id value.
联接获取每个id值的最新日期。
Join back to the original table on based on the id and date in order to get only the most recent rows.
根据id和日期加入原始表，以便仅获取最新的行。
Filter out those rows with the closed status to know the ones that are currently open
过滤掉处于关闭状态的行以了解当前打开的行

#2

So you want to get the most recent row per id and of those, only select those that are open. This is a variation of the common greatest-n-per-group problem.

因此，您希望获得每个id及其中最新的行，只选择那些打开的行。这是常见的最大n组问题的变体。

I would do it this way:

我会这样做：

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';

#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
)

#4

This should get what you want. I wouldn't have a record for open and closing a defect, rather just a single record to track a single defect. But that may not be something you can change easily.

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

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

#5

This query handles multiple opens/closes/opens, and only does one pass through the data (i.e. no self-joins):

此查询处理多个打开/关闭/打开，只有一个通过数据（即没有自连接）：

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';

#1