使用复合主键查询表

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

Answer 2

select * from table where ID2 in (
   select ID2 from table where count(ID2) > 1) t
order by ID2