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

zhlym 2010-02-04 07:10:34
数据库数据情况（因要说明其中关系，固列了较多数据出来，请大家耐心点看，^_^）

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的），麻烦各位，谢谢
...全文
300 30 打赏 收藏 转发到动态 举报
写回复
用AI写文章
30 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhlym 2010-02-05
  • 打赏
  • 举报
 回复
现是12点半，综合 ldslove 和 nianran520的两者语句，总算是交了功课，非常感谢
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
TO ldslove

语句结果和排序都对了，怎么不是表格的形式的呢？print出来的
东那个升 2010-02-04
  • 打赏
  • 举报
 回复 


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','张东阳'
东那个升 2010-02-04
  • 打赏
  • 举报
 回复 




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
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
[Quote=引用 25 楼 nianran520 的回复:]
SQL code--那就建多个表用于排序吧
--自己插数据declare@ordertable(idint,namevarchar(20))insertinto@resultselectdistinct memUse,namefrom tb twhere[memUse]in (select memUsefrom@memUse)orderby (select idfrom@orderwhere na?-
[/Quote]

水平有限，还望指点如何向 @order 插入记录供排序，谢谢
nianran520 2010-02-04
  • 打赏
  • 举报
 回复 
--那就建多个表用于排序吧

--自己插数据

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 )
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 yjh53615728 的回复:]
楼主，你这个名字结果也没有排序的
第2层:
赵仁杰，方世珏，吴芳,蔡洪深
z      f      w    c

排序也是按照字母的排序方式来啊，楼主，你自己看看咯
[/Quote]

--------------------------------------------
是的，所以想编入一个临时ID让它排序出来的结果达到我想要的那种先后顺序
yjh53615728 2010-02-04
  • 打赏
  • 举报
 回复
楼主，你这个名字结果也没有排序的
第2层:
赵仁杰，方世珏，吴芳,蔡洪深
z f w c

排序也是按照字母的排序方式来啊，楼主，你自己看看咯
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
[Quote=引用 21 楼 yjh53615728 的回复:]
to 20楼:

你提问的内容举例，也是
第1层
S16191    文天祥
B23021    朱旦花
第2层
B01691    蔡文华
第3层
B92097    张东阳
[/Quote]

以上结果由于数据碰巧是由上到下顺着下来，所以这个对的
=============================================

当 memUse'S58914' 要的结果是如下（注意有人名有先后的）

第1层
S81791 成杰
B21133 朱二喜
第2层
B12119 赵仁杰
S18161 方世珏
M71152 吴芳
G23418 蔡洪深
第3层
S16191 文天祥
B23021 朱旦花
B16152 谢一言
第4层
B01691 蔡文华
第5层
B92097 张东阳
yjh53615728 2010-02-04
  • 打赏
  • 举报
 回复
to 20楼:

你提问的内容举例，也是
第1层
S16191 文天祥
B23021 朱旦花
第2层
B01691 蔡文华
第3层
B92097 张东阳
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
--不对？

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


=============================

是的，不太对，当 memUse='S18161' 时，出来结果排序与我的举例不太对
yjh53615728 2010-02-04
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 zhlym 的回复:]
TO yjh53615728
谢谢你的回复，不过你语句出来的结果就很错了，
目前只有ldslove 和 nianran520 他们的结果是对的，就是排序上有一点出入需修正
[/Quote]

哪里错了？
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 nianran520 的回复:]
SQL code--不对？declare@resulttable(memUsevarchar(20),namevarchar(20))declare@memUsetable(idint,memUsevarchar(20))declare@iint ,@memvarchar(20)select@i=1,@mem='S18161'insertinto@memUseselect@i,[accid]fro?-
[/Quote]
是的，不太对，当 memUse='S18161' 时
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
TO yjh53615728
谢谢你的回复，不过你语句出来的结果就很错了，
目前只有ldslove 和 nianran520 他们的结果是对的，就是排序上有一点出入需修正
yjh53615728 2010-02-04
  • 打赏
  • 举报
 回复
我用的是存储过程


--> 测试数据：[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	朱二喜

*/
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
TO ldslove

你的结果中排序还是与我想要的有点出入，能按我的例子排出结果的先后吗？
东那个升 2010-02-04
  • 打赏
  • 举报
 回复
从来



--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    张东阳
nianran520 2010-02-04
  • 打赏
  • 举报
 回复 
--不对？



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	张东阳
nianran520 2010-02-04
  • 打赏
  • 举报
 回复 
--不对？



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	张东阳
zhlym 2010-02-04
  • 打赏
  • 举报
 回复
TO nianran520

--加个排序是按姓名排，可还是与我想要的结是有点出入，试着编个临时自动ID给@result，不成功，还请再指点
insert into @result
select distinct memUse,name from tb
where [memUse] in (select memUse from @memUse)
order by name
加载更多回复（10）
SQL 嵌套 N 层太长太难写怎么办？
我们工作中写SQL处理数据是家常便饭，不管是应用内数据处理还是临时查询分析都可以用SQL完成，相对其他技术（如Java等高级语言）也更简单。不过，SQL的简单只限于简单需求，有些复杂计算场景SQL写起来却很难，嵌套N层以至于达到几百上千行，说SQL代码长度时通常不会以行计而是以KB计。这种情况并不少见，相信经常写SQL的小伙伴并不陌生。为什么会出现这种情况呢？在http://c.raqsoft.com.cn/article/1639032922105 里详细分析了这个问题。虽然SQL比其他数据处理技术更简单
SQL SERVER常用语法汇总
一、SQL分类 二、基础语句 三、sql技巧 四、（MS SQL Server）SQL语句导入导出大全 回到目录 一、SQL分类 DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE) DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT) DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) 回到目录 二、基础语...
SQL常用语句总结
MS SQL里没有括号时，运算进行的次序将是先乘后除再模后加减 减号(-)也有两种用途：1.作为负号使用 2.从某一列中减去另一列 and   or   not  如果一个where子句中同时出现这三个操作符 最先评估not   然后是and  然后是or coalesce 哪个不为空用哪个 coalesce(i.ProductID,d.ProductID) @@rowco
SqlService基础一篇搞定（建库建表、插入数据、修改和删除数据、基础查询、条件查询、模糊查询、聚合函数、分组查询、多表查询）
SqlService基础知识总汇前言一、SQLSERVER建库建表1.检查数据库名是否存在2.创建数据库3.建表4.修改表结构5.删除添加约束二、SQLSERVER插入数据1.向部门表插入数据2.向职级表插入数据3.向员工表插入数据4.查询数据是否插入成功三、SQLSERVER修改和删除数据1.修改数据示例2.删除数据示例3.drop、truncate、delete区别四、SQLSERVER基础查询1.查询所有行所有列2.指定列查询（姓名，性别，月薪，电话）3.指定列查询,并自定义中文列名（姓名，性别，月薪
同期群分析是什么？教你用 SQL 来搞定
目录一、同期群分析的定义二、SQL 步骤1. 查看数据2. 根据 uid 、年月聚合用户人数3. 计算年月的差额（天数）4. 计算年月的差额（月数）5. 透视（根据 uid 、首次付费年月去透视年月差额的用户人数）6. 计算留存率一、同期群分析的定义「同期群分析」（Cohort Analysis）是一种通过“纵横”结合对用户分群的细分类型分析的方法：「横向上」——分析同期...
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章