熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQL中UNION 與 UNION ALL的區別分析

2013-11-15 14:39:29  來源: SQL Server 

  UNION 運算符
  
  將兩個或更多查詢的結果組合為單個結果集該結果集包含聯合查詢中的所有查詢的全部行這與使用聯接組合兩個表中的列不同
  
  使用 UNION 組合兩個查詢的結果集的兩個基本規則是
  
  所有查詢中的列數和列的順序必須相同
  
  數據類型必須兼容
  
    這種多結果的查詢組合為單一結果集在實際中應用的非常方便但在應用中也有有著問題如下實例可以進一步說明問題
  
  問題描述
  
    為了進一步分析與統計企業中關鍵部件的生產進度情況采用了一個表Key_Item_Cal其結構如圖一
  
 

  通過聯合查詢將查詢分為三個方面
  
  提出數據的基礎明細其代碼如下
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   dboH_MORVEQTY_RECVD AS Quantity dboKey_itemStyle AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  Order by Style asc
  
  
 

  提出數據的一級匯總明細其代碼如下
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   SUM(dboH_MORVEQTY_RECVD) AS QuantityMAX(dboKey_itemStyle) AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  GROUP BY dboKey_itemStyledboKey_itemKey_item_name
  
  order by Style
  
  其結果如圖如示
  
  
 

  提出數據的二級匯總明細其代碼如下
  SELECT MAX(dboKey_itemKey_item_name) as Item_Name
  
  SUM(dboH_MORVEQTY_RECVD) as Quantity
  
   dboKey_itemStyle as Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  GROUP BY dboKey_itemStyle
  
  ORDER BY dboKey_itemStyle asc
  
  其結果如圖所示
  
  
 

  總體的設計就如以上所示但通過UNION聯接的時候出現了新的問題通過以下例子就可以看出UNION與UNION ALL的區別
  
  方案一其代碼如下
  
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   dboH_MORVEQTY_RECVD AS Quantity dboKey_itemStyle AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  UNION (
  
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   SUM(dboH_MORVEQTY_RECVD) AS QuantityMAX(dboKey_itemStyle) AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  GROUP BY dboKey_itemStyledboKey_itemKey_item_name
  
  UNION
  
  SELECT MAX(dboKey_itemKey_item_name) as Item_Name SUM(dboH_MORVEQTY_RECVD) as Quantity
  
   dboKey_itemStyle as Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  GROUP BY dboKey_itemStyle)
  
  ORDER BY dboKey_itemStyle asc
  
  GO
  
  其結果如下
  
  
 

  問題
  
    通過以上結果可以發現GR後機架PYGB後機架和PYK後機架的明細與其二級匯總值明顯不符而二級匯總的值是正確為什麼明細與匯總值不符?
  
  針對這個問題我采用了第二種方案
  
  方案二其代碼如下
  
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   dboH_MORVEQTY_RECVD AS Quantity dboKey_itemStyle AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  UNION ALL(
  
  SELECT dboKey_itemKey_item_name AS Item_Name
  
   SUM(dboH_MORVEQTY_RECVD) AS QuantityMAX(dboKey_itemStyle) AS Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  WHERE (dboKey_itemKey_item <> )
  
  GROUP BY dboKey_itemStyledboKey_itemKey_item_name
  
  UNION
  
  SELECT MAX(dboKey_itemKey_item_name) as Item_Name
  
  SUM(dboH_MORVEQTY_RECVD) as Quantity
  
   dboKey_itemStyle as Style
  
  FROM dboKey_item LEFT OUTER JOIN
  
   dboH_MORVE ON dboKey_itemKey_item = dboH_MORVEITEM
  
  GROUP BY dboKey_itemStyle)
  
  ORDER BY dboKey_itemStyle asc
  
  GO
  
  其結果如圖
  
  

  通過以上的例子大家可以看出在UNION與UNION ALL在應用上區別
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22130.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.