T-SQL-重複使用Left Join取同一張表單的資料，求更好的解法

sql ms sql sqlserver

jack8900 2020-05-27 09:05:23 ‧ 4469 瀏覽

分享至

我需要撈取部門的上下階關係，當成條件去撈取人員資料，部門檔資料內容如下

declare @dep as table
(
	id varchar(4),
	name nvarchar(10),
	upperid varchar(4)
)

insert into @dep
values
('1403','A','4005'),
('1406','B','2101'),
('1700','C','2101'),
('1701','D','1700'),
('1705','E','1403'),
('1711','F','3008'),
('1715','G','3008'),
('1716','H','3002'),
('1717','I','3002'),
('1722','J','3002'),
('1724','K','3002'),
('1725','L','7120'),
('1731','M','3398'),
('1732','N','2101'),
('1738','O','3005'),
('1739','P','3005'),
('1740','Q','3398'),
('1741','R','3398'),
('1742','S','3007'),
('1744','T','3007'),
('1745','U','3007'),
('1746','V','3007'),
('1756','W','3007'),
('1758','X','3007'),
('1760','Y','3007'),
('1773','Z','1725'),
('1782','AA','1725'),
('1787','AB','1700'),
('1795','AC','1403'),
('1799','AD','1700'),
('1804','AE','9999'),
('1807','AF','1725'),
('1808','AG','1406'),
('3007','AH','4005'),
('2101','AI','9999'),
('9999','AJ',''),
('3008','AK','2101'),
('3002','AL','2101'),
('7120','AM','3012'),
('3012','AN','9999'),
('3398','AO','3012'),
('3005','AP','2101'),
('3007','AQ','4005'),
('4005','AR','9999')

select
ISNULL(dep1.id,'') dep1,ISNULL(dep2.id,'') dep2,ISNULL(dep3.id,'') dep3
,ISNULL(dep4.id,'') dep4,ISNULL(dep5.id,'') dep5,ISNULL(dep6.id,'') dep6
,ISNULL(dep1.name,'') depname1,ISNULL(dep2.name,'') depname2,ISNULL(dep3.name,'') depname3
,ISNULL(dep4.name,'') depname4,ISNULL(dep5.name,'') depname5,ISNULL(dep6.name,'') depname6
from @dep dep1
left join @dep dep2 on dep2.upperid=dep1.id
left join @dep dep3 on dep3.upperid=dep2.id
left join @dep dep4 on dep4.upperid=dep3.id
left join @dep dep5 on dep5.upperid=dep4.id
left join @dep dep6 on dep6.upperid=dep5.id
where dep1.id='9999'

上面的SQL是我目前想到可以串出部門的階層關係的方法，改變dep1.id的資料決定最上層，由上往下找

最後在JOIN人員檔去撈出所屬的人員

select distinct emp.* 
from
(
	select
	ISNULL(dep1.id,'') dep1,ISNULL(dep2.id,'') dep2,ISNULL(dep3.id,'') dep3
	,ISNULL(dep4.id,'') dep4,ISNULL(dep5.id,'') dep5,ISNULL(dep6.id,'') dep6
	,ISNULL(dep1.name,'') depname1,ISNULL(dep2.name,'') depname2,ISNULL(dep3.name,'') depname3
	,ISNULL(dep4.name,'') depname4,ISNULL(dep5.name,'') depname5,ISNULL(dep6.name,'') depname6
	from @dep dep1
	left join @dep dep2 on dep2.upperid=dep1.id
	left join @dep dep3 on dep3.upperid=dep2.id
	left join @dep dep4 on dep4.upperid=dep3.id
	left join @dep dep5 on dep5.upperid=dep4.id
	left join @dep dep6 on dep6.upperid=dep5.id
)a
left join emp on (emp.id=a.dep1 or emp.id=a.dep2 or emp.id=a.dep3 
or emp.id=a.dep4 or emp.id=a.dep5 or emp.id=a.dep6)

上面的做法，在資料量少或部門的層級不多的時候，速度都還OK，大概1秒上下可以跑完，但是資料量大之後時間就會拉長。

想請問各位上面的方式有沒有更好的方式把部門的ID加入人員檔的條件去撈出人員資料?
如果有錯，也麻煩各位鞭小力一點，謝謝

補充:
如果還有缺什麼東西，我再補上

一級屠豬士 iT邦大師 1 級 ‧ 2020-05-27 09:49:27 檢舉

你們來問問題,只貼圖跟code, 但是沒資料啊,你叫別人能怎樣幫你?
通靈? 還有你是要查什麼? 你中間自己的就貼張圖,真正想做到什麼查詢,
要設法表達清楚.
其實問題能講清楚,就差不多解決一半了.

jack8900 iT邦新手 1 級 ‧ 2020-05-27 10:53:58 檢舉

不好意思...我已加上部門檔資料了
想要詢問的問題是，部門檔撈出來的部門ID，把所有部門ID當成人員檔的條件，撈出所有相關人員

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

rogeryao

iT邦超人 7 級 ‧ 2020-05-27 09:47:54

最佳解答

with temp as
(
 select id
 -- ,upperid,name 
 from dep 
 where id='2101' -- 由 2101 往下展
 union all
 select b.id
 -- ,b.upperid,b.name 
 from temp a,dep b
 where b.upperid=a.id 
)
select * from temp
-- 不含 2101
where id<>'2101'
order by id;

Demo