mysql - 如何使用MYSQL WHERE查询中的数据

Question

我有这个 MySQL 查询:

SELECT * FROM A

LEFT JOIN B
ON B.id = A.id

LEFT JOIN C
ON C.other = A.other

WHERE

(SELECT SUM(C.quantity) FROM C WHERE C.other = A.other) < A.quantity

有什么方法可以从 SELECT from WHERE 子句中获取值吗？例如PHP使用？像这样的东西:

WHERE

(SELECT SUM(C.quantity) FROM C WHERE C.other = A.other) AS quantity < A.quantity

我需要在 mysql 中比较这两个值，然后我的脚本中需要它们。我当然可以将此子查询放入第一个 SELECT 中 - 但我相信有比在许多地方重复代码更好的方法。

Answer 1

您可以按如下方式执行此操作:

SELECT    A.*, B.*, C.*, D.sumq 
FROM      A
LEFT JOIN B
       ON B.id = A.id
LEFT JOIN C
       ON C.other = A.other
LEFT JOIN (
           SELECT   other, SUM(quantity) sumq
           FROM     C
           GROUP BY other
          ) AS D
       ON D.other = A.other
WHERE     COALESCE(D.sumq, 0) < A.quantity