create table test (id int primary key,name char(6),sj_id int REFERENCES test(id))
--测试数据
insert test values(1,'A',1)
insert test values(2,'B',1)
insert test values(10,'C',2)
insert test values(11,'D',10)
insert test values(12,'E',11)
insert test values(13,'F',10)
insert test values(14,'G',12)--实现
declare @id int
declare @cs int
set @id = 10
Create table #tmp_test_tree(id int not null,CS int not null)
Select @cs = 1
Insert Into #tmp_test_tree(id,CS) Values(@id,@cs)
While (@@ROWCOUNT >= 1)
Begin
select @cs = @cs + 1
Insert #tmp_test_tree(id,CS)
Select a.id,@cs From test a,#tmp_test_tree b
Where a.sj_id = b.id and b.CS = @cs - 1 and a.id <> a.sj_id
End
select a.id,b.name from #tmp_test_tree a join test b on a.id = b.id where cs > 1drop table #tmp_test_tree,test
id name
----------- ------
11 D
13 F
12 E
14 G (所影响的行数为 4 行)------------------------------------------------------------------------------------
部门表:
Dep_ID Dep_Name Dep_Father Dep_Level
1 总部 0 1
2 部门1 1 2
3 部门1-1 2 3
4 部门2 1 2
5 部门2-1 4 3
6 部门2-2 4 3
7 部门2-1-1 5 4
业绩表
Dep_ID Dep_Gross
1 2500
3 4500
4 200
5 600
6 360
7 650 求部门2的总业绩(包括:部门2、部门2-1、部门2-2、部门2-1-1) declare @部门表 table(Dep_ID int,Dep_Name varchar(10),Dep_Father int,Dep_Level int)
insert into @部门表
select 1,'总部',0,1
union all select 2,'部门1',1,2
union all select 3,'部门1-1',2,3
union all select 4,'部门2',1,2
union all select 5,'部门2-1',4,3
union all select 6,'部门2-2',4,3
union all select 7,'部门2-1-1',5,4declare @业绩表 table(Dep_ID int,Dep_Gross int)
insert into @业绩表
select 1,2500
union all select 3,4500
union all select 4,200
union all select 5,600
union all select 6,360
union all select 7,650 --统计处理
declare @t table(Dep_ID int,idm varchar(8000))
declare @level int
set @level=1
insert into @t select Dep_ID,','+cast(Dep_ID as varchar)+','
from @部门表 where Dep_Level=1
while @@rowcount>0
begin
set @level=@level+1
insert into @t select a.Dep_ID,b.idm+cast(a.Dep_ID as varchar)+','
from @部门表 a join @t b on a.Dep_Father=b.Dep_ID
where a.Dep_Level=@level
end--统计业绩
select a.Dep_ID,a.Dep_Name,业绩=sum(b.Dep_Gross)
from (
select a.Dep_ID,a.Dep_Name,idm='%'+b.idm +'%'
from @部门表 a join @t b on a.Dep_ID=b.Dep_ID
)a join(
select a.*,b.idm
from @业绩表 a join @t b on a.Dep_ID=b.Dep_ID
) b on b.idm like a.idm
group by a.Dep_ID,a.Dep_Name/*--测试结果Dep_ID Dep_Name 业绩
----------- ---------- -----------
1 总部 8810
2 部门1 4500
3 部门1-1 4500
4 部门2 1810
5 部门2-1 1250
6 部门2-2 360
7 部门2-1-1 650(所影响的行数为 7 行)
--*/
--测试数据
insert test values(1,'A',1)
insert test values(2,'B',1)
insert test values(10,'C',2)
insert test values(11,'D',10)
insert test values(12,'E',11)
insert test values(13,'F',10)
insert test values(14,'G',12)--实现
declare @id int
declare @cs int
set @id = 10
Create table #tmp_test_tree(id int not null,CS int not null)
Select @cs = 1
Insert Into #tmp_test_tree(id,CS) Values(@id,@cs)
While (@@ROWCOUNT >= 1)
Begin
select @cs = @cs + 1
Insert #tmp_test_tree(id,CS)
Select a.id,@cs From test a,#tmp_test_tree b
Where a.sj_id = b.id and b.CS = @cs - 1 and a.id <> a.sj_id
End
select a.id,b.name from #tmp_test_tree a join test b on a.id = b.id where cs > 1drop table #tmp_test_tree,test
id name
----------- ------
11 D
13 F
12 E
14 G (所影响的行数为 4 行)------------------------------------------------------------------------------------
部门表:
Dep_ID Dep_Name Dep_Father Dep_Level
1 总部 0 1
2 部门1 1 2
3 部门1-1 2 3
4 部门2 1 2
5 部门2-1 4 3
6 部门2-2 4 3
7 部门2-1-1 5 4
业绩表
Dep_ID Dep_Gross
1 2500
3 4500
4 200
5 600
6 360
7 650 求部门2的总业绩(包括:部门2、部门2-1、部门2-2、部门2-1-1) declare @部门表 table(Dep_ID int,Dep_Name varchar(10),Dep_Father int,Dep_Level int)
insert into @部门表
select 1,'总部',0,1
union all select 2,'部门1',1,2
union all select 3,'部门1-1',2,3
union all select 4,'部门2',1,2
union all select 5,'部门2-1',4,3
union all select 6,'部门2-2',4,3
union all select 7,'部门2-1-1',5,4declare @业绩表 table(Dep_ID int,Dep_Gross int)
insert into @业绩表
select 1,2500
union all select 3,4500
union all select 4,200
union all select 5,600
union all select 6,360
union all select 7,650 --统计处理
declare @t table(Dep_ID int,idm varchar(8000))
declare @level int
set @level=1
insert into @t select Dep_ID,','+cast(Dep_ID as varchar)+','
from @部门表 where Dep_Level=1
while @@rowcount>0
begin
set @level=@level+1
insert into @t select a.Dep_ID,b.idm+cast(a.Dep_ID as varchar)+','
from @部门表 a join @t b on a.Dep_Father=b.Dep_ID
where a.Dep_Level=@level
end--统计业绩
select a.Dep_ID,a.Dep_Name,业绩=sum(b.Dep_Gross)
from (
select a.Dep_ID,a.Dep_Name,idm='%'+b.idm +'%'
from @部门表 a join @t b on a.Dep_ID=b.Dep_ID
)a join(
select a.*,b.idm
from @业绩表 a join @t b on a.Dep_ID=b.Dep_ID
) b on b.idm like a.idm
group by a.Dep_ID,a.Dep_Name/*--测试结果Dep_ID Dep_Name 业绩
----------- ---------- -----------
1 总部 8810
2 部门1 4500
3 部门1-1 4500
4 部门2 1810
5 部门2-1 1250
6 部门2-2 360
7 部门2-1-1 650(所影响的行数为 7 行)
--*/
解决方案 »
- 求一条SQL 的select语句
- 用DTS将SQL中的数据导出到EXCEL中(已设置好格式),如何先删除EXCEL中的数据。
- 急求SQL~
- 同一张表中两个字段的关联更新 --->>
- Microsoft OLE DB Provider for SQL Server 错误 '80040e14'
- 急!!!!!请问哪里有SQLSERVER7.0的SQL Server JDBC Drive下载,
- 怎样删除一个表中的重复数据。
- 问了半年多了,谁能实现这样的查询?来者有分!
- sp_xml_preparedocument存储过程问题,请高手指点!!!!!
- insert has more expressions than target columns,啥意思啊?
- 数据库安全问题
- 怎么把字符串转换为datetime型的yyyy-mm-dd格式?
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t 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','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/--树形数据广度排序处理示例.
--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t 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','小分市'--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID PID Name
------- --------- ----------
001 NULL 山东省
005 NULL 四会市
002 001 烟台市
003 001 青岛市
006 005 清远市
004 002 招远市
007 006 小分市
--*/-- 树形数据深度排序处理示例(递归法)
--测试数据
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_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1)
insert into tb values(2 , '视频' , 0 , 2)
insert into tb values(3 , '老虎' , 1 , 1)
insert into tb values(4 , '狮子' , 1 , 2)
insert into tb values(5 , '搞笑' , 2 , 1)
go--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,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 --调用函数查询id = 1及其所有子节点
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID
/*
id name pid px
----------- ---------- ----------- -----------
1 动物 0 1
3 老虎 1 1
4 狮子 1 2
(所影响的行数为 3 行)
*/drop table tb
drop function dbo.f_cid
我的表结构子ID,父PID
*/
declare @t table(ID int,PID int)
insert into @t select 1,0
insert into @t select 2,1
insert into @t select 3,2
insert into @t select 4,7
insert into @t select 5,4
insert into @t select 6,4/*
当我输入根节点为0时
结构是所有的子节点1,2,3
当我输入根节点是1时
结构是所有的子节点2,3
....
当我输入根节点为7时
结构是所有的子节点4,5,6 就是根据根节点找出所有的子节点
*/--使父id为0,此值必须带入
declare @i int
set @i=0--定义临时表,分别有ID,父ID,深度lev
declare @t2 table(ID int,PID int,lev int)
--先插入一条记录,使得系统变量 @@rowcount>0
insert @t2 select * ,@i from @t where PID=@iwhile @@rowcount>0
begin
set @i=@i+1
insert @t2
select t1.*,@i from @t2 t2 join @t t1 on t2.ID=t1.PID and t2.lev=@i-1
end
select * from @t2