2013年9月17日 星期二

查詢時一併列出相關系所

數年前資訊科技產業蓬勃發展,資訊各領域紛紛獨立切割成立新的系所,相關科系在查詢時能將一併顯示,如資工及資料系兩系,不論查詢其中任何一筆資料,兩者均同時顯示。以下將依相關系統定義是否定義於資料表中分別討論:
1.  簡單版本-SQL
OR可額外增加判斷空間(條件),而條件判斷式中之資料則由CASE WHEN所創造,SQL及兩種測試之結果,如下:
#
SQL
結果
1
SELECT *
FROM
     (   
     SELECT '資工系' Dept_Id
     UNION ALL
     SELECT '資科系'
     UNION ALL
     SELECT '財金系'
     UNION ALL
     SELECT '械械系'
     UNION ALL
     SELECT '資管系'
     UNION ALL
     SELECT '企管系'
     ) A
WHERE 1=1
AND (Dept_Id = '資科系-- 判斷式1
   OR Dept_Id = CASE '資科系' -- 判斷式2--重點
                       WHEN '資工系' THEN '資科系'
                       WHEN '資科系' THEN '資工系'
                  END
    )







判斷式1,查詢資科系;判斷式2則利用以CASE WHEN轉換為查詢資工系
2
WHERE 1=1
AND (Dept_Id = '械械系'
   OR Dept_Id = CASE '械械系' --重點
                                       WHEN '資工系' THEN '資科系'
                 WHEN '資科系' THEN '資工系'
                  END
       )

判斷式1,是基本判斷式,不解釋。重點在於判斷式2的產生,將使用簡單式-CASE語法,查詢條件置於CASE判斷式中並以WHEN進行轉換成對應之相關系所,即可產生另一組查詢條件式,兩組判斷式使用OR進行邏輯組合,任何條件成立均可,以此即可同時查詢相關系所之資料。

2.  定義版本-資料表中
前述對應資料將建立成資料表,依關聯定義為一筆(單向)/兩筆(雙向)撰寫出以下2SQL,如下:
#
SQL
說明
1
SELECT COALESCE(R.Dept_Id, D.Dept_Id) Dept_Id
FROM  #Dept D
     LEFT OUTER JOIN #Rel_Dept R
         ON (D.Dept_Id = R.Dept_Id
              OR D.Dept_Id = R.Rel_Dept_Id
)
WHERE 1=1
      AND D.Dept_Id    ='資工系'
ü  適用於兩者關聯之定義為兩筆(資工-資科、資科-資工)。
ü  COALESCEANSI語法,MSSQL可用ISNULLORACLE則為NVL

2
SELECT *
FROM #Dept
WHERE 1=1
     AND Dept_Id='資工系'
UNION--重複值剔除
SELECT CASE WHEN R.Dept_Id = D.Dept_Id THEN
         R.Rel_Dept_Id
       ELSE
         R.Dept_Id
    END
FROM #Rel_Dept R, #Dept D
WHERE 1=1
  AND D.Dept_Id='資工系' 
  AND (D.Dept_Id = R.Dept_Id
           OR D.Dept_Id = R.Rel_Dept_Id
         )
ü  兩者關聯之定義為一筆/兩筆(資工-資科、資科-資工)均適用。

ü  關聯之定義為兩筆時,資料會有重複現象,可使用UNION自動剔除。



資料產生 (MSSQL為例,ORACLE改為CTAS方式即可)
--1.Dept
SELECT '資工系' Dept_Id
        INTO #Dept
UNION ALL
SELECT '資科系'
UNION ALL
SELECT '財金系'
UNION ALL
SELECT '械械系'
UNION ALL
SELECT '企管系'

--2.Rel_Dept

DROP TABLE #Rel_Dept

SELECT '資工系' Dept_Id, '資科系' Rel_Dept_Id
         INTO #Rel_Dept
UNION ALL
SELECT '資科系' Dept_Id, '資工系'
UNION ALL
SELECT '財金系', '企管系'--刻意建立單向對應關係,可嘗試測試

沒有留言:

張貼留言