子代+父代参考SQL [英] Child + Parent reference SQL
本文介绍了子代+父代参考SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试编写查询以显示项目名称和父项目的名称,但到目前为止还没有采取正确的方法.
I am trying to write a query to display the name of the project and the name of the parent project, but haven't come with the proper way so far.
CREATE TABLE project
(p_id NUMBER(6),
project_name VARCHAR2(30),
client_id NUMBER(6),
mgr_id NUMBER(6),
parent_p_id NUMBER(6),
CONSTRAINT project_pid_pk PRIMARY KEY (p_id),
CONSTRAINT project_client_id_fk FOREIGN KEY (client_id) REFERENCES client(client_id),
CONSTRAINT project_mgr_id_fk FOREIGN KEY (mgr_id) REFERENCES consultant(c_id));
ALTER TABLE project
ADD CONSTRAINT project_parent_pid_fk FOREIGN KEY (parent_p_id) REFERENCES project(p_id);
使用:
SELECT project.p_id, project.project_name, project.parent_p_id
FROM project
WHERE project.parent_p_id IS NOT NULL;
我可以获得大多数信息,但是我不知道如何将project.parent_p_id链接到project.project_name
I can get most of the information, but I don't know how to link the project.parent_p_id to the project.project_name
有人在帮我吗?!
预先感谢:-)
推荐答案
您需要一个自我联接,将PROJECT表这样联接到自身
You need a self join, joining the PROJECT table to itself like this
SELECT p.p_id,
p.project_name,
p.parent_p_id,
pp.project_name as parent_project
FROM project p
inner join project pp
on p.parent_p_id = pp.p_id;
如果要包含没有父项目的项目,则该联接将是LEFT OUTER JOIN.
If you want to include projects which don't have a parent project then the join would be a LEFT OUTER JOIN.
这篇关于子代+父代参考SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文