22,302
社区成员




if object_id(N't','U') is not null
drop table t
go
create table t(pid int identity(1,1),parentId int,name nvarchar(128),rootId int,orderId int)
insert into t(parentId,name,rootId,orderId)
select 0 ,'时装',1,1
union all
select 0,'皮草',2,2
union all
select 2,'a大衣',2,2
union all
select 2,'夹克',2,5
union all
select 2,'大衣',2,1
union all
select 1,'西装',1,2
if object_id(N'f_t') is not null
drop function f_t
go
create function f_t(@pid int)returns table
as
return
with xwj
as
(select pid,parentId,name,rootId,orderId from t where pid=@pid
union all
select a.pid,a.parentId,a.name,a.rootId,a.orderId from t a inner join xwj b on a.parentId=b.pid
)
select top 100 percent * from
(select top 100 percent *,px=row_number()over(partition by parentId order by orderId desc) from xwj order by pid,orderId desc)x
order by parentId,px
----测试
select b.pid,b.parentId,b.name,b.rootId,b.orderId from (select top 100 * from t order by parentId) as a
outer apply f_t(a.pid) as b
order by a.parentId,a.orderId desc,b.px
/*
2 0 皮草 2 2
4 2 夹克 2 5
3 2 a大衣 2 2
5 2 大衣 2 1
1 0 时装 1 1
6 1 西装 1 2
6 1 西装 1 2
4 2 夹克 2 5
3 2 a大衣 2 2
5 2 大衣 2 1
*/
DECLARE @t TABLE([pid] INT,[parentId] INT,[name] NVARCHAR(10),[rootId] INT,[orderId] INT)
INSERT @t SELECT 1,0,N'时装',1,1
UNION ALL SELECT 2,0,N'皮草',2,2
UNION ALL SELECT 3,2,N'大衣',2,2
UNION ALL SELECT 4,2,N'夹克',2,5
UNION ALL SELECT 5,2,N'大衣',2,1
UNION ALL SELECT 6,1,N'西装',1,2
select * from @t
order by rootid desc,parentid, orderid desc
DECLARE @t TABLE([pid] INT,[parentId] INT,[name] NVARCHAR(10),[rootId] INT,[orderId] INT)
INSERT @t SELECT 1,0,N'时装',1,1
UNION ALL SELECT 2,0,N'皮草',2,2
UNION ALL SELECT 3,2,N'大衣',2,2
UNION ALL SELECT 4,2,N'夹克',2,5
UNION ALL SELECT 5,2,N'大衣A',2,1
UNION ALL SELECT 6,1,N'西装',1,2
UNION ALL SELECT 7,4,N'老板夹克A',2,2
UNION ALL SELECT 8,4,N'老板夹克B',2,1
/************/
/*Test Data*/
/***fcuandy**/
/*2008-11-25*/
/************/
;
WITH fc(id,pathSTR)
AS
(
SELECT pid,RIGHT(11000 - orderid,4) + CAST(RIGHT(10000 + PID,4) AS NVARCHAR(MAX))
FROM @t
WHERE parentid=0
UNION ALL
SELECT a.pid,b.pathSTR + RIGHT(11000 - orderid,4) + RIGHT(10000 + PID,4)
FROM @t a,fc b
WHERE a.parentid=b.id
)
SELECT a.*
FROM @t a,fc b
WHERE a.pid=b.id
ORDER BY b.pathSTR
/*忘贴结果
2 0 皮草 2 2
4 2 夹克 2 5
7 4 老板夹克A 2 2
8 4 老板夹克B 2 1
3 2 大衣 2 2
5 2 大衣A 2 1
1 0 时装 1 1
6 1 西装 1 2
*/
DECLARE @t TABLE([pid] INT,[parentId] INT,[name] NVARCHAR(10),[rootId] INT,[orderId] INT)
INSERT @t SELECT 1,0,N'时装',1,1
UNION ALL SELECT 2,0,N'皮草',2,2
UNION ALL SELECT 3,2,N'大衣',2,2
UNION ALL SELECT 4,2,N'夹克',2,5
UNION ALL SELECT 5,2,N'大衣A',2,1
UNION ALL SELECT 6,1,N'西装',1,2
UNION ALL SELECT 7,4,N'老板夹克A',2,2
UNION ALL SELECT 8,4,N'老板夹克B',2,1
/************/
/*Test Data*/
/***fcuandy**/
/*2008-11-25*/
/************/
;
WITH fc(id,pathSTR)
AS
(
SELECT pid,RIGHT(10000 + orderid,4) + CAST(RIGHT(10000 + PID,4) AS NVARCHAR(MAX))
FROM @t
WHERE parentid=0
UNION ALL
SELECT a.pid,b.pathSTR + RIGHT(10000 + orderid,4) + RIGHT(10000 + PID,4)
FROM @t a,fc b
WHERE a.parentid=b.id
)
SELECT a.*
FROM @t a,fc b
WHERE a.pid=b.id
ORDER BY b.pathSTR
/*
1 0 时装 1 1
6 1 西装 1 2
2 0 皮草 2 2
5 2 大衣A 2 1
3 2 大衣 2 2
4 2 夹克 2 5
8 4 老板夹克B 2 1
7 4 老板夹克A 2 2
*/
declare @t table(pid int ,parentId int,name nvarchar(128),rootId int,orderId int)
insert into @t
select 1,0,'时装',1,1 union all
select 2,0,'皮草',2,2 union all
select 3,2,'大衣',2,2 union all
select 4,2,'夹克',2,5 union all
select 5,2,'大衣',2,1 union all
select 6,2,'西装',1,2
select * from @t
order by rootId desc, parentId, orderId desc
/*
pid parentId name rootId orderId
2 0 皮草 2 2
4 2 夹克 2 5
3 2 大衣 2 2
5 2 大衣 2 1
1 0 时装 1 1
6 2 西装 1 2
*/
if object_id(N't','U') is not null
drop table t
go
create table t(pid int identity(1,1),parentId int,name nvarchar(128),rootId int,orderId int)
insert into t(parentId,name,rootId,orderId)
select 0 ,'时装',1,1
union all
select 0,'皮草',2,2
union all
select 2,'a大衣',2,2
union all
select 2,'夹克',2,5
union all
select 2,'大衣',2,1
union all
select 1,'西装',1,2
if object_id(N'f_t') is not null
drop function f_t
go
create function f_t(@pid int)returns table
as
return
with xwj
as
(select pid,parentId,name,rootId,orderId from t where pid=@pid
union all
select a.pid,a.parentId,a.name,a.rootId,a.orderId from t a inner join xwj b on a.parentId=b.pid
)
select top 100 percent * from
(select top 100 percent *,px=row_number()over(partition by parentId order by orderId desc) from xwj order by pid,orderId desc)x
order by parentId,px
----测试
select b.pid,b.parentId,b.name,b.rootId,b.orderId from (select top 100 * from t order by parentId) as a
outer apply f_t(a.pid) as b
order by a.parentId,a.orderId desc,b.px
/*
2 0 皮草 2 2
4 2 夹克 2 5
3 2 a大衣 2 2
5 2 大衣 2 1
1 0 时装 1 1
6 1 西装 1 2
6 1 西装 1 2
4 2 夹克 2 5
3 2 a大衣 2 2
5 2 大衣 2 1
*/
ps per 出来。