[急求]Sql排序问题，求大神帮忙

happyyang0816 2016-04-01 04:48:25

数据如下：
ID Name Date ParentID Layer
1 A 2016-4-1 NULL 0
2 A-1 2016-4-2 1 1
3 A-1-1 2016-4-5 2 2
4 A-1-2 2016-4-4 2 2
5 A-2 2016-4-6 1 1
6 B 2016-3-1 NULL 0
7 B-1 2016-5-1 6 1
8 B-2 2016-4-1 6 1
要求出来的结果先根据层数排序，同层的根据日期排序，效果如下：
ID Name Date ParentID Layer
6 B 2016-3-1 NULL 0
8 B-2 2016-4-1 6 1
7 B-1 2016-5-1 6 1
1 A 2016-4-1 NULL 0
2 A-1 2016-4-2 1 1
4 A-1-2 2016-4-4 2 2
3 A-1-1 2016-4-5 2 2
5 A-2 2016-4-6 1 1

求大神帮忙！！！！

...全文

188 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

happyyang0816 2016-04-07

打赏
举报

不好意思，我的问题描述有点问题，name只是显示用，没有任何规律的，我写成那样只是为了方便看结果，谢谢各位的解答。

砸死牛顿的苹果 2016-04-04

打赏
举报

如果不是叶子结点也要按时间排序还是cte吧

砸死牛顿的苹果 2016-04-04

打赏
举报

思路引用9楼的

,稍微简化了下

砸死牛顿的苹果 2016-04-04

打赏
举报

