MYSQL UNION - 产生意外结果
问题描述:
我有以下MySQL查询MYSQL UNION - 产生意外结果
MYSQL
SELECT SUM(shop_order_price), p.category
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category = 'Mens'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
UNION ALL
SELECT SUM(shop_order_price), p.category
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category= 'Ladies'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
UNION ALL
SELECT SUM(shop_order_price), p.category
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
将会产生以下结果:
结果
SUM(shop_order_price) category
-------------------- ---------
282.5 Mens
751 Ladies
1033.5 Ladies
SUM数字是正确的,但是,底部的行在类别列中显示'Ladies',即使在查询中没有指定类别。
我的问题是我可以得到这最后一行显示TOTAL
,而不是Ladies
?所以如下结果会出现:
预期结果
SUM(shop_order_price) category
-------------------- ---------
282.5 Mens
751 Ladies
1033.5 TOTAL
任何意见将是巨大的。
答
您可以使用GROUP BY
与WITH ROLLUP
修改,简化您的查询:
SELECT SUM(shop_order_price), p.category
FROM shop_orders so
INNER JOIN product p
ON so.shop_order_product_id = p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category IN ('Mens', 'Ladies')
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05'
AND so.shop_order_year = '2014'
GROUP BY p.category
WITH ROLLUP ;
如果你想不显示NULL
但'TOTAL'
,这个最好是在应用程序方面。你可以在SQL中做到这一点,将查询包装在一个派生表中 - 但我不会使用它:
SELECT
sum_order_price,
COALESCE(category, 'TOTAL') AS category
FROM
(SELECT SUM(shop_order_price) AS sum_order_price,
p.category
FROM shop_orders so
INNER JOIN product p
ON so.shop_order_product_id = p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category IN ('Mens', 'Ladies')
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05'
AND so.shop_order_year = '2014'
GROUP BY p.category
WITH ROLLUP
) AS dt ;
答
这不是一个优雅的解决方案,但你可以试试这个。它没有给它赋一个列值,而是在其中硬编码了'TOTAL',我认为这应该会导致你在追求什么。
MYSQL
SELECT SUM(shop_order_price), p.category
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category = 'Mens'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
UNION ALL
SELECT SUM(shop_order_price), p.category
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND p.category= 'Ladies'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
UNION ALL
SELECT SUM(shop_order_price), 'Total'
from shop_orders so
INNER JOIN product p
on so.shop_order_product_id=p.product_id
WHERE so.shop_order_action = 'Sale'
AND so.brand = '53'
AND so.shop_order_location = 'Bawtry'
AND so.shop_order_month = '05' AND so.shop_order_year = '2014'
这是更加优化 - 谢谢。无论如何要在最后一行显示'TOTAL'而不是_NULL_? – aphextwix 2014-09-02 13:20:07
你可以把查询放在派生表中,我会更新。 – 2014-09-02 13:33:00
感谢您的更新答案 - 您是否介意解释为什么您不会使用它的原因? – aphextwix 2014-09-02 13:48:24