求一个SQL语句(SQL SERVER)

[e_zzz]

表名 mytable
=============================================
字段 类型 长度 说明

my_pk smallint 2 索引
super_pk smallint 2 父索引
name nvarchar 50 地名
=============================================


=============================================
具体内容

my_pk super_pk name
1 0 中国
2 1 河北
3 1 山东
4 1 江苏
5 2 承德
6 2 唐山
7 3 德州
8 3 青岛
9 8 青岛崂山区
10 5 承德双兰区
=============================================

求一个SQL语句(SQL SERVER)

根据索引取出下面包含的所有记录的my_PK号

比如查询条件
my_pk=3 应该取得结果是 3,7,8,9

比如查询条件
my_pk=2 应该取得结果是 2,5,6,10

这个语句怎么写？

[ronakitty]

i="查询条件"
sql="select my_pk from [table_name] where my_pk=i and super_pk=i and super_pk=(select my_pk from [table_name] where super_pk=i)"

[e_zzz]

不行呀 大佬

sql server 提示 子查询语句返回得值多于一个。当子查询跟随在=,!=,<,<=,>,>=之后，或子查询用作表达式时，这种情况是不允许的。

[ronakitty]

那就把“=”改成“in”

i="查询条件"
sql="select my_pk from [table_name] where my_pk=i and super_pk=i and super_pk in (select my_pk from [table_name] where super_pk=i)"

[kevin_gao]

sql里面写递归，不会，学习！

[kevin_gao]

使用存储过程或许可以？好像蛮难搞的。

实在不行就asp写递归吧，呵呵。

[baikaishui_0825]

如果只取两层的话，这样试试
select * from mytable where my_pk=3 or super_pk=3

[e_zzz]

成功了，在sql server里面写一个函数

CREATE FUNCTION fnGetmy_pk (@sValues NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
SELECT my_pk,super_pk,name
FROM mytable
WHERE (super_pk = @sValues) or (my_pk = @sValues)
)
GO

调用函数
SELECT * FROM dbo.fnGetmy_pk('1')

搞定了,哈哈哈哈

[e_zzz]

谢谢 (毓苫)朋友的热心帮助

但最好是写成 SQL SERVER 函数

[e_zzz]

数据最多有6层。

[尚和空四]

按函数思路写下去，可以支持n层的

[hto112233]

我知道，不告诉你~：（

[skyboy0720]

我知道,我告诉你~

-----------------树型例子-----------------

create table t1
(
id int identity(1,1),
code int,
parentcode int
)
go
insert t1 select 1,null
union all select 2,1
union all select 3,1
union all select 4,2

--drop table t1


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO

/*--树形数据处理

查询指定id的所有子

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.code=b.code
--*/
create function f_cid(
@id int
)returns @re table(code int,[level] int)
as
begin
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.code,@l
from [t1] a,@re b
where a.parentcode=b.code and b.[level]=@l-1
end
return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [t1] a,f_cid(2)b where a.code=b.code
go

[lwj180]

select * from mytable where my_pk=2 or super_pk = 2

[licry01]

//修改好了,你可以直接运行一下
//函数如下::

CREATE FUNCTION dbo.FUNC_GET_SUBAREA(@area_id int,@level_num int =null)
RETURNS @rt_table table(
area_id int,
area_name varchar (50)
)
AS
BEGIN
declare @tep_name varchar(50),
@tep_id int,
@sub_count int

declare c1 cursor for
(
select my_pk,[name] from mytable where super_pk = @area_id
)
if @level_num=0
begin
insert into @rt_table(area_id,area_name)
select my_pk,[name] from mytable where my_pk = @area_id
end

open c1

fetch c1 into @tep_id,@tep_name
select @sub_count = 0
while @@fetch_status>=0
begin
select @sub_count=count(*) from mytable where super_pk = @tep_id

insert into @rt_table(area_id,area_name) values(@tep_id,@tep_name)

if @sub_count>0
begin
insert into @rt_table(area_id,area_name)
select area_id,area_name from dbo.FUNC_GET_SUBAREA(@tep_id,1)
end
FETCH NEXT FROM c1 INTO @tep_id,@tep_name
end



close c1
deallocate c1

return
END


//使用: select * from dbo.FUNC_GET_SUBAREA(2,0) order by 1
//使用: select * from dbo.FUNC_GET_SUBAREA(1,0) order by 1

[tempmen]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE function fnGetmy_pk(@super_pk nvarchar(50))
returns nvarchar(50)
begin
Declare @my_pk int,@retstr nvarchar(50),@substr nvarchar(50)

