oracle查询以比较表中具有相同id的所有行 [英] oracle query to compare all the rows having same id in a table

问题描述

需要一个SQL查询，该查询将生成状态相同的ID完整的记录.例如，mytable是具有各种记录的表名.我们需要找到所有状态相同的ID的ID.

Need an sql query which results those records whose status is complete for a same id. For example, mytable is the table name which has various records. We need to find all those ids whose status is complete for that same id

id status
12 complete
12  required
12  active
13  complete
13  complete
14  complete
14  required

因此，查询应显示 13完成

So, query should display 13 complete

推荐答案

这将找到id，其中所有行的status为complete:

This will find the ids where all its rows have a status of complete:

SELECT id
FROM   mytable
GROUP BY id
HAVING COUNT(*) = COUNT( CASE status WHEN 'complete' THEN 1 END )

这篇关于oracle查询以比较表中具有相同id的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！