求一个SQL语句？SQLServer2000
0
悬赏园豆：5
[已解决问题]
解决于 2009-08-31 23:02
表A 中字段如下：
ID
Name
parentID
数据为
1 aa 0
2 bb 0
3 cc 2
现在求ID 为3的记录,并把其父ID 的Name也显示出来？出来的结果应该是 3 cc bb
能否用一个SQL语句实现？？
最佳答案
0
/****** Object: Table [dbo].[TestTable] Script Date: 2009-8-31 22:51:33 ******/
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [A] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[ParentID] [int] NULL ,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
insert into A ([Name],ParentID)
select 'aa',0
union all
select 'bb',0
union all
select 'cc',2
select [ID],[Name],(select top 1 [name] from A where id= (select top 1 parentid from A where [id]=3)) as ParentName
from A
where [id]=3
--3 cc bb
收获园豆：5
感谢您的帮助，谢谢
