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
其结果如图如示
３提出数据的二级汇总明细其代码如下
SELECTMAX(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在应用上区别
