id fullname name ---------------------------------------------- 3 河北省 河北省 147 河北省/邢台市 邢台市 149 河北省/邢台市/桥东区 桥东区 150 河北省/邢台市/桥西区 桥西区这样效果又怎么实现呢?
如果需要筛选,在最后语句中加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 河北省/邢台市/隆尧县 隆尧县 */
;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
id fullname name
----------------------------------------------
3 河北省 河北省
147 河北省/邢台市 邢台市
149 河北省/邢台市/桥东区 桥东区
150 河北省/邢台市/桥西区 桥西区这样效果又怎么实现呢?
-- 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 河北省/邢台市/隆尧县 隆尧县
*/
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
“WITH cte AS” 这里的这个"cte“改成其它的,也不影响呀。