SQLServer中如何根据一个城市码找出其下的所有城市？
层次结构如下：

浙江省-杭州市-西湖区

输入的参数可以是1100、1110、1111中的随便一个，返回值为改代码。

比如输入1100的话就返回1111、1112、1113

输入1110返回1111、1112

输入1111返回1111。

请该怎么样用sql语句实现？


城市 城市码 上一级代码
浙江省 1100 -1
杭州市 1110 1100
西湖区 1111 1110
上城区 1112 1110
金华市 1120 1100
下城区 1113 1120

[最优解释]

----------------------------
-- Author  :TravyLee(物是人非事事休,欲语泪先流!)
-- Date    :2012-11-28 09:36:30
-- Version:

--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 

--Feb 10 2012 19:13:17 

--Copyright (c) Microsoft Corporation

--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
go 
create table [test]([城市] varchar(6),[城市码] int,[上一级代码] int)
insert [test]
select '浙江省',1100,-1 union all
select '杭州市',1110,1100 union all
select '西湖区',1111,1110 union all
select '上城区',1112,1110 union all
select '金华市',1120,1100 union all
select '下城区',1113,1120

select * from [test]
go

declare @上一级代码 int
set @上一级代码=1110
select
*
from
test a
where
exists(select 1 from test b where a.上一级代码=b.城市码)
and case when exists (select 1 from test where 上一级代码=@上一级代码 ) 
then 上一级代码 else [城市码] end=@上一级代码
/*
城市     城市码         上一级代码
------ ----------- -----------
西湖区    1111        1110
上城区    1112        1110

(2 行受影响)


*/

[其他解释]
with cte as
(
select 城市码 from tb where 上一级代码=1100
union all
select a.城市码 from tb a join cte b on a.上一级代码=b.城市码
)

select * from cte
[其他解释]
存储过程
[其他解释]

引用:

SQL code?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152------------------------------ Author :TravyLe……

你好，你这个输入1110的时候是可以返回1111和1112，但是输入1100的话返回的是1110，1120。
我的意思是输入1100返回的应该是1111，1112，1113。
[其他解释]

USE tempdb
GO

-- 建立演示环境
CREATE TABLE Dept(
 id int PRIMARY KEY,  
 
 parent_id int,
 name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO

-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
 -- 定位点成员
 SELECT * FROM Dept
 WHERE name = @Dept_name
 UNION ALL
 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
 SELECT A.*
 FROM Dept A, DEPTS B
 WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO

-- 删除演示环境
DROP TABLE Dept

----CTE的综合应用

USE tempdb
GO

-- 建立演示环境
CREATE TABLE Dept(
 id int PRIMARY KEY, 
 parent_id int,
 name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO

-- 查询指定部门下面的所有部门, 并汇总各部门的下级部门数
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(   -- 查询指定部门及其下的所有子部门
 -- 定位点成员
 SELECT * FROM Dept
 WHERE name = @Dept_name
 UNION ALL
 -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
 SELECT A.*
 FROM Dept A, DEPTS B
 WHERE A.parent_id = B.id
),
DEPTCHILD AS(  -- 引用第1个CTE,查询其每条记录对应的部门下的所有子部门
 SELECT 
  Dept_id = P.id, C.id, C.parent_id
 FROM DEPTS P, Dept C
 WHERE P.id = C.parent_id
 UNION ALL
 SELECT 
  P.Dept_id, C.id, C.parent_id
 FROM DEPTCHILD P, Dept C
 WHERE P.id = C.parent_id
),
DEPTCHILDCNT AS( -- 引用第2个CTE, 汇总得到各部门下的子部门数
 SELECT 
  Dept_id, Cnt = COUNT(*)
 FROM DEPTCHILD
 GROUP BY Dept_id
)
SELECT    -- JOIN第1,3个CTE,得到最终的查询结果
 D.*,
 ChildDeptCount = ISNULL(DS.Cnt, 0)
FROM DEPTS D
 LEFT JOIN DEPTCHILDCNT DS
  ON D.id = DS.Dept_id
GO

-- 删除演示环境
DROP TABLE Dept



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/19/4569529.aspx



自己改去