--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID PID Name
------ ------- ----------
002 001 烟台市
004 002 招远市
--*/
from tb a
a b c
101 dk
10101 glk
1010101 yue
102 rtt
10201 ttf
103 gg
. .
. .
. .
我现在想得到结果是,在C 列的值为
a b c
101 dk dk
10101 glk dk_glk
1010101 yue dk_glk_yue
102 rtt rtt
10201 ttf rtt_ttf
103 gg gg
------------------------------------------------
create table tb(a varchar(20),b varchar(20))
insert into tb values('101' , 'dk')
insert into tb values('10101' , 'glk')
insert into tb values('1010101', 'yue')
insert into tb values('102' , 'rtt')
insert into tb values('10201' , 'ttf')
insert into tb values('103' , 'gg')
goselect m1.a , m1.b , c = case when len(m1.a) = 7 then m2.c + '_' + m1.b else m1.c end from
(
select t1.a , t1.b , c = case when len(t1.a) = 5 then t2.c + '_' + t1.b else t1.c end from
(select a , b , c = case when len(a) = 3 then b else '' end from tb) t1,
(select a , b , c = case when len(a) = 3 then b else '' end from tb) t2
where left(t1.a,3) = t2.a
) m1 ,
(
select t1.a , t1.b , c = case when len(t1.a) = 5 then t2.c + '_' + t1.b else t1.c end from
(select a , b , c = case when len(a) = 3 then b else '' end from tb) t1,
(select a , b , c = case when len(a) = 3 then b else '' end from tb) t2
where left(t1.a,3) = t2.a
) m2
where left(m1.a,5) = m2.adrop table tb/*
a b c
-------------------- -------------------- --------------------------------------------------------------
101 dk dk
10101 glk dk_glk
1010101 yue dk_glk_yue
102 rtt rtt
10201 ttf rtt_ttf
103 gg gg(6 行受影响)
*/-----------------------------------------
create function f_tree(@a varchar(20))
returns nvarchar(100)
as
begin
declare @s nvarchar(100)
select @s = isnull(@s + '_' , '') + b from tb where @a like a + '%'
return @s
end
goselect a , b , c = dbo.f_tree(a) from tb
insert into tb values('12.34' , 0 )
insert into tb values('12.34.1' , 2 )
insert into tb values('12.34.2' , 5 )
insert into tb values('12.35' , 0 )
insert into tb values('12.35.1' , 0 )
insert into tb values('12.35.1.1' , 0 )
insert into tb values('12.35.1.1.1', 1 )
insert into tb values('12.35.1.1.2', 2 )
insert into tb values('12.35.1.2' , 1 )
insert into tb values('12.35.2' , 0 )
insert into tb values('12.35.2.1' , 1 )
insert into tb values('12.35.2.2' , 2 )
goselect * , [sum] = (select sum(price) from tb where id like t.id + '%') from tb tdrop table tb/*
id price sum
-------------------- ----------- -----------
12.34 0 7
12.34.1 2 2
12.34.2 5 5
12.35 0 7
12.35.1 0 4
12.35.1.1 0 3
12.35.1.1.1 1 1
12.35.1.1.2 2 2
12.35.1.2 1 1
12.35.2 0 3
12.35.2.1 1 1
12.35.2.2 2 2(所影响的行数为 12 行)
*/
insert into tb values('12.34' , null )
insert into tb values('12.34.1' , 2 )
insert into tb values('12.34.2' , 5 )
insert into tb values('12.35' , null )
insert into tb values('12.35.1' , null )
insert into tb values('12.35.1.1' , null )
insert into tb values('12.35.1.1.1', 1 )
insert into tb values('12.35.1.1.2', 2 )
insert into tb values('12.35.1.2' , 1 )
insert into tb values('12.35.2' , null )
insert into tb values('12.35.2.1' , 1 )
insert into tb values('12.35.2.2' , 2 )
goselect * , [sum] = (select sum(price) from tb where id like t.id + '%') from tb tdrop table tb/*
id price sum
-------------------- ----------- -----------
12.34 0 7
12.34.1 2 2
12.34.2 5 5
12.35 0 7
12.35.1 0 4
12.35.1.1 0 3
12.35.1.1.1 1 1
12.35.1.1.2 2 2
12.35.1.2 1 1
12.35.2 0 3
12.35.2.1 1 1
12.35.2.2 2 2(所影响的行数为 12 行)
*/
有关树的运用和存储过程--建立測試環境
Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--建立函數
Create Function F_GetParent(@departmenid Int)
Returns @Tree Table (departmenid Int, parentid Int)
As
Begin
Insert @Tree Select * From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not Null
Return
End
GO
--測試
Select departmenid From dbo.F_GetParent(8) Order By parentid
GO
--刪除測試環境
Drop Table department
Drop Function F_GetParent
--結果
/*
departmenid
1
3
7
8
*/--建立測試環境
Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--創建存儲過程
Create ProceDure SP_GetParent(@departmenid Int)
As
Begin
Select * Into #Tree From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert #Tree Select A.* From department A Inner Join #Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From #Tree) Where A.parentid Is Not Null
Select departmenid From #Tree Order By parentid
Drop Table #Tree
End
GO
--測試
EXEC SP_GetParent 8
GO
--刪除測試環境
Drop Table department
Drop ProceDure SP_GetParent
--結果
/*
departmenid
1
3
7
8
*/
12.35.1 12.35.2 12.377 12.377.2.1我想查找出xx.xx,也就是第二个点以前的,该怎么写????
declare @s varchar(16)
set @s = '12.35.1'
print left(@s, isnull( charindex('.',@s) + nullif( charindex('.', right(@s,len(@s)-charindex('.',@s)) ) ,0)-1 ,len(@s)))
set @s = '12.35.2'
print left(@s, isnull( charindex('.',@s) + nullif( charindex('.', right(@s,len(@s)-charindex('.',@s)) ) ,0)-1 ,len(@s)))
set @s = '12.377'
print left(@s, isnull( charindex('.',@s) + nullif( charindex('.', right(@s,len(@s)-charindex('.',@s)) ) ,0)-1 ,len(@s)))
set @s = '12.377.2.1'
print left(@s, isnull( charindex('.',@s) + nullif( charindex('.', right(@s,len(@s)-charindex('.',@s)) ) ,0)-1 ,len(@s)))-- 12.35
-- 12.35
-- 12.377
-- 12.377
-------------------- ----------- -----------
12.34 0 7
12.34.1 2 2
12.34.2 5 5
12.35 0 7
12.35.1 0 4
12.35.1.1 0 3
12.35.1.1.1 1 1
12.35.1.1.2 2 2
12.35.1.2 1 1
12.35.2 0 3
12.35.2.1 1 1
12.35.2.2 2 2
12.377 2 2
12.3888 0 5
12.3888.1 3 3
12.3888.2 2 2
现在我是想把他们已经合计好的汇总再进行最后的总汇总,比如这里就是7(12.34)+7(12.35)+2(12.377)+5(12.3888)进行汇总,tim_spac兄的是把所有的xx.xx都提取了。请教大侠们该怎么实现这最后的总汇总啊???
如果一个字符串不包含"."(如"tab"),则解析出objectname(tab);server,db,owner信息均为NULL.
如果一个字符串包含"."(如"dbo.tab"),则解析出objectname(tab),owner(dbo);server,db信息均为NULL.
...
若你的需求是找到所有第二级代码的记录,可以借用一下:
select *
from table
where parsename(id,2) is not null and parsename(id,3) is null