--下面是用递归函数实现求数列:1,1,2,3,5,8,13,21....
create function f_1(@i int)
returns int
as
begin
if @i=1 or @i=2
return 1
return dbo.f_1(@i-1)+dbo.f_1(@i-2)
end
go
select dbo.f_1(8)
create function f_1(@i int)
returns int
as
begin
if @i=1 or @i=2
return 1
return dbo.f_1(@i-1)+dbo.f_1(@i-2)
end
go
select dbo.f_1(8)
(
sIDvarchar(3),
sNamevarchar(20),
ParentIDvarchar(3) )Create Table SaleInfo
(
sIDvarchar(3),
dQuantitydecimal(18,2)
)insert into sType
select'001', '长虹', nullunion
select '002', '29寸', '001'union
select'003', '等离子', '002'union
select'004', '黑色', '003'union
select '005', '红色', '003'union
select '006', '背投', '002'union
select '007', '红色', '006'union
select '008', '黑色', '006'Insert Into SaleInfo
select '004', 10union
select '005', 12unionselect '007',1union
select '008', 2
go--获取数量
create function f_num(@sID varchar(10))
returns int
as
begin
declare @tb table
(
sID varchar(10),
ParentID varchar(10)
) insert @tb
select sID,ParentID from sType where sID=@sID while @@rowcount>0
begin
insert @tb
select A.sID,A.ParentID
from sType A
join @tb B on A.ParentID=B.sID
where A.sID not in(select sID from @tb)
end declare @num int
set @num=0
select @num=@num+isnull(B.dQuantity,0)
from @tb A
join SaleInfo B on A.sID=B.sID return @num
end
go--递归获取级别
create function f_level(@sID varchar(10),@level int)
returns int
as
begin
if (select ParentID from sType where sID=@sID) is null
return @level return dbo.f_level((select ParentID from sType where sID=@sID),isnull(@level,0)+1)
end
go--查询
select (case when grouping(sName)=1 then '小计' else sID end) as sID
,isnull(sName,'') as sName
,dbo.f_num(sID) as quantity
from sType
group by sID,sName
with rollup
having grouping(sID)<>1
order by dbo.f_level(sID,0) desc--删除测试环境
drop function f_level
drop function f_num
drop table sType,SaleInfo--结果
/*
sID sName quantity
---- -------------------- -----------
004 黑色 10
小计 10
005 红色 12
小计 12
007 红色 1
小计 1
008 黑色 2
小计 2
006 背投 3
小计 3
003 等离子 22
小计 22
002 29寸 25
小计 25
001 长虹 25
小计 25(17 row(s) affected)*/
(
id int,
deptid varchar(10),
deptname varchar(20),
pid varchar(20),
deptions varchar(100)
)
insert A
select 1,'A001','公司总部',null,'公司总部' union
select 2,'A0011','总经办','A001','公司总部' union
select 3,'A00111','总经办分部1','A0011','总经办分部1' union
select 4,'A00112','总经办分部2','A0011','总经办分部2' union
select 5,'A00113','总经办分部3','A0011','人力资源部' union
select 5,'A00121','人事科','A00112','人事科'
go/*
根据部门id,查找所有子部门
*/
create function f_nodes(@depid varchar(10))
returns @tb table
(
dlevel int, --深度
depid varchar(20), --部门id
depName varchar(20) --部门名称
)
as
begin
declare @level int
set @level=1
insert @tb
select @level,deptid,deptname from A where deptid=@depid while @@rowcount>0
begin
set @level=@level+1
insert @tb
select @level,A.deptid,A.deptName
from A
join @tb B on A.pid=B.depid
where A.deptid not in(select depid from @tb)
end
return
end
go--查询
--select * from f_nodes('A001')
select replicate(' ',dlevel*2)+depName from f_nodes('A001')--删除测试环境
drop function d_nodes
drop table A--结果
/*
-------------------------
公司总部
总经办
总经办分部1
总经办分部2
总经办分部3
人事科(所影响的行数为 6 行)
*/
create table BOM(ID INT,PID INT)
insert into BOM select 1,0
insert into BOM select 2,1
insert into BOM select 3,2
insert into BOM select 4,3
insert into BOM select 5,4
insert into BOM select 6,0
insert into BOM select 7,6
--创建用户定义函数
create function f_getChild(@ID INT)
returns @t table(ID INT,PID,Level INT)
as
begin
declare @i int
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
--执行查询
select * from dbo.f_getChild(1)
--输出结果
/*
ID PID Level
-- --- ------
2 1 1
3 2 2
4 3 3
5 4 4
*/
-----------------------------------------------------
--生成测试数据
create table t1(id int,name char(1),parentid int)
insert into t1 select 1,'a',0
insert into t1 select 2,'b',0
insert into t1 select 3,'c',1
insert into t1 select 4,'d',1
insert into t1 select 5,'e',2
insert into t1 select 6,'f',2
insert into t1 select 7,'g',3
insert into t1 select 8,'h',3create table t2(id int,num int)
insert into t2 select 7,10
insert into t2 select 8,11
insert into t2 select 6,10
insert into t2 select 5,11
insert into t2 select 4,10
go--创建用户定义函数
create function f_getNum(@id int)
returns varchar(4000)
as
begin
declare @ret varchar(4000),@pid int
set @ret = right('0000'+rtrim(@id),4)
while exists(select 1 from t1 where id=@id and parentid!=0)
begin
select @pid=parentid from t1 where id=@id and parentid!=0
set @id = @pid
set @ret = right('0000'+rtrim(@id),4)+@ret
end
return @ret
end
go
--执行查询
select
a.id,
a.name,
num = sum(b.num)
from
t1 a
left join
t2 b
on
dbo.f_getNum(b.id) like dbo.f_getNum(a.id)+'%'
group by
a.id,a.name
order by
dbo.f_getNum(a.id)
--输出结果:
id name num
---- ------ -----
1 a 31
3 c 21
7 g 10
8 h 11
4 d 10
2 b 21
5 e 10
6 f 11