nclogos 2014-09-30 05:56 采纳率: 0%
浏览 2132

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条回答 默认 最新

  • 吉普赛的歌 2014-12-27 08: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

    看下是不是你要的吧

    评论

报告相同问题？

悬赏问题

  • ¥50 有数据，怎么建立模型求影响全要素生产率的因素
  • ¥50 有数据，怎么用matlab求全要素生产率
  • ¥15 TI的insta-spin例程
  • ¥15 完成下列问题完成下列问题
  • ¥15 C#算法问题, 不知道怎么处理这个数据的转换
  • ¥15 YoloV5 第三方库的版本对照问题
  • ¥15 请完成下列相关问题！
  • ¥15 drone 推送镜像时候 purge: true 推送完毕后没有删除对应的镜像,手动拷贝到服务器执行结果正确在样才能让指令自动执行成功删除对应镜像，如何解决？
  • ¥15 求daily translation（DT）偏差订正方法的代码
  • ¥15 js调用html页面需要隐藏某个按钮