sql - 简化 Oracle 中的现有查询

Question

仅当存在具有 key_id ECRU 和 MTR 的记录时，如何简化以下查询以返回 field2。

SELECT
    z.field2
FROM
    mytable z
WHERE
        z.key_id = 'ECRU'
    AND EXISTS (
        SELECT
            1
        FROM
            mytable
        WHERE
                key_id = 'MTR'
            AND field2 = z.field2
    )

标签： sqloraclecountsubqueryhaving-clause

Answer 1

你可以使用group byand having：

select field2
from mytable
where key_id in ('ECRU', 'MTR')
group by field2
having count(*) = 2

这假定没有重复(field2, key_id)。否则，您需要稍微更改该having子句：

having count(distinct key_id) = 2