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
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
SQL Server 2008中的CTE递归查询得到一棵树(按照每颗树的架构排列)
感觉这个CTE递归查询蛮好用的,先举个例子:   [c-sharp] view plain copy  print? use City;   "http://lib.csdn.net/base/go" class='replace_word' title="Go知识库" target='_blank' style='color:#df3434; fon
SQLServer CTE递归和循环对比的优势--典型案例
首先,我们新建一张测试用的临时表#country,其中包含三个字段,AreaNam(地名) ,BelongTo(上级地名) ,Msg(地方简介)Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100)) 下面我们往这张表里插入一堆测试数据: INSERT INTO #country S
SQL With (递归CTE查询)
指定临时命名的结果集,这些结果集称为公用表表达式 (CTE)。该表达式源自简单查询,并且在单条 SELECT、INSERT、UPDATE 或 DELETE 语句的执行范围内定义。该子句也可用在 CREATE VIEW 语句中,作为该语句的 SELECT 定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。 语法  
SQL Server CTE 递归查询全解
最近工作中遇到了一个问题,需要根据保存的流程数据,构建流程图。数据库中保持的流程数据是树形结构的,表结构及数据如下图: 仔细观察表结构,会发现其树形结构的特点: FFIRSTNODE:标记是否为根节点FSTABLENAME:标记来源单据名称FSID:标记来源单据分录IDFTTABLENAME :标记目标单据名称FTID:标记目标单据分录ID 图中的流程为: 销售合同--
突破SQL2005CTE递归最大100层的限制
 ;WITH t AS (  SELECT 1 AS num  UNION ALL  SELECT num+1 FROM t  WHERE num)SELECT * FROM tOPTION(MAXRECURSION 0) --当指定MAXRECURSION为0时,递归层次无限制,100为系统的默认值/*num-----------1234567891011121314151617181920
SQL递归CTE查询(;with xxx as)
with cte as ( select Id,Pid,DeptName,0 as lvl from Department where Id = 2 union all select d.Id,d.Pid,d.DeptName,lvl+1 from cte c inner join Department d on c.Id = d.Pid ) select
[SQL Server 2005/2008]递归更新update(使用CTE公用表达式)
[SQL Server 2005/2008]递归 更新 update (使用CTE公用表达式), 应用举例, t_user_sells中有每个用户每天的销售量, 现在需要得到每个用户每天的销量增长率。即:(当天销售量-昨日销售量)/昨日售销量* 100%
SQL SERVER 关于树形排序的处理
<br />今天在CSDN看到个题目是关于树形在内容:<br />题目如下(但实际的题目更简单)<br />create table #t(id int,parentid varchar(10))<br />insert into #t<br />select 1, 0 union all<br />select 10, 1 union all<br />select 13, 10 union all<br />select 16, 10 union all<br />select 11, 16 union
SQL with as 的用法 以及递归函数的写法 递归层次查询
自:http://wudataoge.blog.163.com/blog/static/80073886200961652022389/ 一.WITH AS的含义      WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNI
MSSQL 下递归CTE的应用通过父级获取所有对应的子级节点(一)
能掌握的就是应用CTE递归,WITH <Expression> AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到,可以简单理解为把一段SQL片段对应的结果集取一个别名。