sql - 如何在分层查询中获取不同的元素列表？

Question

我有一个数据库表，其中的人员由姓名、工作和城市标识。我有第二个表，其中包含每个城市公司中每个职位的层次结构表示。

假设我在 people 表中有 3 个人:

[name(PK),title,city]
Jim, Salesman, Houston
Jane, Associate Marketer, Chicago
Bill, Cashier, New York

我在工作表中有数千个工作类型/位置组合，下面是其中的一个示例。您可以看到层次关系，因为 parent_title 是 title 的外键:

[title,city,pay,parent_title]
Salesman, Houston, $50000, CEO
Cashier, Houston, $25000
CEO, USA, $1000000
Associate Marketer, Chicago, $75000
Senior Marketer, Chicago, $125000

.....

我遇到的问题是我的 Person 表是一个复合键，所以我不知道如何构建查询的 start with 部分，以便它以每个我指定城市的三份工作。

我可以执行三个单独的查询来获得我想要的结果，但这并不能很好地扩展。例如:

select * from jobs
start with city = (select city from people where name = 'Bill') and title = (select title from people where name = 'Bill')
connect by prior parent_title = title
UNION
select * from jobs
start with city = (select city from people where name = 'Jim') and title = (select title from people where name = 'Jim')
connect by prior parent_title = title
UNION
select * from jobs
start with city = (select city from people where name = 'Jane') and title = (select title from people where name = 'Jane')
connect by prior parent_title = title

我还能如何获得我指定的三个人以上的所有工作的不同列表(或者如果不可能，我可以用不同的包装它)？

Answer 1

请试试这个。我没有测试过这个。

SELECT  distinct *
FROM    jobs
START   WITH ( city, title ) IN 
     ( SELECT city, title
       FROM   people
       WHERE  name IN ( 'Bill', 'Jim', 'Jane' )
     )
CONNECT BY PRIOR parent_title = title;