select col1=tb1.name,col2=tb2.name from tb tb1,tb tb2 where tb1.class=0 and tb1.id=tb2.class
USE test GO-->生成表tbif object_id('tb') is not null drop table tb Go Create table tb([ID] smallint,[Name] nvarchar(4),[CLASS] nvarchar(1)) Insert into tb Select 1,N'营业收入',N'0' Union all Select 2,N'营业支出',N'0' Union all Select 3,N'香烟',N'1' Union all Select 4,N'白酒',N'1' Union all Select 5,N'娃哈哈',N'1' Union all Select 6,N'电费',N'2' Union all Select 7,N'水遇',N'2' Union all Select 8,N'其他',N'6';WITH t AS ( SELECT 0 AS row,Name AS PatentName,tb.* FROM tb WHERE CLASS=0 UNION ALL SELECT row+1,t.PatentName,tb.* FROM tb INNER JOIN t ON tb.CLASS=t.ID )SELECT PatentName AS 大项,Name AS 子项 FROM t WHERE PatentName=N'营业收入' AND row>0 /* 大项 子项 ---- ---- 营业收入 香烟 营业收入 白酒 营业收入 娃哈哈*/
还要附加一个问题,就是。假如ID就一个大项下面没有子项 ID NAME CLASS 22 借款 0也要表示出一项怎么办? 营业收入 香烟 营业收入 白酒 营业收入 娃哈哈 借款
USE test GO-->生成表tbif object_id('tb') is not null drop table tb Go Create table tb([ID] smallint,[Name] nvarchar(50),[CLASS] smallint) Insert into tb Select 1,N'营业收入',0 Union all Select 2,N'营业支出',0 Union all Select 3,N'香烟',1 Union all Select 4,N'白酒',1 Union all Select 5,N'娃哈哈',1 Union all Select 6,N'电费',2 Union all Select 7,N'水遇',2 Union all Select 8,N'其他',6 Union all Select 9,N'借款',0;WITH t AS ( SELECT 0 AS row,Name AS PatentName,tb.ID,CAST('' AS NVARCHAR(50)) AS Name,CLASS FROM tb WHERE CLASS=0 UNION ALL SELECT row+1,t.PatentName,tb.* FROM tb INNER JOIN t ON tb.CLASS=t.ID )SELECT PatentName AS 大项,Name AS 子项 FROM t WHERE (PatentName=N'营业收入' OR PatentName='借款') AND (row>0 OR NOT EXISTS(SELECT 1 FROM tb AS x WHERE x.ID>t.ID AND t.ID=x.CLASS))
where tb1.class=0 and tb1.id=tb2.class
GO-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([ID] smallint,[Name] nvarchar(4),[CLASS] nvarchar(1))
Insert into tb
Select 1,N'营业收入',N'0'
Union all Select 2,N'营业支出',N'0'
Union all Select 3,N'香烟',N'1'
Union all Select 4,N'白酒',N'1'
Union all Select 5,N'娃哈哈',N'1'
Union all Select 6,N'电费',N'2'
Union all Select 7,N'水遇',N'2'
Union all Select 8,N'其他',N'6';WITH t AS (
SELECT 0 AS row,Name AS PatentName,tb.* FROM tb
WHERE CLASS=0
UNION ALL
SELECT row+1,t.PatentName,tb.* FROM tb
INNER JOIN t ON tb.CLASS=t.ID
)SELECT PatentName AS 大项,Name AS 子项 FROM t
WHERE PatentName=N'营业收入'
AND row>0
/*
大项 子项
---- ----
营业收入 香烟
营业收入 白酒
营业收入 娃哈哈*/
ID NAME CLASS
22 借款 0也要表示出一项怎么办?
营业收入 香烟
营业收入 白酒
营业收入 娃哈哈
借款
GO-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([ID] smallint,[Name] nvarchar(50),[CLASS] smallint)
Insert into tb
Select 1,N'营业收入',0
Union all Select 2,N'营业支出',0
Union all Select 3,N'香烟',1
Union all Select 4,N'白酒',1
Union all Select 5,N'娃哈哈',1
Union all Select 6,N'电费',2
Union all Select 7,N'水遇',2
Union all Select 8,N'其他',6
Union all Select 9,N'借款',0;WITH t AS (
SELECT 0 AS row,Name AS PatentName,tb.ID,CAST('' AS NVARCHAR(50)) AS Name,CLASS FROM tb
WHERE CLASS=0
UNION ALL
SELECT row+1,t.PatentName,tb.* FROM tb
INNER JOIN t ON tb.CLASS=t.ID
)SELECT PatentName AS 大项,Name AS 子项 FROM t
WHERE (PatentName=N'营业收入' OR PatentName='借款')
AND (row>0 OR NOT EXISTS(SELECT 1 FROM tb AS x WHERE x.ID>t.ID AND t.ID=x.CLASS))
/*
大项 子项
--------- -------
借款
营业收入 香烟
营业收入 白酒
营业收入 娃哈哈
*/
on tb1.id=tb2.class