gpt4 book ai didi

mysql - 如何减去作为子查询结果的 2 列

转载 作者:行者123 更新时间:2023-11-28 23:47:21 25 4
gpt4 key购买 nike

我有这个查询,它按预期工作正常,即它显示所有列以及每个子查询的 2 列...

SELECT 
a.group_id,
a.code,
a.description_en,
c.size_code,
(
SELECT
SUM(b.qty)
FROM
receiving_details AS b
WHERE
b.code = c.size_code
) AS in_qty,
(
SELECT
SUM(d.qty)
FROM
requisition_details AS d
WHERE
d. matl_code = c.size_code
) AS out_qty
FROM products AS a
INNER JOIN products_sizes AS c ON c.prod_code = a.code
ORDER BY a.group_id ASC, a.code ASC, c.size_code ASC

但是,当我尝试添加这一行时...

(in_qty - out_qty) AS balance,

就在“FROM”语句之前,我收到一个错误:Unknown column 'in_qty' in 'field list'

我做错了什么?

编辑:

根据接受的答案,我又做了一些修复并得到了我想要的结果。

SELECT *, (e.in_qty - e.out_qty) AS balance FROM
(SELECT
a.group_id,
a.code,
a.description_en,
c.size_code,
(
SELECT
IFNULL(SUM(b.qty),0)
FROM
receiving_details AS b
WHERE
b.code = c.size_code
) AS in_qty,
(
SELECT
IFNULL(SUM(d.qty),0)
FROM
requisition_details AS d
WHERE
d. matl_code = c.size_code
) AS out_qty
FROM products AS a
INNER JOIN products_sizes AS c ON c.prod_code = a.code) AS e
ORDER BY e.group_id ASC, e.code ASC, e.size_code ASC

最佳答案

您不能在自己的 select 语句中引用同一字段。一种选择是将结果移动到另一个子查询中,然后执行计算:

select *, (in_qty - out_qty) AS balance
from (
SELECT
a.group_id,
a.code,
a.description_en,
c.size_code,
(
SELECT
SUM(b.qty)
FROM
receiving_details AS b
WHERE
b.code = c.size_code
) AS in_qty,
(
SELECT
SUM(d.qty)
FROM
requisition_details AS d
WHERE
d. matl_code = c.size_code
) AS out_qty
FROM products AS a
INNER JOIN products_sizes AS c ON c.prod_code = a.code
) t
ORDER BY group_id, code, size_code

关于mysql - 如何减去作为子查询结果的 2 列,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/33380946/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com