set @retstr=''
DECLARE mytable_cursor CURSOR FOR

SELECT my_pk FROM mytable where super_pk=@super_pk

OPEN mytable_cursor

FETCH NEXT FROM mytable_cursor
INTO @my_pk

WHILE @@FETCH_STATUS = 0
BEGIN
if @retstr=''
set @retstr=@retstr+convert(nvarchar(10),@my_pk)
else
set @retstr=@retstr+','+convert(nvarchar(10),@my_pk)


set @substr=dbo.fnGetmy_pk(convert(nvarchar(10),@my_pk))

if @substr<>'' set @retstr=@retstr+','+@substr


FETCH NEXT FROM mytable_cursor
INTO @my_pk

END

CLOSE mytable_cursor
DEALLOCATE mytable_cursor
return @retstr
end



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


调用时：select '2'+dbo.fnGetmy_pk('2')
注意：调用时要加上第一个数（这里是'2'）
如果你还不理解 可以跟我联系：[e-mail]

[licry01]

//修改好了,你可以直接运行一下
//函数如下::

CREATE FUNCTION dbo.FUNC_GET_SUBAREA(@area_id int,@level_num int =null)
RETURNS @rt_table table(
area_id int,
area_name varchar (50)
)
AS
BEGIN
declare @tep_name varchar(50),
@tep_id int,
@sub_count int

declare c1 cursor for
(
select my_pk,[name] from mytable where super_pk = @area_id
)
if @level_num=0
begin
insert into @rt_table(area_id,area_name)
select my_pk,[name] from mytable where my_pk = @area_id
end

open c1

fetch c1 into @tep_id,@tep_name
select @sub_count = 0
while @@fetch_status>=0
begin
select @sub_count=count(*) from mytable where super_pk = @tep_id

insert into @rt_table(area_id,area_name) values(@tep_id,@tep_name)

if @sub_count>0
begin
insert into @rt_table(area_id,area_name)
select area_id,area_name from dbo.FUNC_GET_SUBAREA(@tep_id,1)
end
FETCH NEXT FROM c1 INTO @tep_id,@tep_name
end



close c1
deallocate c1

return
END


//使用: select * from dbo.FUNC_GET_SUBAREA(2,0) order by 1
//使用: select * from dbo.FUNC_GET_SUBAREA(1,0) order by 1

[子陌红尘]

试试：
----------------------------------------------------------------
create function f_getpk(@my_pk int)
returns varchar(8000)
as
begin
declare @ret varchar(8000),@var1 varchar(1000),@var2 varchar(1000)
select
@ret = rtrim(my_pk),
@var1 = rtrim(my_pk),
@var2 = ''
from
mytable
where
my_pk = @my_pk

while (@var1 != '')
begin
while charindex(',',@var1)>0
begin
select
@ret = @ret + ',' + rtrim(my_pk),
@var2 = @var2 + ',' + rtrim(my_pk)
from
mytable
where
super_pk = left(@var1,charindex(',',@var1)-1)

set @var1 = stuff(@var1,1,charindex(',',@var1),'')
end

select
@ret = @ret + ',' + rtrim(my_pk),
@var2 = @var2 + ',' + rtrim(my_pk)
from
mytable
where
super_pk = @var1

set @var1 = stuff(@var2,1,1,'')
set @var2 = ''
end

return @ret
end

[e_zzz]

还是不对！！！

这样只能取到两层

55555555555555


高手帮忙

[ronakitty]

id="查询条件"

set rs=server.createobject("adodb.recordset")
sql="select my_pk,super_pk from [table_name] where my_pk=id or super_pk=id order by my_pk"
rs.open sql,conn,1,1
if not rs.eof then
if rs("super_pk")=0 then
response.write(rs("my_pk")&",")
else
response.write(rs("super_pk")&",")
end if
end if

set rs1=server.createobject("adodb.recordset")
sql1="select my_pk,super_pk from [table_name] where my_pk<>id and super_pk<>id order by my_pk"
rs1.open sql1,conn,1,1
if not rs1.eof then
for i=1 to rs1.recordcount
j=1
pk=rs1("super_pk")
while j=1
set rs2=server.createobject("adodb.recordset")
sql2="select my_pk,super_pk from [table_name] where my_pk="& pk &" and super_pk=id order by my_pk"
rs2.open sql2,conn,1,1
if not rs2.eof then
response.write(rs1("my_pk")&",")
j=0
end if
pk=rs2("super_pk")
wend
rs1.movenext
exit for
end if

[kevin_gao]

是不是可以了？可以了我就收藏了！ ：）