UNION 運算符
將兩個或更多查詢的結果組合為單個結果集
該結果集包含聯合查詢中的所有查詢的全部行
這與使用聯接組合兩個表中的列不同
使用 UNION 組合兩個查詢的結果集的兩個基本規則是
所有查詢中的列數和列的順序必須相同
數據類型必須兼容
這種多結果的查詢組合為單一結果集在實際中應用的非常方便
但在應用中也有有著問題
如下實例可以進一步說明問題
問題描述
為了進一步分析與統計企業中關鍵部件的生產進度情況
采用了一個表Key_Item_Cal
其結構如圖一
通過聯合查詢
將查詢分為三個方面
1提出數據的基礎明細其代碼如下 SELECT dbo
Key_item
Key_item_name AS Item_Name
dbo
H_MORVE
QTY_RECVD
AS Quantity
dbo
Key_item
Style AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
Order by Style asc
2提出數據的一級匯總明細其代碼如下 SELECT dbo
Key_item
Key_item_name AS Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) AS Quantity
MAX(dbo
Key_item
Style) AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
GROUP BY dbo
Key_item
Style
dbo
Key_item
Key_item_name
order by Style
其結果如圖如示
3提出數據的二級匯總明細其代碼如下 SELECT MAX(dbo
Key_item
Key_item_name) as Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) as Quantity
dbo
Key_item
Style as Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
GROUP BY dbo
Key_item
Style
ORDER BY dbo
Key_item
Style asc
其結果如圖所示
總體的設計就如以上所示
但通過UNION聯接的時候出現了新的問題
通過以下例子
就可以看出UNION與UNION ALL的區別
方案一
其代碼如下
SELECT dbo
Key_item
Key_item_name AS Item_Name
dbo
H_MORVE
QTY_RECVD
AS Quantity
dbo
Key_item
Style AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
UNION (
SELECT dbo
Key_item
Key_item_name AS Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) AS Quantity
MAX(dbo
Key_item
Style) AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
GROUP BY dbo
Key_item
Style
dbo
Key_item
Key_item_name
UNION
SELECT MAX(dbo
Key_item
Key_item_name) as Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) as Quantity
dbo
Key_item
Style as Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
GROUP BY dbo
Key_item
Style)
ORDER BY dbo
Key_item
Style asc
GO
其結果如下
問題
通過以上結果
可以發現GR
後機架
PY
G
B後機架和PY
K
後機架的明細與其二級匯總值明顯不符
而二級匯總的值是正確
為什麼明細與匯總值不符?
針對這個問題
我采用了第二種方案
方案二
其代碼如下
SELECT dbo
Key_item
Key_item_name AS Item_Name
dbo
H_MORVE
QTY_RECVD
AS Quantity
dbo
Key_item
Style AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
UNION ALL(
SELECT dbo
Key_item
Key_item_name AS Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) AS Quantity
MAX(dbo
Key_item
Style) AS Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
WHERE (dbo
Key_item
Key_item <>
)
GROUP BY dbo
Key_item
Style
dbo
Key_item
Key_item_name
UNION
SELECT MAX(dbo
Key_item
Key_item_name) as Item_Name
SUM(dbo
H_MORVE
QTY_RECVD
) as Quantity
dbo
Key_item
Style as Style
FROM dbo
Key_item LEFT OUTER JOIN
dbo
H_MORVE ON dbo
Key_item
Key_item = dbo
H_MORVE
ITEM
GROUP BY dbo
Key_item
Style)
ORDER BY dbo
Key_item
Style asc
GO
其結果如圖
通過以上的例子
大家可以看出在UNION與UNION ALL在應用上區別
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22130.html