mysql - 在mysql中使用虚拟列

Question

我有如下 mysql 查询:

SELECT
  company_name,
  SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
  total_sold_qty,
  (total_purchase_qty - total_sold_qty) AS rem_qty
FROM tbl_company
  JOIN tbl_share_purchase
ON purchase_company_id = tbl_company.id
  JOIN (SELECT
      sold_company_id,
      SUM(sold_qty)   AS total_sold_qty
    FROM tbl_share_sales
    GROUP BY sold_company_id) s
ON s.sold_company_id = tbl_company.id
GROUP BY purchase_company_id

是否可以使用 (total_purchase_qty - total_sold_qty) AS rem_qty 来获取剩余数量？

Answer 1

如果你将它包装在子查询上你可以这样做，但是如果它定义在同一级别你就不能使用别名进行计算，

SELECT  company_name,
        SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
        total_sold_qty,
        (SUM(purchase_qty * num_of_certificate) - total_sold_qty) AS rem_qty
FROM    tbl_company
        JOIN tbl_share_purchase
            ON purchase_company_id = tbl_company.id
        JOIN 
        (
            SELECT  sold_company_id,
                    SUM(sold_qty)   AS total_sold_qty
            FROM    tbl_share_sales
            GROUP   BY sold_company_id
        ) s ON s.sold_company_id = tbl_company.id
GROUP   BY purchase_company_id

这里使用子查询，

SELECT  company_name,
        total_purchase_qty,
        total_sold_qty,
        (total_purchase_qty - total_sold_qty) AS rem_qty
FROM
(
    SELECT  company_name,
            SUM(purchase_qty * num_of_certificate) AS total_purchase_qty,
            total_sold_qty
    FROM    tbl_company
            JOIN tbl_share_purchase
                ON purchase_company_id = tbl_company.id
            JOIN 
            (
                SELECT  sold_company_id,
                        SUM(sold_qty)   AS total_sold_qty
                FROM    tbl_share_sales
                GROUP   BY sold_company_id
            ) s ON s.sold_company_id = tbl_company.id
    GROUP   BY purchase_company_id
)