不太清楚你的意思 select my_pk from table where my_pk=3 or super_pk=3
需要写个函数来完成,一条SQL是不可以的。
--建立测试环境 Create Table TEST (my_pk Int, super_pk Int, name Nvarchar(100)) --插入数据 Insert TEST Values(1, 0, N'中国') Insert TEST Values(2, 1, N'河北') Insert TEST Values(3, 1, N'山东') Insert TEST Values(4, 1, N'江苏') Insert TEST Values(5, 2, N'承德') Insert TEST Values(6, 2, N'唐山') Insert TEST Values(7, 3, N'德州') Insert TEST Values(8, 3, N'青岛') Insert TEST Values(9, 8, N'青岛崂山区') Insert TEST Values(10, 5, N'承德双兰区') GO --建立函数 Create Function Getmy_pk(@my_pk Int) Returns Varchar(1000) As Begin Declare @S Varchar(1000) Declare @T Table(my_pk Int,super_pk Int) Insert Into @T Select my_pk,super_pk From TEST Where my_pk=@my_pk While @@rowcount>0 Insert Into @T Select A.my_pk,A.super_pk From TEST A Inner Join @T B On A.super_pk=B.my_pk Where A.my_pk Not In(Select my_pk from @T) Set @S='' Select @S=@S+','+Rtrim(my_pk) from @T Order by my_pk Return(Stuff(@S,1,1,'')) End GO --测试 Select dbo.Getmy_pk(2) As super_pk Select dbo.Getmy_pk(3) As super_pk GO --删除测试环境 Drop Table TEST Drop Function Getmy_pk --结果 /* super_pk 2,5,6,10super_pk 3,7,8,9 */
select my_pk from table where my_pk=3 or super_pk=3
Create Table TEST
(my_pk Int,
super_pk Int,
name Nvarchar(100))
--插入数据
Insert TEST Values(1, 0, N'中国')
Insert TEST Values(2, 1, N'河北')
Insert TEST Values(3, 1, N'山东')
Insert TEST Values(4, 1, N'江苏')
Insert TEST Values(5, 2, N'承德')
Insert TEST Values(6, 2, N'唐山')
Insert TEST Values(7, 3, N'德州')
Insert TEST Values(8, 3, N'青岛')
Insert TEST Values(9, 8, N'青岛崂山区')
Insert TEST Values(10, 5, N'承德双兰区')
GO
--建立函数
Create Function Getmy_pk(@my_pk Int)
Returns Varchar(1000)
As
Begin
Declare @S Varchar(1000)
Declare @T Table(my_pk Int,super_pk Int)
Insert Into @T Select my_pk,super_pk From TEST Where my_pk=@my_pk
While @@rowcount>0
Insert Into @T Select A.my_pk,A.super_pk From TEST A
Inner Join @T B On A.super_pk=B.my_pk
Where A.my_pk Not In(Select my_pk from @T)
Set @S=''
Select @S=@S+','+Rtrim(my_pk) from @T Order by my_pk
Return(Stuff(@S,1,1,''))
End
GO
--测试
Select dbo.Getmy_pk(2) As super_pk
Select dbo.Getmy_pk(3) As super_pk
GO
--删除测试环境
Drop Table TEST
Drop Function Getmy_pk
--结果
/*
super_pk
2,5,6,10super_pk
3,7,8,9
*/