一般情况下数据库树形结构:DECLARE @T TABLE(ID INT,[NAME] NVARCHAR(20),PARENTID INT) INSERT INTO @T SELECT 1,'广东',NULL UNION ALL SELECT 2,'深圳',1 UNION ALL SELECT 3,'南山',2 UNION ALL SELECT 4,'安徽',NULL UNION ALL SELECT 5,'安庆',4 UNION ALL SELECT 6,'潜山',5 SELECT * FROM @T
Create Table #Directory ( ID int ,Name nVarChar(200) ,ParentID Int ) GoInsert #Directory Select 1,1,null Union Select 2,2,1 Union Select 3,3,2 Union Select 4,4,2 Union Select 5,5,3Go With Dir(ID,ParentID,Name,LV) As ( Select Directory.ID,Directory.ParentID,Directory.Name,0 From #Directory Directory Where Directory.ID =5 Union All Select D2.ID,D2.ParentID,D2.Name,D.LV +1 From #Directory D2 Inner Join Dir D On D2.ID =D.ParentID ) Select * From Dir
3Q,初学sql 能否详解下Select 1,1,null Union Select 2,2,1是什么意思 select我知道是查询,不知道后面的11null,221是什么
http://www.w3school.com.cn/sql/sql_union.asp 你查看下 UNION 操作符用于合并两个或多个 SELECT 语句的结果集 Union和做distinct操作 Union ALL 不会
INSERT INTO @T
SELECT 1,'广东',NULL
UNION ALL
SELECT 2,'深圳',1
UNION ALL
SELECT 3,'南山',2
UNION ALL
SELECT 4,'安徽',NULL
UNION ALL
SELECT 5,'安庆',4
UNION ALL
SELECT 6,'潜山',5
SELECT * FROM @T
(
ID int
,Name nVarChar(200)
,ParentID Int
)
GoInsert #Directory Select 1,1,null
Union Select 2,2,1
Union Select 3,3,2
Union Select 4,4,2
Union Select 5,5,3Go
With Dir(ID,ParentID,Name,LV)
As
(
Select Directory.ID,Directory.ParentID,Directory.Name,0 From #Directory Directory Where Directory.ID =5
Union All
Select D2.ID,D2.ParentID,D2.Name,D.LV +1 From #Directory D2 Inner Join Dir D On D2.ID =D.ParentID
)
Select * From Dir
3Q,初学sql 能否详解下Select 1,1,null
Union Select 2,2,1是什么意思 select我知道是查询,不知道后面的11null,221是什么
http://www.w3school.com.cn/sql/sql_union.asp
你查看下
UNION 操作符用于合并两个或多个 SELECT 语句的结果集
Union和做distinct操作
Union ALL 不会