求助高手：编写一个SQL Server查询语句

Question

实际物理表：单位编码     单位名称 父级单位
DW0100     广东电力
DW0200     北京电力
DW0201     朝阳电力 DW0200
DW020101     朝阳一部 DW0201
DW020102     朝阳二部 DW0201
DW0202     西城电力 DW0200
DW0203     东城电力 DW0200
执行一条查询SQL，生成结果集如下：单位编码     单位名称 父级单位     父子级别
DW0100     广东电力     1
DW0200     北京电力     1
DW0201     朝阳电力 DW0200     2
DW020101     朝阳一部 DW0201     3
DW020102     朝阳二部 DW0201     3
DW0202     西城电力 DW0200     2
DW0203     东城电力 DW0200     2哪位大侠帮忙写出这么一条查询SQL，感激不尽！！

Answer 1

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(单位编码 varchar(8), 单位名称 varchar(8), 父级单位 varchar(8))
insert into #
select 'DW0100', '广东电力', '' union all
select 'DW0200', '北京电力', '' union all
select 'DW0201', '朝阳电力', 'DW0200' union all
select 'DW020101', '朝阳一部', 'DW0201' union all
select 'DW020102', '朝阳二部', 'DW0201' union all
select 'DW0202', '西城电力', 'DW0200' union all
select 'DW0203', '东城电力', 'DW0200';with cte (单位编码,单位名称,父级单位,父子级别) as
(
select left(单位编码,len(单位编码)-2),单位名称,convert(varchar(8),''),1 from # where 父级单位=''
union all
select b.*, a.父子级别+1 from cte a join # b on b.单位编码 like a.单位编码 + '__' where b.父级单位<>''
)
select 单位编码 + case 父子级别 when 1 then '00' else '' end 单位编码,单位名称,父级单位,父子级别 from cte/*
单位编码   单位名称 父级单位 父子级别
---------- -------- -------- -----------
DW0100     广东电力              1
DW0200     北京电力              1
DW0201     朝阳电力     DW0200   2
DW0202     西城电力     DW0200   2
DW0203     东城电力     DW0200   2
DW020101   朝阳一部     DW0201   3
DW020102   朝阳二部     DW0201   3
*/

Answer 2

　非常像　desc_rank() 排名函数　sql server 2005　看结果：
use tempdb;
go
if object_id('dbo.tb') is not null drop table dbo.tb
create table dbo.tb(单位编码 varchar(8), 单位名称 varchar(8), 父级单位 varchar(8))
insert into dbo.tb
select 'DW0100', '广东电力', '' union all
select 'DW0200', '北京电力', '' union all
select 'DW0201', '朝阳电力', 'DW0200' union all
select 'DW020101', '朝阳一部', 'DW0201' union all
select 'DW020102', '朝阳二部', 'DW0201' union all
select 'DW0202', '西城电力', 'DW0200' union all
select 'DW0203', '东城电力', 'DW0200'select *,
   dense_rank()over(order by [父级单位])as[父子级别]
from dbo.tb;
/**
单位编码   单位名称    父级单位  父子级别
-------- -------- -------- --------------------
DW0100   广东电力               1
DW0200   北京电力               1
DW0201   朝阳电力     DW0200   2
DW0202   西城电力     DW0200   2
DW0203   东城电力     DW0200   2
DW020101 朝阳一部     DW0201   3
DW020102 朝阳二部     DW0201   3(7 行受影响)
**/

Answer 3

单位编码   单位名称    父级单位  父子级别
-------- -------- -------- --------------------
DW0100   广东电力               1
DW0200   北京电力               1
DW0201   朝阳电力     DW0200   2
DW0202   西城电力     DW0200   2
DW0203   东城电力     DW0200   2
DW020101 朝阳一部     DW0201   3
DW020102 朝阳二部     DW0201   3
DW020201 西城一部     DW0202   4     // 这里应为3
DW020202 西城二部     DW0202   4     // 这里应为3这个方式挺好的，但还是不能解决根本问题，假如新增两条数据，如上表的最后两条数据的父子级别是错误的

Answer 4

补充一点：单位编码是随意的，并没有固定的编码结构，因此不能根据单位编码的长度计算其相应的级别。只能根据单位编码和父级单位的引用关系进行计算，因此可能要用到递归计算，在oracle中可以使用start with语法，在SQL Server中不知道有没有类似的SQL语法。

Answer 5

那天晕了，正解如下：if object_id('tempdb.dbo.#') is not null drop table #
create table #(单位编码 varchar(8), 单位名称 varchar(8), 父级单位 varchar(8))
insert into #
select 'DW0100', '广东电力', '' union all
select 'DW0200', '北京电力', '' union all
select 'DW0201', '朝阳电力', 'DW0200' union all
select 'DW020101', '朝阳一部', 'DW0201' union all
select 'DW020102', '朝阳二部', 'DW0201' union all
select 'DW0202', '西城电力', 'DW0200' union all
select 'DW0203', '东城电力', 'DW0200';with cte (单位编码,单位名称,父级单位,父子级别) as
(
    select *, 1 from # where 父级单位=''
    union all
    select a.*, b.父子级别+1 from # a join cte b on a.父级单位=b.单位编码
)
select * from cte/*
单位编码   单位名称 父级单位 父子级别
---------- -------- -------- -----------
DW0100     广东电力              1
DW0200     北京电力              1
DW0201     朝阳电力     DW0200   2
DW0202     西城电力     DW0200   2
DW0203     东城电力     DW0200   2
DW020101   朝阳一部     DW0201   3
DW020102   朝阳二部     DW0201   3
*/