MySQL 在搜索结果底部添加总计(如果找到一条或多条记录)

Question

>我在搜索结果底部有以下SQL语句生成总计，但是如果没有找到记录，我不想显示总计。 需要以下声明的帮助。

SELECT Member_ID, Order_Total, Shipping_Cost, TAX FROM Order Where                 
Order_Total > 100
UNION (
SELECT "Total" as Member_ID, sum(Order_Total) as Order_Total, sum(Shipping_Cost) as Shipping_Cost, sum(TAX) as TAX FROM Order Where                 
Order_Total > 100);

Answer 1

您不需要UNION，您可以使用WITH ROLLUP选项进行自动总计。如果查询未选择任何行，则结果将为空。

SELECT IFNULL(Member_ID, 'Total') AS Member_ID, Order_Total, Shipping_Cost, TAX
FROM (
SELECT Member_ID, sum(Order_Total) as Order_Total, sum(Shipping_Cost) as Shipping_Cost, sum(TAX) as TAX 
FROM `Order` 
Where Order_Total > 100
GROUP BY Order_ID
WITH ROLLUP) AS x

另一种方法是保留UNION，但向第二个查询添加HAVING子句。

SELECT Member_ID, Order_Total, Shipping_Cost, TAX 
FROM `Order` 
Where Order_Total > 100
UNION (
SELECT "Total" as Member_ID, sum(Order_Total) as Order_Total, sum(Shipping_Cost) as Shipping_Cost, sum(TAX) as TAX 
FROM `Order` 
Where Order_Total > 100
HAVING COUNT(*) > 0);