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

t3psigkw 于 2021-06-21 发布在 Mysql

关注(0)|答案(5)|浏览(303)

我有一个复杂的aggregate functions查询，它生成一个结果集，并且必须用包含该结果集的总计和平均值的单行进行修改。
我的想法是为结果集分配一个别名，然后在UNIONALL语句之后的第二个查询中使用该别名。
但是，我无法在union all语句之后的后续select语句中成功使用别名。
为了简单起见，我不会在这里发布原始查询，只是我尝试过的变体的简化列表：

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，则结果相同。
那么，我应该如何在前面的查询中为结果集/派生表分配别名，并在后面的查询中使用相同的别名，所有这些都在联合查询中？
修正案：
我的主要查询是：

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

我想用一个查询来完成。

sql mysql

来源：https://stackoverflow.com/questions/48059363/sql-union-query-referencing-to-alias-of-derived-table

5条答案

按热度按时间

vq8itlhq1#

你的问题写得很好。您可以通过使用一个包含伪查询的周围查询来获得一个总行 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;

赞(0）回复(0）举报 2021-06-21

kninwzqo2#

不能使用基于子查询的表别名（不在outer united select的范围内），必须重复以下代码：

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

赞(0）回复(0）举报 2021-06-21

rbpvctlc3#

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

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

赞(0）回复(0）举报 2021-06-21

vu8f3i0k4#

我不完全确定你想解决什么问题，但我猜是这样的：
假设的“主要”查询：

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，这里可能有一些语法错误。不过，总体思路应该很清楚。）
当然，也就是说，如果我必须这样做，可能有更好的方法来达到同样的效果。见托尔斯滕凯特纳对此事的评论。
（前面的答案假设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

赞(0）回复(0）举报 2021-06-21

bxgwgixi5#

出现错误是因为 UNION 不知道别人的别名。
db engine执行4个查询，然后使用union操作粘贴它们。
你真正的table是 fees . test3是第三个查询中使用的别名。
如果要处理 UNION 操作时，必须将查询封装在主查询中。

赞(0）回复(0）举报 2021-06-21