iT邦幫忙

1

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

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

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加入人員檔的條件去撈出人員資料?
如果有錯,也麻煩各位鞭小力一點,謝謝

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

你們來問問題,只貼圖跟code, 但是沒資料啊,你叫別人能怎樣幫你?
通靈? 還有你是要查什麼? 你中間自己的就貼張圖,真正想做到什麼查詢,
要設法表達清楚.
其實問題能講清楚,就差不多解決一半了.
jack8900 iT邦新手 5 級 ‧ 2020-05-27 10:53:58 檢舉
不好意思...我已加上部門檔資料了
想要詢問的問題是,部門檔撈出來的部門ID,把所有部門ID當成人員檔的條件,撈出所有相關人員
2
rogeryao
iT邦高手 2 級 ‧ 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

jack8900 iT邦新手 5 級 ‧ 2020-05-27 14:06:50 檢舉

感謝幫忙~~時間快很多!

1
浩瀚星空
iT邦超人 1 級 ‧ 2020-05-27 13:05:53

我這沒mssql的環境,沒辦法直接寫給你。

不過一般遇到這種多參數關聯同一表的。
且參數值達3個以上的。

我大多不是做成view模式。要不然就是從程式內分兩段來處理。
畢竟原本多join的方式很容易造成多重搜表的動作。
也容易造成ram的負擔。

我記得mssql有程序化處理的動作,不太記得了是啥。我對其不熟沒辦法很有效的告訴你。

qpowjohn iT邦新手 5 級 ‧ 2020-06-06 23:12:28 檢舉

stored procedure

我要發表回答

立即登入回答