在 SQL Server 中，选择已完成但不进入已解决状态的记录

Question

我想在 SQL Server 中查找标记为已完成但未经历已解决状态的记录。

下面是以下示例。

tracking_id task_id task status   old_status new_status_id
1195372     140717  ABC  Complete NULL       NULL
1195373     140717  ABC  Complete NULL       New
1195374     140717  ABC  Complete New        Completed
1195380     140718  XYZ  Complete NULL       NULL
1195381     140718  XYZ  Complete NULL       New
1195382     140718  XYZ  Complete New        Completed
1195383     140719  PQR  Complete NULL       NULL
1195384     140719  PQR  Complete NULL       Resolved
1195385     140719  PQR  Complete Resolved   Completed

现在在表中为 task_id 140717 并且140718标记为">已完成">，但它从未进入状态"已解决"。其中task_id 140719也被标记为已完成，但它的状态为已解决。所以我想编写SQL查询来获取task_id 140717和140718的记录。

有关如何

为此编写查询的任何帮助将不胜感激。

Answer 1

这个想法是为了确保任务 ID 具有"已完成"记录，但没有"已解决"记录。

select ...
from the_table t_outer
where 
  status = 'Completed' and
  not exists (
    select 1 from the_table t_inner
    where 
      t_inner.task_id = t_outer.task_id and  -- same task
      'Resolved' in (t_inner.old_status, t_inner.new_status)
  )

(整个状态/new_status/old_status感觉不整洁。

Answer 2

像这样使用NOT EXISTS：

select * 
from yourtable t 
where status = 'Complete' 
and not exists 
     (select top 1 1 
      from yourtable o 
      where o.task_id = t.task_id 
      and o.old_status = 'Resolved') 

Answer 3

如果old_status_id并不总是'resolved' 'completed'则使用not exists()：

select *
from t
where new_status_id = 'completed'
  and not exists (
    select 1
    from t as i
    where i.task_id = t.task_id
      and i.new_status_id = 'resolved'
  )

Answer 4

我想

你只需要task_id而不是行？

SELECT task_id
FROM yourTable
GROUP BY task_id
HAVING COUNT(CASE WHEN old_status = 'Resolved' THEN 1 END) = 0
   AND COUNT(CASE WHEN new_status_id = 'Completed' THEN 1 END) = 1

现在，如果您希望所有记录都涉及那些"未完成"的任务

WITH cte as (
    SELECT task_id
    FROM yourTable
    GROUP BY task_id
    HAVING COUNT(CASE WHEN old_status = 'Resolved' THEN 1 END) = 0
       AND COUNT(CASE WHEN new_status_id = 'Completed' THEN 1 END) = 1
)
SELECT *
FROM yourTable Y
JOIN cte C
  ON Y.task_id = C.task_id

Answer 5

select * from mytable
where new_status_id = 'Completed'
      and old_status <> 'Resolved'

Answer 6

您应该遵循一些有关 select 语句的指南，因为这是非常基本的语法。

只需使用一些 where 子句：

SELECT *
FROM Table
WHERE old_status !+ 'Resolved'
AND new_status_id = 'Completed'