在SQL中分类查询并且重新编号解决方法

在SQL中分类查询并且重新编号
在SQL中如何才能查出类似这样的结果，谢谢
OneClass
No.NameStatus
1OfficialsNon-statutory
2DevelopmentNon-statutory
TwoClass
No.NameStatus
1BoardStatutory
2CommitteeNon-statutory
3HonoursNon-statutory
4ICAC Complaints CommitteeNon-statutory
ThreeClass
No.NameStatus
1ICACNon-statutory
2CitizensNon-statutory
3Corruptionstatutory

[解决办法]
3個表用 union all --連接
[解决办法]

SQL code

--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
[解决办法]
SQL code
舉個例子，猜一下use Tempdbgo--> -->  if not object_id(N'Tempdb..#T1') is null    drop table #T1GoCreate table #T1([No] int,[Name] nvarchar(11),[Status] nvarchar(13))Insert #T1select 1,N'Officials',N'Non-statutory' union allselect 2,N'Development',N'Non-statutory'Goif not object_id(N'Tempdb..#T2') is null    drop table #T2GoCreate table #T2([No] int,[Name] nvarchar(38),[Status] nvarchar(13))Insert #T2select 1,N'Board',N'Statutory' union allselect 2,N'Committee',N'Non-statutory' union allselect 3,N'Honours',N'Non-statutory' union allselect 4,N'ICAC ComplaintsCommittee','Non-statutory'Gouse Tempdbgo--> -->  if not object_id(N'Tempdb..#T3') is null    drop table #T3GoCreate table #T3([No] int,[Name] nvarchar(10),[Status] nvarchar(13))Insert #T3select 1,N'ICAC',N'Non-statutory' union allselect 2,N'Citizens',N'Non-statutory' union allselect 3,N'Corruption',N'statutor'Go;WITH aAS(Select *,ParentID=0 from #T1UNION ALLSelect *,ParentID=1 from #T2UNION ALLSelect *,ParentID=2 from #T3),a2AS(SELECT *,ord=CAST(RIGHT(100+[No],2) AS NVARCHAR(200)) FROM a WHERE ParentID=0UNION ALLSELECT 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],ParentIDFROM a2 OPTION(MAXRECURSION 0)/*NO    Name    Status    ParentID1    Officials    Non-statutory    02    Board    Statutory    13    Committee    Non-statutory    14    Honours    Non-statutory    15    ICAC ComplaintsCommittee    Non-statutory    16    Development    Non-statutory    07    ICAC    Non-statutory    28    Citizens    Non-statutory    29    Corruption    statutor    2*/
[解决办法]
探讨


Table中有这几个字段：Class  Name  Status