select * from ProCategories order by rootid asc, orderid desc
select * from ProCategories order by rootid asc, orderid desc 和我要的结果不同
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 */
后面没人写?per 再吗?职业规划遇见问题。
select * , ROW_NUMBER() over(partition by rootId order by orderId desc)as cnt from from table where parentId = 0 union select * ,ROW_NUMBER() over(partition by rootId order by orderId desc) as cnt from from table where parentId <> 0 order by rootid desc,cnt asc
我测试过,select * , ROW_NUMBER() over(partition by rootId order by orderId desc)as cnt from (select 1 pid, 0 parentId, N'时装' as name, 1 rootId , 1 orderId union select 2 pid, 0 parentId, N'皮草' as name, 2 rootId , 2 orderId union select 3 pid, 2 parentId, N'大衣' as name, 2 rootId , 2 orderId union select 4 pid, 2 parentId, N'夹克' as name, 2 rootId , 5 orderId union select 5 pid, 2 parentId, N'大衣' as name, 2 rootId , 1 orderId union select 6 pid, 1 parentId, N'西装' as name, 1 rootId , 2 orderId ) As A where parentId = 0 union select * ,ROW_NUMBER() over(partition by rootId order by orderId desc) as cnt from (select 1 pid, 0 parentId, N'时装' as name, 1 rootId , 1 orderId union select 2 pid, 0 parentId, N'皮草' as name, 2 rootId , 2 orderId union select 3 pid, 2 parentId, N'大衣' as name, 2 rootId , 2 orderId union select 4 pid, 2 parentId, N'夹克' as name, 2 rootId , 5 orderId union select 5 pid, 2 parentId, N'大衣' as name, 2 rootId , 1 orderId union select 6 pid, 1 parentId, N'西装' as name, 1 rootId , 2 orderId ) As A where parentId <> 0 order by rootid desc,cnt asc ---结果数据 /* pid parentId name rootId orderId cnt 2 0 皮草 2 2 1 4 2 夹克 2 5 1 3 2 大衣 2 2 2 5 2 大衣 2 1 3 1 0 时装 1 1 1 6 1 西装 1 2 1 */
大哥是动态的 你没看楼主说级别无限制请高手出手楼主的要求可以这样理解 皮草 2 --- 夹克 5 --- 大衣 2 --- 大衣 1 时装 1 --- 西装 2 后面的数字是orderID 先是 2 和 1 的排列 然后是2里面的 5 2 1 进行倒序 是无限极的 这里只是例子再贴回自己的代码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 出来。
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
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
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
en 晚上聊 我的qq 332896485 上班这里不让用聊天工具。又是小弟弟,一个礼拜多 闲死了。
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 1 西装 1 2
2
5
2
1
1
2这个是什么排序规则?
皮草 2
--- 夹克 5
--- 大衣 2
--- 大衣 1
时装 1
--- 西装 2
后面的数字是orderID
先是 2 和 1 的排列
然后是2里面的 5 2 1 进行倒序
是无限极的 这里只是例子
和我要的结果不同
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
*/
select * , ROW_NUMBER() over(partition by rootId order by orderId desc)as cnt from
from table
where parentId = 0
union
select * ,ROW_NUMBER() over(partition by rootId order by orderId desc) as cnt from
from table
where parentId <> 0
order by rootid desc,cnt asc
(select 1 pid, 0 parentId, N'时装' as name, 1 rootId , 1 orderId
union
select 2 pid, 0 parentId, N'皮草' as name, 2 rootId , 2 orderId
union
select 3 pid, 2 parentId, N'大衣' as name, 2 rootId , 2 orderId
union
select 4 pid, 2 parentId, N'夹克' as name, 2 rootId , 5 orderId
union
select 5 pid, 2 parentId, N'大衣' as name, 2 rootId , 1 orderId
union
select 6 pid, 1 parentId, N'西装' as name, 1 rootId , 2 orderId
) As A
where parentId = 0
union
select * ,ROW_NUMBER() over(partition by rootId order by orderId desc) as cnt from
(select 1 pid, 0 parentId, N'时装' as name, 1 rootId , 1 orderId
union
select 2 pid, 0 parentId, N'皮草' as name, 2 rootId , 2 orderId
union
select 3 pid, 2 parentId, N'大衣' as name, 2 rootId , 2 orderId
union
select 4 pid, 2 parentId, N'夹克' as name, 2 rootId , 5 orderId
union
select 5 pid, 2 parentId, N'大衣' as name, 2 rootId , 1 orderId
union
select 6 pid, 1 parentId, N'西装' as name, 1 rootId , 2 orderId
) As A
where parentId <> 0
order by rootid desc,cnt asc
---结果数据
/*
pid parentId name rootId orderId cnt
2 0 皮草 2 2 1
4 2 夹克 2 5 1
3 2 大衣 2 2 2
5 2 大衣 2 1 3
1 0 时装 1 1 1
6 1 西装 1 2 1
*/
皮草 2
--- 夹克 5
--- 大衣 2
--- 大衣 1
时装 1
--- 西装 2
后面的数字是orderID
先是 2 和 1 的排列
然后是2里面的 5 2 1 进行倒序
是无限极的 这里只是例子再贴回自己的代码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 出来。
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
*/
例如,下面就显示错位
/*
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
*/
楼主的意思是 找每个节点下的子树 并且排序。2005 可以用 子树排序放再函数里面 在用 apply 去调用函数 apply 可以返回左输入的结果集
cross|outer 一个是不存在不返回 一个是存在返回。
只能说句 csdn 高手太鸡巴少了。除了极少的星星和钻石。 大部分的星星都是菜鸟!per 出来有私事找你!
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
*/
可以这样理解
皮草 2
--- 夹克 5
--- 大衣 2
--- 大衣 1
时装 1
--- 西装 2
后面的数字是orderID
先是 2 和 1 的排列
然后是2里面的 5 2 1 进行倒序
是无限极的 这里只是例子
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
这个是qq吗?
是不是做ms_sql都特不值钱。。本人毕业一年刚换工作 才可怜4k左右
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
上班这里不让用聊天工具。又是小弟弟,一个礼拜多 闲死了。