成功了,在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')搞定了,哈哈哈哈
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')搞定了,哈哈哈哈
解决方案 »
- ibatis 创建触发器问题
- 使用struts1时令牌重复提交问题
- 这个什么错误啊,我本地可以到服务器上就不行了
- Tomcat如何配置和使用SSL
- 问题请教
- 我在一台机子(resin-3.0.8)中如何配置多个站点或多个虚拟目录?
- IDE工具(比如JBUILDER)里面,输入一个对象之后再打“.”成员操作符号,会自动显示出方法跟成员变量?这个功能是怎么实现的?
- 有没有一个很简单的类似struts的web框架,就是一个很小的应用,不想用struts??记得以前有个帖子说过,怎么找都找不到!!!!
- 在TOMCAT下,我一个Servlet程序修改之后,tomcat运行的还是老的程序!TOMCAT是不是不能自动更新???
- 在jsp或java中有没有查找子串在父串中位置的通用方法?
- 在jsp中如何取当前IE的地址(包括参数)
- 请问哪里有jsp手册下载?
SQL SERVER只能写函数或存储过程实现了,还是oracle功能全,一句就搞定
http://community.csdn.net/Expert/TopicView.asp?id=4094417
http://community.csdn.net/Expert/TopicView.asp?id=4094491
(
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
GO
SET ANSI_NULLS ON
GOCREATE 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_pkOPEN mytable_cursorFETCH NEXT FROM mytable_cursor
INTO @my_pkWHILE @@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
ENDCLOSE mytable_cursor
DEALLOCATE mytable_cursor
return @retstr
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
调用时:select '2'+dbo.fnGetmy_pk('2')
注意:调用时要加上第一个数(这里是'2')
如果你还不理解 可以跟我联系:[email protected]
//函数如下::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