sql - 使用复合主键查询表

Question

我有一个带有复合主键的表。这是一个交叉引用表，看起来像这样

Table 
ID    Relationship             ID2
1     Spouse                   10
2     Employee                 20
2     Former Employee          20
3     Former Employee          30
4     Child                    40

我正在尝试编写一个 ID2 匹配的查询，您有相同 ID 的员工和前员工

ID    Relationship             ID2
2     Employee                 20
2     Former Employee          20      

感谢任何帮助!!

Answer 1

在 Oracle、SQL Server 和 PostgreSQL 中:

SELECT  *
FROM    (
        SELECT  t.*,
                COUNT(*) OVER (PARTITION BY id, id2) AS cnt
        FROM    mytable t
        WHERE   relationship IN ('Employee', 'Former Employee')
        ) q
WHERE   cnt = 2

跨平台版本:

SELECT  t.*
FROM    (
        SELECT  id, id2
        FROM    mytable
        WHERE   relationship IN ('Employee', 'Former Employee')
        GROUP BY
                id, id2
        HAVING  COUNT(*) = 2
        ) q
JOIN    mytable t
ON      t.id = q.id
        AND t.id2 = q.id2
        AND t.relationship IN ('Employee', 'Former Employee')