在SQL中如何才能查出类似这样的结果,谢谢
OneClass
No. Name Status
1 Officials Non-statutory
2 Development Non-statutory
TwoClass
No. Name Status
1 Board Statutory
2 Committee Non-statutory
3 Honours Non-statutory
4 ICAC Complaints Committee Non-statutory
ThreeClass
No. Name Status
1 ICAC Non-statutory
2 Citizens Non-statutory
3 Corruption statutory
OneClass
No. Name Status
1 Officials Non-statutory
2 Development Non-statutory
TwoClass
No. Name Status
1 Board Statutory
2 Committee Non-statutory
3 Honours Non-statutory
4 ICAC Complaints Committee Non-statutory
ThreeClass
No. Name Status
1 ICAC Non-statutory
2 Citizens Non-statutory
3 Corruption statutory
解决方案 »
- 如何用查询分析器循环建表?
- '+' 附近有语法错误?
- 请问如何从一张表中同时读出两条不同的记录
- 大虾近来看看:)
- 请各位兄弟指点 select * from ZX_Office_KQ_Inf a where not exists(select 1 from ZX_Office_KQ_Inf where 工号=a.工号 and 流向='返
- 防火墙和SQL Server2005远程登录以及局域网内的Ping问题之间的关系
- 请问这样的SQL语句为何取不到记录??
- 存储过程的参数是条SQL语句
- 执行 'osql -U 用户名 -P 密码 -i c:\***.sql'
- 救急!!!
- sqlserver2005数据库延迟阻塞的问题?
- 数据库无法按文件-文件组备份
不过格式和你这个有区别,你这个格式太扯
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
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'
Goif 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
*/
OneClass
No. Name Status
1 Officials Non-statutory
2 Development Non-statutory
TwoClass
No. Name Status
1 Board Statutory
2 Committee Non-statutory
3 Honours Non-statutory
4 ICAC Complaints Committee Non-statutory
ThreeClass
No. Name Status
1 ICAC Non-statutory
2 Citizens Non-statutory
3 Corruption statutory
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 ParentID,ord),[Name],[Status],ParentID
FROM a2
OPTION(MAXRECURSION 0)
/*NO Name Status ParentID
1 Officials Non-statutory 0
2 Development Non-statutory 0
3 Board Statutory 1
4 Committee Non-statutory 1
5 Honours Non-statutory 1
6 ICAC ComplaintsCommittee Non-statutory 1
7 ICAC Non-statutory 2
8 Citizens Non-statutory 2
9 Corruption statutor 2
*/