排序查询

[weixin_36732892]

目前遇到一个sql排序卡住了，求助。
code name parentCode orderBy
A 信息1 1
B 信息2 2
C 信息1_1 A 1
D 信息1_2 A 2
E 信息2_2 B 2
F 信息2_1 B 1

我有如图所示的一张表数据，是一张自关联的父子关系表。我想排序结果是
code name parentCode orderBy
A 信息1 1
C 信息1_1 A 1
D 信息1_2 A 2
B 信息2 2
F 信息2_1 B 1
E 信息2_2 B 2
父级下的所有子集和父级在一块，并且按照orderBy排序。求解。

[RINK_1]

if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,2 union all
select N'B',N'信息2',null,1 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go

WITH CTE
AS
(select *,0 as sub_orderby,orderby as main_orderby from #T A WHERE parentCode IS NULL
 UNION ALL
 select A.*,A.orderby,B.main_orderby
 from #T A
 JOIN CTE B ON B.code=A.parentCode)

select * from CTE
order by main_orderby,sub_orderby

[听雨停了]

看你的数据格式直接order by name就可以了啊

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,1 union all
select N'B',N'信息2',null,2 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go
--测试数据结束
;WITH cte AS (
SELECT *,code AS groupcode FROM #T WHERE parentCode IS NULL
UNION ALL
SELECT #T.*,cte.groupcode FROM #T JOIN cte ON cte.Code = #T.parentCode
)
SELECT * FROM cte ORDER BY groupcode,orderBy

[「已注销」]

引用 1 楼 qq_37170555 的回复:

看你的数据格式直接order by name就可以了啊

如果name的数据格式不是这么存的不就不好使了么？所以直接order by name肯定是不符合扩展的。

[「已注销」]

引用 3 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,1 union all
select N'B',N'信息2',null,2 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go
--测试数据结束
;WITH cte AS (
SELECT *,code AS groupcode FROM #T WHERE parentCode IS NULL
UNION ALL
SELECT #T.*,cte.groupcode FROM #T JOIN cte ON cte.Code = #T.parentCode
)
SELECT * FROM cte ORDER BY groupcode,orderBy

看的我真是羞愧，感谢感谢。

[听雨停了]

引用 2 楼 weixin_36732892 的回复:

[quote=引用 1 楼 qq_37170555 的回复:]
看你的数据格式直接order by name就可以了啊

如果name的数据格式不是这么存的不就不好使了么？所以直接order by name肯定是不符合扩展的。[/quote]

use Tempdb
go
--> --> 听雨停了-->测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #tab
select N'A',N'信息1',null,1 union all
select N'B',N'信息2',null,2 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'X',N'信息1_1_1',N'C',1 union all
select N'Y',N'信息1_1_2',N'C',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
--测试数据结束

WITH cte AS (
SELECT *,cast(orderBy AS VARCHAR(200)) AS new_num FROM #tab WHERE ISNULL(parentcode,'')=''
UNION ALL
SELECT a.*,cast(b.new_num +CAST(a.orderBy AS varchar(200)) AS VARCHAR(200)) FROM #tab a
INNER JOIN cte b ON a.parentcode=b.code
)
SELECT * FROM cte
ORDER BY new_num

生成一个新的列来排序

[「已注销」]

引用 3 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,1 union all
select N'B',N'信息2',null,2 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go
--测试数据结束
;WITH cte AS (
SELECT *,code AS groupcode FROM #T WHERE parentCode IS NULL
UNION ALL
SELECT #T.*,cte.groupcode FROM #T JOIN cte ON cte.Code = #T.parentCode
)
SELECT * FROM cte ORDER BY groupcode,orderBy

再问一下，因为我的code列目前数据库里用的是GUID，然后我想按orderBy列排序有办法么？

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,2 union all
select N'B',N'信息2',null,1 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go

如果我把测试数据改成这样的，但是要让code列为B的按照OrderBy列排在前面。

[吉普赛的歌]

稍改一下

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([code] nvarchar(21),[name] nvarchar(25),[parentCode] nvarchar(21),[orderBy] int)
Insert #T
select N'A',N'信息1',null,2 union all
select N'B',N'信息2',null,1 union all
select N'C',N'信息1_1',N'A',1 union all
select N'D',N'信息1_2',N'A',2 union all
select N'E',N'信息2_2',N'B',2 union all
select N'F',N'信息2_1',N'B',1
Go
--测试数据结束
;WITH cte AS (
SELECT *,code AS groupcode FROM #T WHERE parentCode IS NULL
UNION ALL
SELECT #T.*,cte.groupcode FROM #T JOIN cte ON cte.Code = #T.parentCode
)
SELECT * FROM cte ORDER BY CASE WHEN groupcode='B' THEN 0 ELSE 1 END,groupcode,orderBy