日期:2014-05-18 浏览次数:20614 次
--1. select row_number()over(order by name desc) as [No.],name,status from tb --2. select row_number()over(order by name) as [No.],name,status from tb --3. select row_number()over(order by status) as [No.],name,status from tb
------解决方案--------------------
舉個例子,猜一下 use Tempdb go --> --> if not object_id(N'Tempdb..#T1') is null drop table #T1 Go Create table #T1([No] int,[Name] nvarchar(11),[Status] nvarchar(13)) Insert #T1 select 1,N'Officials',N'Non-statutory' union all select 2,N'Development',N'Non-statutory' Go if not object_id(N'Tempdb..#T2') is null drop table #T2 Go Create table #T2([No] int,[Name] nvarchar(38),[Status] nvarchar(13)) Insert #T2 select 1,N'Board',N'Statutory' union all select 2,N'Committee',N'Non-statutory' union all select 3,N'Honours',N'Non-statutory' union all select 4,N'ICAC ComplaintsCommittee','Non-statutory' Go use Tempdb go --> --> if not object_id(N'Tempdb..#T3') is null drop table #T3 Go Create table #T3([No] int,[Name] nvarchar(10),[Status] nvarchar(13)) Insert #T3 select 1,N'ICAC',N'Non-statutory' union all select 2,N'Citizens',N'Non-statutory' union all select 3,N'Corruption',N'statutor' Go ;WITH a AS ( Select *,ParentID=0 from #T1 UNION ALL Select *,ParentID=1 from #T2 UNION ALL Select *,ParentID=2 from #T3 ),a2 AS ( SELECT *,ord=CAST(RIGHT(100+[No],2) AS NVARCHAR(200)) FROM a WHERE ParentID=0 UNION ALL SELECT a.*,ord=CAST(b.ord+RIGHT(100+a.[No],2) AS NVARCHAR(200)) FROM a2 AS b INNER JOIN a ON b.[No]=a.ParentID AND b.ParentID=0 ) SELECT NO=ROW_NUMBER()OVER(ORDER BY ord),[Name],[Status],ParentID FROM a2 OPTION(MAXRECURSION 0) /* NO Name Status ParentID 1 Officials Non-statutory 0 2 Board Statutory 1 3 Committee Non-statutory 1 4 Honours Non-statutory 1 5 ICAC ComplaintsCommittee Non-statutory 1 6 Development Non-statutory 0 7 ICAC Non-statutory 2 8 Citizens Non-statutory 2 9 Corruption statutor 2 */
------解决方案--------------------