create function f_getPath(@GROUP_ID varchar(200))
returns varchar(8000)
as
begin
declare @ret varchar(8000),@PARENT_GROUP_ID varchar(200)
set @ret = ''
select @ret=@ret+ITEM_NAME,@PARENT_GROUP_ID=PARENT_GROUP_ID
from RFID_GROUP_SETTING where GROUP_ID=@GROUP_ID
while @@rowcount<>0
begin
set @GROUP_ID=@PARENT_GROUP_ID
select @ret=@ret+ITEM_NAME,@PARENT_GROUP_ID=PARENT_GROUP_ID
from RFID_GROUP_SETTING where GROUP_ID=@GROUP_ID
end
return @ret
end
资料表CREATE TABLE [dbo].[SETTING](
[GROUP_ID] [varchar](50) NOT NULL,
[PARENT_ID] [varchar](50) NULL,
[ITEM_NAME] [nvarchar](50) NULL,
CONSTRAINT [PK_SETTING] PRIMARY KEY CLUSTERED
(
[GROUP_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]数据
G1001 * 总部
G1002 G1001 A厂
G1029 G1002 B部门
G1033 G1029 C科室希望查询出来结果
如查询 条件 G1033
查询结果
G1033 G1029 C科室-B部门-A厂-总部 或者 总部-A厂-B部门-C科室
如查询 条件 G1029
查询结果
G1029 G1001 B部门-A厂-总部 或者 总部-A厂-B部门select *,path=dbo.f_getPath(ID) from SETTING as T1
where GROUP_ID='G1033'
我的函數有点问题,谢谢大家!
returns varchar(8000)
as
begin
declare @ret varchar(8000),@PARENT_GROUP_ID varchar(200)
set @ret = ''
select @ret=@ret+ITEM_NAME,@PARENT_GROUP_ID=PARENT_GROUP_ID
from RFID_GROUP_SETTING where GROUP_ID=@GROUP_ID
while @@rowcount<>0
begin
set @GROUP_ID=@PARENT_GROUP_ID
select @ret=@ret+ITEM_NAME,@PARENT_GROUP_ID=PARENT_GROUP_ID
from RFID_GROUP_SETTING where GROUP_ID=@GROUP_ID
end
return @ret
end
资料表CREATE TABLE [dbo].[SETTING](
[GROUP_ID] [varchar](50) NOT NULL,
[PARENT_ID] [varchar](50) NULL,
[ITEM_NAME] [nvarchar](50) NULL,
CONSTRAINT [PK_SETTING] PRIMARY KEY CLUSTERED
(
[GROUP_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]数据
G1001 * 总部
G1002 G1001 A厂
G1029 G1002 B部门
G1033 G1029 C科室希望查询出来结果
如查询 条件 G1033
查询结果
G1033 G1029 C科室-B部门-A厂-总部 或者 总部-A厂-B部门-C科室
如查询 条件 G1029
查询结果
G1029 G1001 B部门-A厂-总部 或者 总部-A厂-B部门select *,path=dbo.f_getPath(ID) from SETTING as T1
where GROUP_ID='G1033'
我的函數有点问题,谢谢大家!
解决方案 »
- 连接数据库失败 急!
- 多表链接查询中可以使用外链接吗?求SQL语句
- 從備份還原過來的數據變成只讀了﹐刪也刪不掉﹐郁悶﹗
- sql server 查询分析器消息栏里怎么去掉“(5 行受影响)”
- 表table字段a,里面是以数字加逗号形式,如1,2 另一个是查询后取得的ID集合, 如何写取得表table中所有符合字段a中数字在查询集合有的记录?
- vfp中,怎么给软件限定使用次数和时间`~~~给个方案,或例子吧~~
- 如何用vbscript将dbase3中的记忆型字段写入SQL Server中
- 不是通常的行变列,应该说成是行连接,请高手来解!
- 关于oracle 8.05 on NT 只能在NT下运行吗
- CAESE 语句的问题?,大送分!!!帮忙改正
- and 与 “=、> <...”运算顺序
- 没有全文检索服务
select *,path=dbo.f_getPath(ID) from SETTING as T1
where GROUP_ID='G1033'dbo.f_getPath(ID)忘了传递参数,来人啦!我好结贴!
create table [tb] (id int,name varchar(1),pid int)
insert into [tb]
select 1,'A',0 union all
select 2,'B',1 union all
select 3,'D',1 union all
select 4,'C',2 union all
select 5,'D',2 union all
select 6,'A',4 union all
select 7,'E',5 union all
select 8,'F',5
GO
;with cte
as
(
select *,[path]=cast([name]+'->' as varchar(100)) ,[level] = 1 from tb where pid = 0
union all
select a.*, cast(c.[path]+a.[name]+'->' as varchar(100)),[level]+1 from cte c ,tb a where a.pid = c.id
)
select
*
from cte
where len([path]) > 6 and right([path],3) = left([path],3)
/*
id name pid path level
----------- ---- ----------- -------------- -----
6 A 4 A->B->C->A-> 4(1 行受影响)
*/------------------------------------------------------------------------
-- Author : happyflystone
-- Date : 2010-04-06
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
Go
CREATE TABLE tb([cid] NVARCHAR(1),[pid] NVARCHAR(1))
Go
INSERT INTO tb
SELECT 'A','B' UNION ALL
SELECT 'A','D' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'B','D' UNION ALL
SELECT 'C','A' UNION ALL
SELECT 'D','E' UNION ALL
SELECT 'D','F'
GO
--Start
;with cte
as
(
select *,[path]=cast([cid]+'->' as varchar(100)) ,[level] = 1
from (select distinct cid,cast('' as nvarchar(1)) as pid from tb union select distinct pid ,'' from tb) b
union all
select a.*,cast(a.[cid]+'->'+c.[path] as varchar(100)),[level]+1
from cte c ,tb a
where a.pid = c.cid and charindex(a.[cid]+'->',c.[path])=0
)
select
[path]+cid+'->'
from cte
where exists(select 2 from tb where cid+'->' = right([path],3) and pid+'->' = left([path],3))-- = left([path],3)
--Result:
/*
--------------
A->B->C->A->
C->A->B->C->
B->C->A->B->(3 行受影响)*/
--End
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/06/5456223.aspx
就是
select *,path=dbo.f_getPath(ID) from SETTING as T1
where GROUP_ID='G1033'
没有参数,需全查怎么办
dbo.f_getPath(id)处理传入id所在的完整路径.