求一组挺难的MS SQL（三天了没搞定），麻烦各位出手

Question

数据库数据情况（因要说明其中关系，固列了较多数据出来，请大家耐心点看，^_^）accid      memUse     name
S81791     S58914     李广超
B21133     S58914     李广超
M71152    B21133     朱二喜
G23418    B21133     朱二喜
NULL       G23418     蔡洪深
B16152     G23418     蔡洪深
B12119    S81791     成杰
S18161    S81791     成杰
NULL       B12119     赵仁杰
NULL       B12119     赵仁杰
S16191     S18161     方世珏
B23021     S18161     方世珏
B01691     B23021     朱旦花
NULL       B23021     朱旦花
NULL       B01691     蔡文华
B92097     B01691     蔡文华
NULL       M71152     吴芳
NULL       M71152     吴芳
NULL       S16191     文天祥
NULL       S16191     文天祥
NULL       B16152     谢一言
NULL       B16152     谢一言
NULL       B92097     张东阳
NULL       B92097     张东阳数据说明:
1、每个 memUse 字段用户都有仅两行记录（如 S58914 有两行记录）
2、每个 memUse 字段用户其对应的 accid 字段最多会跟两个用户（如 B21133 用户对应的 accid字段有 M71152、G23418 ），当然有时会只有一个，有时会两个都是NULL（如 B12119 对应的accid 字段两个都是NULL）
3、其关系是由上到下每个用户 accid 内容 与 memUse 内容关联（好比上下线的关系）举例说明1需要的结果：（如 memUse='S58914' 结果如下）第1层（2个，此行也想列出作标识，本括号的不算在内，以下相同）
S81791     成杰
B21133     朱二喜第2层（4个，由于每个人有两行记录，即有可能会跟最多两个人，所以到这里变成4个了，依此类推）
B12119     赵仁杰
S18161     方世珏
M71152     吴芳
G23418     蔡洪深第3层（8个，正常情况下会有8个，但如像 B12119 没有对应跟用户，所以为NULL的就不显出来了）
S16191     文天祥
B23021     朱旦花
B16152     谢一言（如这行本来是 G23418 蔡洪深第二行的对应的accid 用户）第4层（16个，正常情况下会有16个，但如像 S16191 没有对应跟用户，所以为NULL的就不显出来了，依此类推）
B01691     蔡文华第5层（32个，正情况下会有32个）
B92097     张东阳举例说明2需要的结果：（如 memUse='S18161' 结果如下）
第1层
S16191     文天祥
B23021     朱旦花
第2层
B01691     蔡文华
第3层
B92097     张东阳以以上举例说明，求实现这个要求的一组MS SQL语句（最好是SQL2000的），麻烦各位，谢谢

Answer 1

--> 测试数据：[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','张东阳'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 nullwhile 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) 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层
B23021 朱旦花
S16191 文天祥
第2层
B01691 蔡文华
第3层
B92097 张东阳

Answer 2

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 table #tb1(memUse varchar(10),name varchar(20),lev int)declare  @lev int,@memUse varchar(10)set @memUse='S58914'--查询条件
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  print N'第'+cast(@lev as varchar(10))+'层'
select memUse,name from #tb1 where lev=@lev
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
drop table #tb1
drop table #tb

Answer 3

24 行受影响)(2 行受影响)
第1层
memUse     name
---------- --------------------
B21133     朱二喜
S81791     成杰(2 行受影响)(4 行受影响)第2层
memUse     name
---------- --------------------
B12119     赵仁杰
G23418     蔡洪深
M71152     吴芳
S18161     方世珏(4 行受影响)(3 行受影响)第3层
memUse     name
---------- --------------------
B16152     谢一言
B23021     朱旦花
S16191     文天祥(3 行受影响)(1 行受影响)第4层
memUse     name
---------- --------------------
B01691     蔡文华(1 行受影响)(1 行受影响)第5层
memUse     name
---------- --------------------
B92097     张东阳(1 行受影响)(0 行受影响)

Answer 4

TO nianran520
感谢出手，居然这么简短，现在还不全看得懂，还需请教测试了下，出来的层与结果对了，但目前的语句先后顺序好像有点出入@mem = 'S18161' 正确结果如下
第1层 
S16191    文天祥 
B23021    朱旦花 
第2层 
B01691    蔡文华 
第3层 
B92097    张东阳麻烦nianran520再帮调一下顺序，感谢

Answer 5

--加个排序即可
insert into @result
select distinct memUse,name from tb 
where [memUse] in (select memUse from @memUse)
order by name

Answer 6

TO ldslove
你的语句出来的结果为什么是全分开的？能形成一个张与 层数字一起综合显示的结果 表吗？还有就是出来的顺序与我的列的也是有出入，麻烦再帮调下，3Q

Answer 7

TO nianran520--加个排序是按姓名排，可还是与我想要的结是有点出入，试着编个临时自动ID给@result，不成功，还请再指点
    insert into @result
    select distinct memUse,name from tb 
    where [memUse] in (select memUse from @memUse)
    order by name

Answer 8

--不对？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 nullwhile 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 张东阳

Answer 9

--不对？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 nullwhile 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 张东阳

Answer 10

从来
--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 = @levset @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    张东阳

Answer 11

TO ldslove你的结果中排序还是与我想要的有点出入，能按我的例子排出结果的先后吗？

Answer 12

我用的是存储过程--> 测试数据：[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 朱二喜
*/

Answer 13

TO yjh53615728
谢谢你的回复，不过你语句出来的结果就很错了，
目前只有ldslove 和 nianran520 他们的结果是对的，就是排序上有一点出入需修正

Answer 14

是的，不太对，当 memUse='S18161' 时

Answer 15

--不对？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 nullwhile 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    张东阳
=============================是的，不太对，当 memUse='S18161' 时，出来结果排序与我的举例不太对

Answer 16

to 20楼:你提问的内容举例，也是
第1层 
S16191    文天祥 
B23021    朱旦花 
第2层 
B01691    蔡文华 
第3层 
B92097    张东阳 

Answer 17

以上结果由于数据碰巧是由上到下顺着下来，所以这个对的
=============================================当  memUse'S58914' 要的结果是如下（注意有人名有先后的）第1层
S81791    成杰 
B21133    朱二喜 
第2层
B12119    赵仁杰 
S18161    方世珏 
M71152    吴芳 
G23418    蔡洪深 
第3层
S16191    文天祥 
B23021    朱旦花 
B16152    谢一言
第4层 
B01691    蔡文华 
第5层
B92097    张东阳

Answer 18

楼主，你这个名字结果也没有排序的
第2层:
赵仁杰，方世珏，吴芳,蔡洪深
z       f      w    c排序也是按照字母的排序方式来啊，楼主，你自己看看咯

Answer 19

--那就建多个表用于排序吧
--自己插数据
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 ) 

Answer 20

水平有限，还望指点如何向 @order 插入记录供排序，谢谢

Answer 21

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 idset @lev=@lev+1
end
print @RetVal

Answer 22

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','张东阳'  

Answer 23

TO ldslove
 
语句结果和排序都对了，怎么不是表格的形式的呢？print出来的

Answer 24

现是12点半，综合 ldslove 和 nianran520的两者语句，总算是交了功课，非常感谢