这玩意完全没说明。猜了半天才猜明白left和right干嘛的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的初始量也有问题啊给个答案,让我参考下
--这个是先创建计算排列和组合的函数,然后语句里计算每个点的值和位置,再行列转换得到结果 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) ) SSet @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*[email protected] 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
--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)
根据上面的数据,查询得到下图
有企业表company(cid int identity,name nvarchar(50))
有服务表company_service(sid int identity,cid int,service_type id,s_begin date,s_end date)按照服务级别service_type倒序排列每个企业的产品例如:a企业,有产品a1,a2,a3,级别为6,b企业,有产品b1,b2,级别为4,c企业,有产品c1,c2,c3,c4,c5最终排序为
a1,b1,c1,a2,b2,c2,a3,c3,c4,c5
这玩意完全没说明。猜了半天才猜明白left和right干嘛的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的初始量也有问题啊给个答案,让我参考下
--这个是先创建计算排列和组合的函数,然后语句里计算每个点的值和位置,再行列转换得到结果
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)
) SSet @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*[email protected] 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
是先创建N行N列的表,多加一列id列,并插入N行数据
然后开始开始填充数据
首先在最上面一行的中央填入1
然后以递增的顺序将后面的数字放入上面一行靠右一列的方格中
这种放置是可以环绕的,当下一个计算出来的位置的行数超出最上面一行的时候
就返回下一行,列数超出最右边就返回最左边,按此计算方法
如果计算出来的下一个位置已经被填充,就将下一个位置改为前一个位置的正下方
直到N*N