求一SQL语句，关于省市区这样树状结构的.

数据如下:
表名： Area
id                   name                                               pid
-------------------- -------------------------------------------------- --------------------
3                    河北省                                                0
147                  邢台市                                                3
149                  桥东区                                                147
150                  桥西区                                                147
151                  邢台县                                                147
152                  临城县                                                147
153                  内丘县                                                147
154                  柏乡县                                                147
155                  隆尧县                                                147

根据网上的资料我已实现  "查询树状结构某节点的上级所有根节点" 和  "--查询树状结构某节点下的所有子节点"


但我想要这样的查询效果：
1、
id              name 
3 河北省
147 河北省/邢台市
149 河北省/邢台市/桥东区
150 河北省/邢台市/桥西区

就是某一id下面的所有数据，并且name可以这样串起来，

2、某一id的上级数据，name也可以串起来。

12 个解决方案

#1

id fullname name
----------------------------------------------
3 河北省 河北省
147 河北省/邢台市 邢台市
149 河北省/邢台市/桥东区 桥东区
150 河北省/邢台市/桥西区 桥西区

这样效果又怎么实现呢？

#2

如果需要筛选，在最后语句中加where即可

----------------------------------------------------------------
-- Author  :DBA_HuangZJ(發糞塗牆)
-- Date    :2014-07-31 11:22:21
-- Version:
--      Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
--	May 14 2014 18:34:29 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[Area]
if object_id('[Area]') is not null drop table [Area]
go 
create table [Area]([id] int,[name] varchar(6),[pid] int)
insert [Area]
select 3,'河北省',0 union all
select 147,'邢台市',3 union all
select 149,'桥东区',147 union all
select 150,'桥西区',147 union all
select 151,'邢台县',147 union all
select 152,'临城县',147 union all
select 153,'内丘县',147 union all
select 154,'柏乡县',147 union all
select 155,'隆尧县',147
--------------开始查询--------------------------
;WITH cte AS (
select * ,CAST(name AS VARCHAR(100)) AS fullname
from [Area]
WHERE pid=0
UNION ALL 
SELECT a.id,a.NAME,a.pid,CAST(b.fullname+'/'+a.NAME AS VARCHAR(100)) AS FULLname
FROM [AREA] a INNER JOIN cte b ON a.pid=b.id)
SELECT id,fullname,name
FROM cte

----------------结果----------------------------
/* 
id          fullname                                                                                             name
----------- ---------------------------------------------------------------------------------------------------- ------
3           河北省                                                                                                  河北省
147         河北省/邢台市                                                                                              邢台市
149         河北省/邢台市/桥东区                                                                                          桥东区
150         河北省/邢台市/桥西区                                                                                          桥西区
151         河北省/邢台市/邢台县                                                                                          邢台县
152         河北省/邢台市/临城县                                                                                          临城县
153         河北省/邢台市/内丘县                                                                                          内丘县
154         河北省/邢台市/柏乡县                                                                                          柏乡县
155         河北省/邢台市/隆尧县                                                                                          隆尧县
*/

#3

能说说为什么不能把条件加在  WHERE pid=0 这里，这样查询的时候 ，能缩小范围呀。

#4

在CTE中，WHERE pid=0 是用来作为“锚点”，也就是递归的其实点，实际上不是用来筛选数据的。

#5

中国的省市区实际上几千个，不会太多的

#6

;WITH f AS (
select * ,CAST(name AS VARCHAR(100)) AS fullname
from [Area] as a
WHERE not exists(select 1 from area where id=a.pid) --这样直接取到顶层数据
UNION ALL 
SELECT a.id,a.NAME,a.pid,CAST(b.fullname+'/'+a.NAME AS VARCHAR(100)) AS FULLname
FROM [AREA] a INNER JOIN cte b ON a.pid=b.id)
SELECT id,fullname,name
FROM f

#7

用CTE递归，版主V5

#8

引用 2 楼 DBA_Huangzj 的回复:

--------------开始查询--------------------------
;WITH cte AS (
select * ,CAST(name AS VARCHAR(100)) AS fullname
from [Area]
WHERE pid=0
UNION ALL 
SELECT a.id,a.NAME,a.pid,CAST(b.fullname+'/'+a.NAME AS VARCHAR(100)) AS FULLname
FROM [AREA] a INNER JOIN cte b ON a.pid=b.id)
SELECT id,fullname,name
FROM cte

[/code]

学习了，貌似可用于物料解BOM額

#9

引用 4 楼 DBA_Huangzj 的回复:

在CTE中，WHERE pid=0 是用来作为“锚点”，也就是递归的其实点，实际上不是用来筛选数据的。

“WITH cte AS” 这里的这个"cte“改成其它的，也不影响呀。

#10

可以改，但是一旦改了，后续所有CTE都要替换

#11

版主V5，学习了！

#12

多谢各位的支持，版主V5，下班结贴了。