34,576
社区成员
发帖
与我相关
我的任务
分享
--1.建表
CREATE TABLE #T
(
id INT,
[name] VARCHAR(10),
[for] INT,
[left] INT,
[right] INT
)
--2.插入数据
INSERT INTO #T VALUES(1, '小小', 0, 3, 2)
INSERT INTO #T VALUES(4, '笑笑', 2, 8, 9)
INSERT INTO #T VALUES(5, '琦琦', 2, 13, 0)
INSERT INTO #T VALUES(2, '琪琪', 1, 5, 4)
INSERT INTO #T VALUES(3, '晓晓', 1, 6, 7)
INSERT INTO #T VALUES(6, '兵兵', 3, 10, 11)
INSERT INTO #T VALUES(7, '冰冰', 5, 0, 12)
INSERT INTO #T VALUES(8, '公主', 4, 0, 0)
INSERT INTO #T VALUES(9, '筱筱', 4, 0, 0)
INSERT INTO #T VALUES(10, '姗姗', 6, 0, 0)
INSERT INTO #T VALUES(11, '珊珊', 6, 0, 0)
INSERT INTO #T VALUES(12, '微微', 7, 0, 0)
INSERT INTO #T VALUES(13, '薇薇', 5, 0, 0)
--这个是先创建计算排列和组合的函数,然后语句里计算每个点的值和位置,再行列转换得到结果
Declare @A int=8;
Declare @w nVarchar(3000)=''
Declare @s nVarchar(3000)=''
Declare @sql nvarchar(4000)=''
Select
@w=@w+',['+number+']',
@s=@s+',isnull(['+number+'],'''') As ['+number+']'
From
(
Select convert(Varchar(10),number) As number
From master.dbo.spt_values
Where type='P' and number between 1 and 2*(@A+1)
) S
Set @sql=
'
With T
As
(
Select number From master.dbo.spt_values
Where type=''P'' And number between 1 and @A+1
)
Select '+Stuff(@s,1,1,'')+' From
(
Select A.number*2+@A-B.number As A,B.number As B,
Convert(Varchar(10),nullif(dbo.fn_c(A.number-1,b.number),0)) As C
From T A cross join T B
Where b.number<=@A
) s
Pivot
(
max(c)
for
A in('+Stuff(@w,1,1,'') +')
)p'
exec sp_executesql @sql,N'@A int',@A
--排列
Create Function fn_p(@I int)
Returns int
As
Begin
Declare @Rst int=1
While @I>0
Begin
Set @Rst=@Rst*@I
Set @I=@I-1
End
Return @Rst
end
go
--组合
Create Function fn_c(@n int,@m int)
Returns int
As
Begin
return dbo.fn_p(@m)/(dbo.fn_p(@n)*dbo.fn_p(@m-@N))
End
go
with t as (
select id,name,[for] f,[left] l,[right] r from #t
),t1 as (
select a.*,isnull(c.id,isnull(b.id,0)) as parent from t a
left join t b on a.id=b.l
left join t c on a.id=c.r
),t2 as (
select *,0 as lv,0 as loc from t1 where parent=0
union all
select a.*,lv+1,(case when a.id=b.l then -1*power(2,2-lv) else power(2,2-lv) end)+b.loc from t1 a,t2 b where a.parent=b.id
),t3 as (
select lv+1 as id,loc+abs((select min(loc) from t2))+1 as loc,name from t2
)
select isnull([1],'') as [1],isnull([2],'') as [2],isnull([3],'') as [3],isnull([4],'') as [4],isnull([5],'') as [5],isnull([6],'') as [6],isnull([7],'') as [7],isnull([8],'') as [8]
,[9],[10],[11],[12],[13],[14],[15]
from t3
pivot(
max(name) for loc in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
) p
问题是有个小疑问啊,如果我不知道最终有多少数据,那么行列转换这里必须用exec了,不能一个语句实现,另外就是,如果不知道有多少层,t2里的power的初始量也有问题啊
给个答案,让我参考下