聽說幸福…在山的那邊 (浪機子)

  ##使用在組織的判斷上，非常的方便##

  例如，組織檔的裡有上一階層的組織，如何算出某組織下有多少個單位

  Organ 組織檔  Organ = A  下有 Organ AA BB CC DD , AA組織下有AAA BBB CCC

  檔案的架構會是

  Organ = AA , Uporgan = A

  Organ = BB , Uporgan = A

  Organ = BB , Uporgan = A

  Organ = AAA , Uporgan = AA

  Organ = BBB , Uporgan = AA

  Organ = CCC , Uporgan = AA

  --------------------------------------------------------------------

  WITH tmpOrgan AS
  (
   SELECT OrganID,OrganName, 1 AS treelevel
   FROM Organ
   WHERE  OrganID = 'A'
   UNION ALL
   SELECT o.OrganID,o.OrganName,treelevel +1
   FROM Organ o
   JOIN tmpOrgan o1
   ON o1.OrganID = o.UpOrganID 
  )

 ##要把資料存到另一個 table 中，某則算出來的資料在程式下方要使用的話，就會不見囉##

  INSERT INTO @TABLE (OrganID,OrganName,treelevel) 
  SELECT OrganID,OrganName,treelevel FROM tmpOrgan

--------------------------------------------------------------------

 ##得到的資料##

OrganID OrganName treelevel
A             A部               1
AA          AA科             2
BB          BB科              2
CC          CC科             2
AAA       AAA組           3
BBB        BBB組           3
CCC        CCC組          3