22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(id int IDENTITY(1,1),accid varchar(10),memUse varchar(10),name varchar(20))
insert tb(accid,memUse,name)
select 'S81791','S58914','李广超' union all
select 'B21133','S58914','李广超' union all
select 'M71152','B21133','朱二喜' union all
select 'G23418','B21133','朱二喜' union all
select NULL,'G23418','蔡洪深' union all
select 'B16152','G23418','蔡洪深 ' union all
select 'B12119','S81791','成杰' union all
select 'S18161','S81791','成杰' union all
select NULL,'B12119','赵仁杰' union all
select NULL,'B12119','赵仁杰' union all
select 'S16191','S18161','方世珏' union all
select 'B23021','S18161','方世珏' union all
select 'B01691','B23021','朱旦花' union all
select NULL,'B23021','朱旦花' union all
select NULL,'B01691','蔡文华' union all
select 'B92097','B01691','蔡文华' union all
select NULL,'M71152','吴芳' union all
select NULL,'M71152','吴芳' union all
select NULL,'S16191','文天祥' union all
select NULL,'S16191','文天祥' union all
select NULL,'B16152','谢一言' union all
select NULL,'B16152','谢一言' union all
select NULL,'B92097','张东阳' union all
select NULL,'B92097','张东阳'
alter function dbo.get_lev(@memUse varchar(10))
returns @tb1 table
(id int IDENTITY(1,1)
,accid varchar(10)
,memUse varchar(10)
,name varchar(20)
,lev int)
as
begin
declare @lev int
set @lev=0
insert @tb1(accid,memUse,name,lev)
select accid,memUse,name,@lev from tb where memUse=@memUse
order by id
while @@ROWCOUNT>0
begin
set @lev=@lev+1
insert @tb1(accid,memUse,name,lev)
select a.accid,a.memUse,a.name,@lev
from tb a,@tb1 b
where a.memUse=b.accid
and b.lev=@lev-1
order by b.id,a.id
end
return
end
declare @lev int
,@memUse varchar(10)
,@RetVal varchar(8000)
,@newline AS NVARCHAR(2)
SET @newline = NCHAR(13) + NCHAR(10)
set @lev=1
SET @RetVal=''
while exists(select 1 from dbo.get_lev('S18161' ) where lev=@lev)
begin
SET @RetVal =@RetVal+N'第'+cast(@lev as varchar(10))+'层'+@newline
--+ N'memUse name'+@newline
SELECT @RetVal = @RetVal+memUse + ' ' + name +@newline
FROM (select max(id) as id,memUse,name,lev from dbo.get_lev('S18161' )
group by memUse,name,lev
) a
WHERE lev = @lev
order by id
set @lev=@lev+1
end
print @RetVal
--那就建多个表用于排序吧
--自己插数据
declare @order table(id int,name varchar(20))
insert into @result
select distinct memUse,name from tb t
where [memUse] in (select memUse from @memUse)
order by (select id from @order where name = t.name )
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([accid] varchar(6),[memUse] varchar(6),[name] varchar(6))
insert [tb]
select 'S81791','S58914','李广超' union all
select 'B21133','S58914','李广超' union all
select 'M71152','B21133','朱二喜' union all
select 'G23418','B21133','朱二喜' union all
select null,'G23418','蔡洪深' union all
select 'B16152','G23418','蔡洪深' union all
select 'B12119','S81791','成杰' union all
select 'S18161','S81791','成杰' union all
select null,'B12119','赵仁杰' union all
select null,'B12119','赵仁杰' union all
select 'S16191','S18161','方世珏' union all
select 'B23021','S18161','方世珏' union all
select 'B01691','B23021','朱旦花' union all
select null,'B23021','朱旦花' union all
select null,'B01691','蔡文华' union all
select 'B92097','B01691','蔡文华' union all
select null,'M71152','吴芳' union all
select null,'M71152','吴芳' union all
select null,'S16191','文天祥' union all
select null,'S16191','文天祥' union all
select null,'B16152','谢一言' union all
select null,'B16152','谢一言' union all
select null,'B92097','张东阳' union all
select null,'B92097','张东阳'
-->存储过程,以memUse查询
CREATE PROC sp_test(@memUse VARCHAR(40))
AS
BEGIN
SET NOCOUNT ON
DECLARE @t INT
SET @t=1
CREATE TABLE #t1(accid VARCHAR(40),memUse VARCHAR(40),name VARCHAR(40),层次 INT)
INSERT #t1
SELECT accid,memUse,name,@t
FROM tb
WHERE memUse=@memUse
CREATE TABLE #t2(accid VARCHAR(40),memUse VARCHAR(40),name VARCHAR(40),层次 INT)
INSERT #t2
SELECT b.accid,b.memUse,b.name,@t
FROM #t1 a,tb b
WHERE a.accid=b.memUse AND a.层次=1
WHILE @@ROWCOUNT>0
BEGIN
SET @t=@t+1
INSERT #t2
SELECT b.accid,b.memUse,b.name,@t
FROM #t2 a,tb b
WHERE a.accid=b.memUse AND a.层次=@t-1
END
SELECT DISTINCT(memUse),name FROM #t2 ORDER BY name
SET NOCOUNT OFF
END
-->查询
EXEC sp_test @memUse='S58914'
-->结果
/*
memUse name
G23418 蔡洪深
B01691 蔡文华
S81791 成杰
S18161 方世珏
S16191 文天祥
M71152 吴芳
B16152 谢一言
B92097 张东阳
B12119 赵仁杰
B23021 朱旦花
B21133 朱二喜
*/
--create table tb(accid varchar(10),memUse varchar(10),name varchar(20))
--insert tb
--select 'S81791','S58914','李广超' union all
--select 'B21133','S58914','李广超' union all
--select 'M71152','B21133','朱二喜' union all
--select 'G23418','B21133','朱二喜' union all
--select NULL,'G23418','蔡洪深' union all
--select 'B16152','G23418','蔡洪深 ' union all
--select 'B12119','S81791','成杰' union all
--select 'S18161','S81791','成杰' union all
--select NULL,'B12119','赵仁杰' union all
--select NULL,'B12119','赵仁杰' union all
--select 'S16191','S18161','方世珏' union all
--select 'B23021','S18161','方世珏' union all
--select 'B01691','B23021','朱旦花' union all
--select NULL,'B23021','朱旦花' union all
--select NULL,'B01691','蔡文华' union all
--select 'B92097','B01691','蔡文华' union all
--select NULL,'M71152','吴芳' union all
--select NULL,'M71152','吴芳' union all
--select NULL,'S16191','文天祥' union all
--select NULL,'S16191','文天祥' union all
--select NULL,'B16152','谢一言' union all
--select NULL,'B16152','谢一言' union all
--select NULL,'B92097','张东阳' union all
--select NULL,'B92097','张东阳'
--create function dbo.get_lev(@memUse varchar(10))
--returns @tb1 table
--(memUse varchar(10)
--,name varchar(20)
--,lev int)
--as
--begin
-- declare @lev int
-- set @lev=1
-- insert @tb1(memUse,name,lev)
-- select distinct memUse,name,@lev from tb where memUse in(
-- select accid from tb where memUse=@memUse)
-- while @@ROWCOUNT>0
-- begin
-- set @lev=@lev+1
-- insert @tb1(memUse,name,lev)
-- select distinct memUse,name,@lev
-- from tb
-- where memUse in(select accid
-- from tb
-- where memUse in(select memUse from @tb1 where lev=@lev-1) )
-- end
-- return
--end
declare @lev int
,@memUse varchar(10)
,@RetVal varchar(8000)
,@newline AS NVARCHAR(2)
SET @newline = NCHAR(13) + NCHAR(10)
set @lev=1
SET @RetVal=''
while exists(select 1 from dbo.get_lev('S58914') where lev=@lev)
begin
SET @RetVal =@RetVal+N'第'+cast(@lev as varchar(10))+'层'+@newline
--+ N'memUse name'+@newline
SELECT @RetVal = @RetVal+memUse + ' ' + name +@newline
FROM get_lev('S58914')
WHERE lev = @lev
set @lev=@lev+1
end
print @RetVal
--drop table tb
------------------------------------
第1层
B21133 朱二喜
S81791 成杰
第2层
B12119 赵仁杰
G23418 蔡洪深
M71152 吴芳
S18161 方世珏
第3层
B16152 谢一言
B23021 朱旦花
S16191 文天祥
第4层
B01691 蔡文华
第5层
B92097 张东阳
--不对?
declare @result table(memUse varchar(20),name varchar(20))
declare @memUse table(id int,memUse varchar(20))
declare @i int , @mem varchar(20)
select @i = 1,
@mem = 'S18161'
insert into @memUse select @i,[accid] from tb where memUse=@mem
and [accid] is not null
while exists(select 1 from @memUse where memUse is not null)
begin
insert into @result
select '第'+ltrim(@i)+'层',''
insert into @result
select distinct memUse,name from tb
where [memUse] in (select memUse from @memUse)
order by name
insert into @memUse
select @i+1,[accid] from tb where memUse in (select memUse from @memUse where id = @i)
and [accid] is not null
delete from @memUse where id = @i
select @i = @i + 1
end
select * from @result
----------------------------
第1层
S16191 文天祥
B23021 朱旦花
第2层
B01691 蔡文华
第3层
B92097 张东阳
--不对?
declare @result table(memUse varchar(20),name varchar(20))
declare @memUse table(id int,memUse varchar(20))
declare @i int , @mem varchar(20)
select @i = 1,
@mem = 'S18161'
insert into @memUse select @i,[accid] from tb where memUse=@mem
and [accid] is not null
while exists(select 1 from @memUse where memUse is not null)
begin
insert into @result
select '第'+ltrim(@i)+'层',''
insert into @result
select distinct memUse,name from tb
where [memUse] in (select memUse from @memUse)
order by name
insert into @memUse
select @i+1,[accid] from tb where memUse in (select memUse from @memUse where id = @i)
and [accid] is not null
delete from @memUse where id = @i
select @i = @i + 1
end
select * from @result
----------------------------
第1层
S16191 文天祥
B23021 朱旦花
第2层
B01691 蔡文华
第3层
B92097 张东阳