在同一张表中查询父级项目名字

Question

项目表存有项目编号，项目名称，父级项目编号等等
现在想查询出父级项目的编号，但不知怎么做我自己做的查询如下：select tp.ProjectId,tp.ProjectName,tp.ParentPId,/*tp.ProjectName as 'ParentName',*/tp.DepartmentId,td.DepartmentName,convert(varchar(10),tp.BeginTime,20) as BeginTime,
convert(varchar(10),tp.PendTime,20) as PEndTime,tp.Manager,tu.UName,tp.Describe,tp.state,tp.proportion,tp.GroupId,tp.Puid from tProject as tp,tDepartment as td,tUser as tu
where tp.ProjectId='Proj00020000004' and tp.DepartmentId=td.DepartmentId and tp.Manager=tu.UId
被注释掉的就是要显示的父级项目名称表结构如下/**
*
*tProject
*
**/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tProject]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tProject]
GOCREATE TABLE [dbo].[tProject] (
[Projectid] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Parentpid] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Projectname] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Describe] [nvarchar] (400) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Type] [int] NOT NULL ,
[Begintime] [datetime] NOT NULL ,
[Pendtime] [datetime] NOT NULL ,
[Endtime] [datetime] NULL ,
[State] [int] NOT NULL ,
[Mcapacity] [int] NOT NULL ,
[Fcapacity] [int] NOT NULL ,
[DelayFlag] [int] NOT NULL ,
[Manager] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[GROUPID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Puid] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Rmanager] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Sprojectname] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Creater] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Outflag] [int] NOT NULL ,
[Reserve1] [int] NOT NULL ,
[Reserve2] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Reserve3] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Reserve4] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DepartmentID] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Proportion] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
测试数据：insert tProject
(Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manager,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000001',N'Proj00020000001',N'新建项目',N'新建一个根级项目',0,'2010-06-01 00:00:00.000','1900-01-01 
00:00:00.000',NULL,0,0,0,0,N'00005',N'Group0000000001',N'0002000001',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',100)
insert tProject
(Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manager,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000002',N'Proj00020000002',N'JAVA版',N'使用JSF技术开发新版系统',0,'2010-06-01 00:00:00.000','1900-01-01 
00:00:00.000',NULL,0,0,0,0,N'00006',N'Group0000000002',N'0002000002',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',100) insert tProject
(Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manage
r,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000003',N'Proj00020000003',N'项目二',N'再添加一个根级项目',0,'2010-06-01 00:00:00.000','2010-08-01 
00:00:00.000','1900-01-01 
00:00:00.000',0,0,0,0,N'00005',N'Group0000000003',N'0002000003',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',100)
insert tProject
(Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manager,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000004',N'Proj00020000004',N'C语言开发工具设计',N'设计一个基于C语言的开发工具',0,'2010-06-01 
00:00:00.000','2010-10-01 00:00:00.000','1900-01-01 
00:00:00.000',0,0,0,0,N'00007',N'Group0000000004',N'0002000004',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',100)
insert tProject (Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manager,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000005',N'Proj00020000002',N'工作流',N'工作流引擎',0,'2010-06-10 00:00:00.000','2010-08-02 
00:00:00.000',NULL,0,0,0,0,N'00005',N'Group0000000005',N'0002000005',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',50)
insert tProject(Projectid,Parentpid,Projectname,Describe,Type,Begintime,Pendtime,Endtime,State,Mcapacity,Fcapacity,DelayFlag,Manager,GROUPID,Puid,Rmanager,Sprojectname,Creater,Outflag,Reserve1,Reserve2,Reserve4,DepartmentID,Proportion) values
(N'Proj00020000006',N'Proj00020000002',N'Java优化版开心农场的实现',N'利用Java制作优化版开心农场',0,'2010-06-10 
00:00:00.000','2010-08-01 00:00:00.000',NULL,0,0,0,0,N'00005',N'Group0000000006',N'0002000006',N'*',N'*',N'00004',0,0,N'*',N'*',N'0000000002',50)

Answer 1

SELECT tp.ProjectId, tp.ProjectName, tp.ParentPId, 
(select top 1 ProjectName FROM tProject WHERE Parentpid=tp.Rrojectid) as 'ParentName',
tp.DepartmentId, 
       td.DepartmentName, CONVERT(VARCHAR(10), tp.BeginTime, 20) AS BeginTime, CONVERT(VARCHAR(10), tp.PendTime, 20) AS 
       PEndTime, tp.Manager, tu.UName, tp.Describe, tp.state, tp.proportion, tp.GroupId, tp.Puid 
FROM tProject AS tp, tDepartment AS td, tUser AS tu
WHERE tp.ProjectId = 'Proj00020000004'
AND tp.DepartmentId = td.DepartmentId
AND tp.Manager = tu.UIdTRY

Answer 2

你注释掉的这块
/*tp.ProjectName as 'ParentName',*/
tp只要变成父表的名就成了
看不出你的哪个是传说中的父表

Answer 3

--modify:SELECT tp.ProjectId, tp.ProjectName, tp.ParentPId, 
(select top 1 ProjectName FROM tProject WHERE Parentpid=tp.Projectid) as 'ParentName',
tp.DepartmentId, 
       td.DepartmentName, CONVERT(VARCHAR(10), tp.BeginTime, 20) AS BeginTime, CONVERT(VARCHAR(10), tp.PendTime, 20) AS 
       PEndTime, tp.Manager, tu.UName, tp.Describe, tp.state, tp.proportion, tp.GroupId, tp.Puid 
FROM tProject AS tp, tDepartment AS td, tUser AS tu
WHERE tp.ProjectId = 'Proj00020000004'
AND tp.DepartmentId = td.DepartmentId
AND tp.Manager = tu.UId

Answer 4

#3楼 htl258(Tony) 的似乎还有点问题
0004这个是父子都是自身（根级项目）
换成查询0006的子项目，父级项目名字出不来

Answer 5

SELECT tp.ProjectId, tp.ProjectName, tp.ParentPId,     
        (select ProjectName from tProject where ProjectId in(select ParentPId from tProject where ProjectId = 'Proj00020000006')) as 'ParentName',
        tp.DepartmentId, 
       td.DepartmentName, CONVERT(VARCHAR(10), tp.BeginTime, 20) AS BeginTime, CONVERT(VARCHAR(10), tp.PendTime, 20) AS 
       PEndTime, tp.Manager, tu.UName, tp.Describe, tp.state, tp.proportion, tp.GroupId, tp.Puid 
FROM tProject AS tp, tDepartment AS td, tUser AS tu
WHERE tp.ProjectId = 'Proj00020000006'
    AND tp.DepartmentId = td.DepartmentId
    AND tp.Manager = tu.UId这样可以了…………
谢谢指导