每次求和后的mysql求和结果

Question

你好，我正在尝试对两个 mysql 结果求和。

这是我的代码，它不起作用。

SELECT 
     a.* ,
    b.* , 
    SUM(b.price) as price,
    SUM(b.tax) as tax,
    price + tax as price_tax --> error
FROM (SELECT  name , item_id 
     FROM items)a
LEFT JOIN (SELECT item_id , price , tax
         FROM bank
        )b
ON a.item_id = b.item_id 
GROUP BY item_id

• 我想像这样对两个结果求和:price + tax as price_tax

Answer 1

首先

SQL 引擎在(特定的)查询执行期间不知道 select 子句中的列别名，所以你不能使用别名你必须重复代码

      SELECT 

        SUM(b.price) as price,
        SUM(b.tax) as tax,
        SUM(b.price) +  SUM(b.tax) as  price_tax 
        FROM (
            SELECT  name , item_id 
            FROM items
        )a
        LEFT JOIN (
        SELECT item_id , price , tax

        FROM bank
        )b
        ON a.item_id = b.item_id 
        GROUP BY item_id

第二个

在某些mysql版本中不允许使用聚合函数和select group by中未提及的列，因此您必须为其他列删除group by中未提及的列(或添加假聚合函数)

      SELECT 
        a.item ,
        SUM(b.price) as price,
        SUM(b.tax) as tax,
        SUM(b.price) +  SUM(b.tax) as  price_tax 
        FROM (
            SELECT  name , item_id 
            FROM items
        )a
        LEFT JOIN (
        SELECT item_id , price , tax

        FROM bank
        )b
        ON a.item_id = b.item_id 
        GROUP BY item_id