关于子部门和上级部门关联问题

Question

有一个人事表employee,如下：
emp_name   depart_id
杨晴         06301
张红英       06301
杜秋         06302
陈艳雪       06302
有一个部门表depart,如下：
depart_id    uper_depart_id  depart_name
063              001           办公楼
06301           063            人事部
06302           063            企划部
我想把上面两个表关联成下表的形式：
emp_name   uper_depart_id    depart_name
 杨晴          063             办公楼
张红英         063             办公楼
杜秋           063             办公楼
陈艳雪         063             办公楼
还请各位帮忙写一下sql语句，多谢先！     

Answer 1

SELECT a.* ,b.[depart_name]
FROM employee AS a 
INNER JOIN depart AS b ON a.[depart_id] LIKE  b.[depart_id]+'%'
WHERE NOT EXISTS(SELECT 1 FROM depart WHERE a.[depart_id] LIKE  [depart_id]+'%' AND [depart_id]<b.[depart_id])

Answer 2

select
  a.emp_name ,b.uper_depart_id ,b.depart_name
from
  employee a join depart b
on
  a.depart_id=b.depart_id

Answer 3

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#employee') is null
drop table #employee
Go
Create table #employee([emp_name] nvarchar(3),[depart_id] nvarchar(5))
Insert #employee
select N'杨晴',N'06301' union all
select N'张红英',N'06301' union all
select N'杜秋',N'06302' union all
select N'陈艳雪',N'06302'
Goif not object_id(N'Tempdb..#depart') is null
drop table #depart
Go
Create table #depart([depart_id] nvarchar(5),[uper_depart_id] nvarchar(3),[depart_name] nvarchar(3))
Insert #depart
select N'063',N'001',N'办公楼' union all
select N'06301',N'063',N'人事部' union all
select N'06302',N'063',N'企划部'
GoSELECT a.[emp_name],c.[uper_depart_id],b.[depart_name]
FROM #employee AS a 
INNER JOIN #depart AS b ON a.[depart_id] LIKE  b.[depart_id]+'%'
INNER JOIN #depart AS c ON c.[depart_id]=a.[depart_id]
WHERE NOT EXISTS(SELECT 1 FROM #depart WHERE a.[depart_id] LIKE  [depart_id]+'%' AND [depart_id]<b.[depart_id])/*
emp_name uper_depart_id depart_name
杨晴 063 办公楼
张红英 063 办公楼
杜秋 063 办公楼
陈艳雪 063 办公楼
*/

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-28 14:00:32
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[employee]
if object_id('[employee]') is not null drop table [employee]
go 
create table [employee]([emp_name] varchar(6),[depart_id] varchar(5))
insert [employee]
select '杨晴','06301' union all
select '张红英','06301' union all
select '杜秋','06302' union all
select '陈艳雪','06302'
--> 测试数据：[depart]
if object_id('[depart]') is not null drop table [depart]
go 
create table [depart]([depart_id] varchar(5),[uper_depart_id] varchar(3),[depart_name] varchar(6))
insert [depart]
select '063','001','办公楼' union all
select '06301','063','人事部' union all
select '06302','063','企划部'
--------------开始查询--------------------------
;with f as
(
select * from depart a where not exists(select 1 from depart where depart_id=a.uper_depart_id)
union all
select a.* from depart a join f on a.depart_id=f.uper_depart_id
),
f1 as
(
select
  a.emp_name ,b.uper_depart_id ,b.depart_name
from
  employee a join depart b
on
  a.depart_id=b.depart_id
)select
   a.emp_name,a.uper_depart_id,b. depart_name
 from
   f1 a join f b
 on
   a.uper_depart_id=b.depart_id
----------------结果----------------------------
/* emp_name uper_depart_id depart_name
-------- -------------- -----------
杨晴       063            办公楼
张红英      063            办公楼
杜秋       063            办公楼
陈艳雪      063            办公楼(4 行受影响)
*/

Answer 5

这个题应该用递归的方法来处理才会比较完美（比如depart_id编码无规律时）。目前roy_88的方法在处理编码有规律的数据还是OK的，而小F在处理多层数据时（只需再加一层就知道了），数据不完整！