SQL 联合查询 - 引用派生表的别名

Question

我有一个复杂的聚合函数查询，它生成一个结果集，并且必须用包含该结果集的总计和平均值的单行进行修改。

我的想法是为结果集分配一个别名，然后在 UNION ALL 语句之后的第二个查询中使用该别名。

但是，我无法在随后的 SELECT 语句中，在 UNION ALL 语句之后成功使用该别名。

为了简单起见，我不会在这里发布原始查询，只是我尝试过的变体的简化列表：

SELECT * FROM fees AS Test1 WHERE Percentage = 15
UNION ALL
(SELECT * FROM fees AS Test2 WHERE Percentage > 15)
UNION ALL
(SELECT * FROM (SELECT * FROM fees AS Test3 WHERE Percentage < 10) AS Test4)
UNION ALL
SELECT * FROM Test3

结果是：

MySQL said: Documentation
#1146 - Table 'xxxxxx.Test3' doesn't exist

如果最后一个查询引用表 Test1、Test2 或 Test4，则结果相同。

那么，我应该如何在前面的查询中为结果集/派生表分配别名，并在后面的查询中使用相同的别名，所有这些都在 UNION 查询中？

修正案：

我的主要查询是：

SELECT
COALESCE(referrers.name,order_items.ReferrerID),
SUM(order_items.quantity) as QtySold,
ROUND(SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 2) as TotalRevenueNetto,
ROUND(100*SUM(order_items.quantity*order_items.purchasepricenet)/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as PurchasePrice,
ROUND(100*SUM(order_items.quantity*COALESCE(order_items.calculatedfee,0)+order_items.quantity*COALESCE(order_items.calculatedcost,0))/SUM((order_items.quantity*order_items.price+order_items.shippingcosts)/((100+order_items.vat)/100)), 1) as Costs,
ROUND(100*SUM(order_items.calculatedprofit) / SUM( (order_items.quantity*order_items.price + order_items.shippingcosts)/((100+order_items.vat)/100) ) , 1) as Profit,
COALESCE(round(100*Returns.TotalReturns_Qty/SUM(order_items.quantity),2),0) as TotalReturns
FROM order_items LEFT JOIN (SELECT order_items.ReferrerID as ReferrerID, sum(order_items.quantity) as TotalReturns_Qty FROM order_items WHERE OrderType='returns' and OrderTimeStamp>='2017-12-1 00:00:00' GROUP BY order_items.ReferrerID) as Returns ON Returns.ReferrerID = order_items.ReferrerID LEFT JOIN `referrers` on `referrers`.`referrerId` = `order_items`.`ReferrerID`
WHERE ( ( order_items.BundleItemID in ('-1', '0') and order_items.OrderType in ('order', '') ) or ( order_items.BundleItemID is NULL and order_items.OrderType = 'returns' ) ) and order_items.OrderTimestamp >= '2017-12-1 00:00:00'
GROUP BY order_items.ReferrerID
ORDER BY referrers.name ASC

我想对上面查询产生的所有行进行总计：

SELECT 'All marketplaces', SUM(QtySold), SUM(TotalRevenueNetto), AVG(PurchasePrice), AVG(Costs), AVG(Profit), AVG(TotalReturns) FROM PrimaryQuery

我想用一个查询来做到这一点。

Answer 1

你的查询写得很好。您可以通过使用带有虚拟GROUP BY子句的周围查询来获取总行，并WITH ROLLUP：

SELECT 
COALESCE(Referrer, 'All marketplaces'),
SUM(QtySold) AS QtySold,
SUM(TotalRevenueNetto) AS TotalRevenueNetto,
AVG(PurchasePrice) AS PurchasePrice,
AVG(Costs) AS Costs,
AVG(Profit) AS Profit,
AVG(TotalReturns) AS TotalReturns
FROM 
(
SELECT
COALESCE(referrers.name,order_items.ReferrerID) AS Referrer,
SUM(order_items.quantity) AS QtySold,
...
) PrimaryQuery
GROUP BY Referrer ASC WITH ROLLUP;

Answer 2

我不完全确定您要解决什么问题，但我想如下所示：

假设的"主"查询：

SELECT    T1.ID
,         Sum(total_grade)/COUNT(subjects) as AverageGrade
FROM      A_Table T1
JOIN      AnotherTable T2
ON  T2.id = T1.id
GROUP BY  T1.ID

您需要子结果集，而不必继续查询相同的数据。

编辑：我错误地认为下面提到的链接文档和方法适用于当前版本的mySQL。但是，它是未来版本的草稿，目前不支持CTE。

在没有 CTE 支持的情况下，我可能只会将结果集插入到临时表中。像这样：

CREATE TABLE TEMP_TABLE(ID INT, AverageGrade DECIMAL(15, 3))
INSERT INTO TEMP_TABLE
SELECT    T1.ID
,         Sum(total_grade)/COUNT(subjects) as AverageGrade
FROM      A_Table T1
JOIN      AnotherTable T2
ON  T2.id = T1.id
GROUP BY  T1.ID
SELECT ID, AverageGrade FROM TEMP_TABLE WHERE AverageGrade > 5
UNION ALL
SELECT COUNT(ID) AS TotalCount, SUM(AverageGrade) AS Total_AVGGrade FROM TEMP_TABLE
DROP TABLE TEMP_TABLE

(免责声明：我对mySQL不太熟悉，这里可能存在一些语法错误。不过，总体思路应该很清楚。

也就是说，当然，如果我必须这样做，可能有更好的方法来实现相同的目标。请参阅Thorsten Kettner对此事的评论。

---

(假设CTE是可能的先前答案：)

CTE 方法如下所示：

WITH CTE AS
(
SELECT    T1.ID
,         Sum(total_grade)/COUNT(subjects) as AverageGrade
FROM      A_Table T1
JOIN      AnotherTable T2
ON  T2.id = T1.id
GROUP BY  T1.ID
)
SELECT ID, AverageGrade FROM CTE WHERE AverageGrade > 5
UNION ALL
SELECT COUNT(ID) AS TotalCount, SUM(AverageGrade) AS Total_AVGGrade FROM CTE

Answer 3

您有错误，因为UNION中涉及的每个查询都不知道其他查询的别名。

DB Engine 在您的情况下执行 4 个查询，然后使用 UNION 操作粘贴它们。

您的真实表是fees.Test3 是第三个查询中使用的别名。

如果要处理UNION操作的结果，则必须将查询封装在 MAIN 查询中。

Answer 4

看起来你需要下面这样的东西。请尝试

SELECT * FROM fees AS Test2 WHERE Percentage >= 15
UNION ALL
SELECT * FROM fees AS Test3 WHERE Percentage < 10

Answer 5

您不能使用基于子查询的表别名(不在外部联合选择范围内)，您必须重复代码，例如：

SELECT * FROM fees AS Test1 WHERE Percentage = 15
UNION ALL
SELECT * FROM fees AS Test2 WHERE Percentage > 15
UNION ALL
SELECT * FROM (
SELECT * FROM fees AS Test3 WHERE Percentage < 10
) AS Test4
UNION ALL
SELECT * FROM fees AS Test3 WHERE Percentage < 10