--引用一楼的数据---不说这句好像不尊重别人劳动...

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(25),[Date] Date,[ParentID] int,[Layer] int)
Insert #T
select 1,N'A','2016-4-1',null,0 union all
select 2,N'A-1','2016-4-2',1,1 union all
select 3,N'A-1-1','2016-4-5',2,2 union all
select 4,N'A-1-2','2016-4-4',2,2 union all
select 5,N'A-2','2016-4-6',1,1 union all
select 6,N'B','2016-3-1',null,0 union all
select 7,N'B-1','2016-5-1',6,1 union all
select 8,N'B-2','2016-4-1',6,1 union all 
select 9,N'A-111111','2016-4-1',1,1 union all
select 10,N'A-111111-1','2016-5-1',9,2 union all 
select 11,N'A-111111-2','2016-4-1',9,2
Go
;with t as 
(select *,
	tname=SUBSTRING(name,1,len(name)-charindex('-',reverse(name))+1),
	maxL=MAX(layer)over(partition by left(name,1)) 
from #t)
select * from t 
order by (case when Layer=maxL then tname else Name end),date

道素 2016-04-02

打赏
举报

我也提供一种思路，借用一下roy_88的临时表#t：大体思路是将相同首写字母的数据的name变成同样的结构，最大的Layer减去当前数据Layer的值就是要补全的layer字符串，如果 A字母的最大是2，A的layer是0就变成A-0-0 然后用新生成的Name排序，遇到最底层的用司机排序


;WITH t AS (
     SELECT *,[name]+REPLICATE('-0',MAX([Layer]) OVER (PARTITION BY LEFT([name],1))-isnull([Layer],0) ) AS newname , MAX([Layer]) OVER (PARTITION BY LEFT([name],1)) AS MaxLayer
     FROM #t 
) 
SELECT *,ISNULL(a.pNewName,'') +convert(VARCHAR, t.[date],101) FROM t 
OUTER APPLY (SELECT tt.newname AS pNewName FROM t AS tt WHERE tt.id=t.[ParentID]) a
ORDER BY LEFT(t.[name],1),CASE WHEN t.layer=t.MaxLayer THEN ISNULL(a.pNewName,'') +convert(VARCHAR, t.[date],101)  ELSE  t.newname END

ID Name Date ParentID Layer newname MaxLayer pNewName (No column name) 1 A 2016-04-01 NULL 0 A-0-0 2 NULL 04/01/2016 2 A-1 2016-04-02 1 1 A-1-0 2 A-0-0 A-0-004/02/2016 4 A-1-2 2016-04-04 2 2 A-1-2 2 A-1-0 A-1-004/04/2016 3 A-1-1 2016-04-05 2 2 A-1-1 2 A-1-0 A-1-004/05/2016 5 A-2 2016-04-06 1 1 A-2-0 2 A-0-0 A-0-004/06/2016 6 B 2016-03-01 NULL 0 B-0 1 NULL 03/01/2016 8 B-2 2016-04-01 6 1 B-2 1 B-0 B-004/01/2016 7 B-1 2016-05-01 6 1 B-1 1 B-0 B-005/01/2016

spiritofdragon 2016-04-01

打赏
举报

确实，ID或者Name用定长，没有值用0填充这是通常设计，这样设计，就不递归了。但设计不好时，会递归就行了。

spiritofdragon 2016-04-01

打赏
举报

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(25),[Date] Date,[ParentID] int,[Layer] int)
Insert #T
select 1,N'A','2016-4-1',null,0 union all
select 2,N'A-1','2016-4-2',1,1 union all
select 3,N'A-1-1','2016-4-5',2,2 union all
select 4,N'A-1-2','2016-4-4',2,2 union all
select 5,N'A-2','2016-4-6',1,1 union all
select 6,N'B','2016-3-1',null,0 union all
select 7,N'B-1','2016-5-1',6,1 union all
select 8,N'B-2','2016-4-1',6,1 union all 
select 9,N'A-111111','2016-4-1',1,1 union all
select 10,N'A-111111-1','2016-5-1',9,2 union all 
select 11,N'A-111111-2','2016-4-1',9,2
Go
with cnt as (
select COUNT(1) cnt from #T
)
,MaxLayer as (select MAX(Layer)MaxLayer from #T)
,cte as (
select 
	*
	,POWER((select cnt from cnt),(select MaxLayer from MaxLayer)-Layer)*ROW_NUMBER()over(order by date)CurrSort
from #T 
where Layer=0
union all
select 
	t.*
	,cte.CurrSort+POWER((select cnt from cnt),(select MaxLayer from MaxLayer)-t.Layer)*ROW_NUMBER()over(order by t.date)
from cte join #T t on cte.ID=t.ParentID
)
select *
from cte
order by CurrSort

中国风 2016-04-01

打赏
举报

如果NAME为显示名称，就新增一列显示路径如以上Ord（生成的排序列）用CTE的效率慢几倍都属正常以上回复能看懂就是方法，看不懂那就是取巧

中国风 2016-04-01

打赏
举报

CTE方法，长度不定时 use Tempdb go --> --> if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([ID] int,[Name] nvarchar(25),[Date] Date,[ParentID] int,[Layer] int) Insert #T select 1,N'A','2016-4-1',null,0 union all select 2,N'A-1','2016-4-2',1,1 union all select 3,N'A-1-1','2016-4-5',2,2 union all select 4,N'A-1-2','2016-4-4',2,2 union all select 5,N'A-2','2016-4-6',1,1 union all select 6,N'B','2016-3-1',null,0 union all select 7,N'B-1','2016-5-1',6,1 union all select 8,N'B-2','2016-4-1',6,1 Go ;WITH T AS ( SELECT *,Ord=CAST(RIGHT(10000+ROW_NUMBER()OVER(ORDER BY [Date]),4) AS VARCHAR(100)) FROM #T AS a WHERE [ParentID] IS NULL OR [ParentID] =0 UNION ALL SELECT a.* ,Ord=CAST(b.Ord+RIGHT(10000+ROW_NUMBER()OVER(PARTITION BY b.ID ORDER BY a.[Date]),4) AS VARCHAR(100)) FROM #T AS a INNER JOIN T AS b ON b.ID=a.ParentID ) SELECT * FROM T ORDER BY T.Ord /* ID Name Date ParentID Layer Ord 6 B 2016-03-01 NULL 0 0001 8 B-2 2016-04-01 6 1 00010001 7 B-1 2016-05-01 6 1 00010002 1 A 2016-04-01 NULL 0 0002 2 A-1 2016-04-02 1 1 00020001 4 A-1-2 2016-04-04 2 2 000200010001 3 A-1-1 2016-04-05 2 2 000200010002 5 A-2 2016-04-06 1 1 00020002 */

中国风 2016-04-01

打赏
举报

引用 3 楼 happyyang0816 的回复:

[quote=引用 2 楼 spiritofdragon 的回复:] 太取巧了。加一条 union all select 8,N'A-111111','2016-4-1',1,1 就不对了。

是啊，而且结果与我的要求有点小出入，最后3条的顺序不对[/quote] Name必须固定长度，没有用0填充，长度不定，那是你设计问题如果不这样处理，用CTE递归生成这样的固定长度列，方法相同，这样用低效

happyyang0816 2016-04-01

打赏
举报

引用 2 楼 spiritofdragon 的回复:

太取巧了。加一条 union all select 8,N'A-111111','2016-4-1',1,1 就不对了。

是啊，而且结果与我的要求有点小出入，最后3条的顺序不对

spiritofdragon 2016-04-01

打赏
举报

太取巧了。加一条 union all select 8,N'A-111111','2016-4-1',1,1 就不对了。

中国风 2016-04-01

打赏
举报

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(25),[Date] Date,[ParentID] int,[Layer] int)
Insert #T
select 1,N'A','2016-4-1',null,0 union all
select 2,N'A-1','2016-4-2',1,1 union all
select 3,N'A-1-1','2016-4-5',2,2 union all
select 4,N'A-1-2','2016-4-4',2,2 union all
select 5,N'A-2','2016-4-6',1,1 union all
select 6,N'B','2016-3-1',null,0 union all
select 7,N'B-1','2016-5-1',6,1 union all
select 8,N'B-2','2016-4-1',6,1
Go


SELECT  a.*
FROM    #T AS a
CROSS APPLY (SELECT TOP 1 Date,Name FROM #T WHERE a.Name LIKE name + '%' ORDER BY a.name) AS b
ORDER BY b.Date,b.Name,LEN(a.name),Date;

/*
ID	Name	Date	ParentID	Layer
6	B	2016-03-01	NULL	0
8	B-2	2016-04-01	6	1
7	B-1	2016-05-01	6	1
1	A	2016-04-01	NULL	0
2	A-1	2016-04-02	1	1
5	A-2	2016-04-06	1	1
4	A-1-2	2016-04-04	2	2
3	A-1-1	2016-04-05	2	2
*/