SQL Server 刪除重複的欄位

重複有2種,一種是很簡單的完全重複
這種情況就只要利用關鍵字 DISTINCT
就可以排除重複值,就不贅敘:


select distinct ...

另一種情況是比較特殊,可能有多個欄位重複
但某些欄位為特定的,用圖說明比較快:

SELECT * FROM Browser_WebSite_Travel AS BB
WHERE BB.BrTravel_Version = 'zh-tw' and 
      BB.BrTravel_CNAME = '王功漁火'


如上圖,會選出名為王功漁火,語系為中文的景點
但不同點在Group欄,我希望可以將其結合為一筆,以逗號隔開
要如何讓他執行我想要的結果呢??




這時可以結合子查詢,並下 for xml path('') 指令

SELECT 
(
  SELECT  AA.BrTravel_Group+',' 
  FROM    Browser_WebSite_Travel AS AA
  WHERE AA.BrTravel_Version = 'zh-tw' and 
    AA.BrTravel_CNAME = '王功漁火'
     FOR XML PATH('')
)AS BrTravel_Total_Group,
* 
FROM Browser_WebSite_Travel AS BB
WHERE BB.BrTravel_Version = 'zh-tw' and 
      BB.BrTravel_CNAME = '王功漁火'

注意的是子查詢的條件,與外面的條件要一致,結果如下:


這樣是結合在一起,但是我只要單獨一個結果啊...
本人試過Distinct,group by 2者都無法獨立出來
後來想到可以用CTE來試試,結果挺令我滿意

WITH T1 --將各館結合為一個欄位
AS
(
 SELECT 
 (
   SELECT  AA.BrTravel_Group+',' 
   FROM    Browser_WebSite_Travel AS AA
   WHERE AA.BrTravel_Version = 'zh-tw' and 
     AA.BrTravel_CNAME = '王功漁火'
   FOR XML PATH('')
 )AS BrTravel_Total_Group,
 * 
 FROM Browser_WebSite_Travel AS BB
 WHERE BB.BrTravel_Version = 'zh-tw' and 
    BB.BrTravel_CNAME = '王功漁火'
), T2 --排除其他重複列
AS(
 SELECT MIN(BrTravel_SNO) AS autoSNO
 FROM T1 
 GROUP BY BrTravel_Total_Group
)
SELECT * --從T1中選出對應T2的唯一值,如此「給合欄位」並「排除重複值」即可實現
FROM T1 
WHERE BrTravel_SNO IN(SELECT autoSNO FROM T2)

結果如下:



目前僅實現單一景點的排除,現在要做的事
所有景點都排除,
其實道理相同
在T1景點名稱子查詢中,先進行對應即可:

WITH T1
AS
(
 SELECT 
 (
   SELECT  AA.BrTravel_Group+',' 
   FROM    Browser_WebSite_Travel AS AA
   WHERE AA.BrTravel_Version = 'zh-tw' and
    AA.BrTravel_CNAME = bb.BrTravel_CNAME --T1表中進行名稱對應
   FOR XML PATH('')
 )AS BrTravel_Total_Group,
 * 
 FROM Browser_WebSite_Travel AS BB
 WHERE BB.BrTravel_Version = 'zh-tw'
), T2
AS(
 SELECT MIN(BrTravel_SNO) AS autoSNO
 FROM T1 
 GROUP BY BrTravel_CNAME
)
SELECT * 
FROM T1 
WHERE BrTravel_SNO IN(SELECT autoSNO FROM T2)

結果如下,相當滿意:


如此即完成任務,若有語系切換,轉換條件即可

留言

熱門文章