2 nclogos nclogos 于 2014.09.30 13:56 提问

sqlserver 用CTE 递归 排序问题 求大神指教啊

create table t_manage
(f_gener int,f_accounts varchar(100) null,
f_firstGener varchar(100) null,
f_secondGener varchar(100) null,
f_thirdGener varchar(100) null,
f_joindate datetime null
)
select * from dbo.t_manage
insert into dbo.t_manage values('1','BV51','F51','D51',null,'2013-11-08 12:02:29.477')
insert into dbo.t_manage values('1','CY51','F51','D51',null,'2014-05-03 13:48:05.240')
insert into dbo.t_manage values('1','CY52','F51','D51',null,'2014-07-08 11:44:37.927')
insert into dbo.t_manage values('1','BV58','F51','D51',null,'2014-09-25 14:26:16.397')
insert into dbo.t_manage values('1','BV519','F51','D51',null,'2014-09-28 11:54:05.190')
insert into dbo.t_manage values('1','BV520','F51','D51',null,'2014-09-28 12:01:40.753')
insert into dbo.t_manage values('1','D518','F51','D51',null,'2014-09-28 12:04:37.537')
insert into dbo.t_manage values('1','CY518','F51','D51',null,'2014-09-28 15:22:20.727')
insert into dbo.t_manage values('1','BU5103','F51','D51',null,'2014-09-28 16:52:37.243')

insert into dbo.t_manage values('2','BU5101','CY51','F51','D51','2013-11-08 12:02:29.477')
insert into dbo.t_manage values('2','BU591','CY51','F51','D51','2014-05-03 13:48:05.240')
insert into dbo.t_manage values('2','CY58','CY52','F51','D51','2014-07-08 11:44:37.927')
insert into dbo.t_manage values('2','BV57','CY52','F51','D51','2014-09-25 14:26:16.397')
insert into dbo.t_manage values('2','BV59','BV58','F51','D51','2014-09-28 11:54:05.190')
insert into dbo.t_manage values('2','BV510','CY52','F51','D51','2014-09-28 12:01:40.753')
insert into dbo.t_manage values('2','CY59','CY52','F51','D51','2014-09-28 12:04:37.537')
insert into dbo.t_manage values('2','D512','CY52','F51','D51','2014-09-28 15:22:20.727')
insert into dbo.t_manage values('2','F510','CY52','F51','D51','2014-09-28 16:52:37.243')
insert into dbo.t_manage values('2','CY510','CY52','F51','D51','2014-09-25 14:26:16.397')
insert into dbo.t_manage values('2','D513','CY52','F51','D51','2014-09-28 11:54:05.190')
insert into dbo.t_manage values('2','BV512','BV51','F51','D51','2014-09-28 12:01:40.753')
insert into dbo.t_manage values('2','F511','CY52','F51','D51','2014-09-28 12:04:37.537')
insert into dbo.t_manage values('2','BV521','BV51','F51','D51','2014-09-28 15:22:20.727')
insert into dbo.t_manage values('2','CY515','BV51','F51','D51','2014-09-28 16:52:37.243')

insert into dbo.t_manage values('3','D511','BV59','CY52','F51','2014-09-25 14:55:08.010')
insert into dbo.t_manage values('3','Z58','CY58','CY52','F51','2014-09-25 15:33:19.227')
insert into dbo.t_manage values('3','BU597','BV510','CY52','F51','2014-09-25 17:49:45.923')
insert into dbo.t_manage values('3','BV511','F510','CY52','F51','2014-09-26 14:28:34.310')
insert into dbo.t_manage values('3','Z510','BV510','CY52','F51','2014-09-27 11:47:40.927')

这是一张表 以及表中内容 f_gener=1表示第一代 f_gener=2表示第二代 f_gener=3表示第三代 f_joindate表示加入日期 现在要求:以第一代为主排序,再以第二代细分第三代,第一代排序越晚加入帐号排在越前面,第二代,第三代再按照先后顺序排序

排序需求:
以第一代帐号排序 越晚加入帐号排在越前面,第二代和第三代再按照加入先后顺序依序排列。
现在写的sql可以做到各代层级正确,第一代帐号也能满足越晚加入越前面。但是第二代和第三代就没办法做到了 求指教啊
WITH personreleation AS (
select * from (
SELECT row_number() over(order by f_joindate desc) xh,a.f_accounts,a.f_firstGener,a.f_secondGener,a.f_thirdGener,a.f_joindate,
cast(f_firstGener+'\'+ f_accounts AS VARCHAR(200)) AS Full_Code,cast(1 as int) as f_gener
FROM t_manage a with(nolock) WHERE f_firstGener = 'F51') as m

UNION all
SELECT c.xh,b.f_accounts,b.f_firstGener,b.f_secondGener,b.f_thirdGener,b.f_joindate,
cast(c.Full_Code +'\'+ b.f_accounts AS VARCHAR(200)) AS Full_Code,cast(c.f_gener+1 as int) as f_gener
FROM t_manage AS b with(nolock)
INNER join personreleation AS c ON b.f_firstGener= c.f_accounts
)
,results as (
SELECT ROW_NUMBER() OVER(ORDER BY xh,Full_Code,f_joindate desc) rownumber,

  • FROM personreleation with(nolock)
    where 1=1 ) select * from results where rownumber BETWEEN 1 AND 50

1个回答

yenange
yenange   2014.12.27 16:00
 ;WITH personreleation AS (
    select * from (
        SELECT row_number() over(order by f_joindate desc) xh,a.f_accounts,a.f_firstGener,a.f_secondGener,a.f_thirdGener,a.f_joindate,
        cast(f_firstGener+'\'+ f_accounts AS VARCHAR(200)) AS Full_Code,cast(1 as int) as f_gener 
        FROM t_manage a with(nolock) WHERE f_firstGener = 'F51') as m
    UNION all 
    SELECT c.xh,b.f_accounts,b.f_firstGener,b.f_secondGener,b.f_thirdGener,b.f_joindate,
        cast(c.Full_Code +'\'+ b.f_accounts AS VARCHAR(200)) AS Full_Code,cast(c.f_gener+1 as int) as f_gener 
    FROM t_manage AS b with(nolock) 
        INNER join personreleation AS c ON b.f_firstGener= c.f_accounts 
)
,
results as (
    SELECT ROW_NUMBER() OVER( PARTITION BY f_gener ORDER BY xh,Full_Code,f_joindate desc) rownumber, * FROM personreleation with(nolock) where 1=1 
)
select * from results where rownumber BETWEEN 1 AND 50

加了: PARTITION BY f_gener

看下是不是你要的吧

Csdn user default icon
上传中...
上传图片
插入图片