带有子查询结果的where子句

hjzp0vay 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(358)

我对这个问题非常沮丧，我已经解决了好几个小时了：(

SELECT m_order.id,       
 (SELECT SUM(price*amount) FROM m_order_item as item WHERE item.id_order = m_order.id) AS total
FROM `m_order` 
WHERE total > 100

它不断地在where子句中返回未知的列total，但是在没有这个有问题的where子句的情况下，名为total的列是完全可以计算的。
谢谢你的帮助。

mysql

来源：https://stackoverflow.com/questions/52005579/mysql-query-where-clause-with-result-of-subquery

2条答案

按热度按时间

gg58donl1#

total是列别名，对于where条件不可见。。其中，在select子句求值之前，零件由bd引擎求值
所以你应该重复这个代码

SELECT m_order.id,       
 (  SELECT SUM(price*amount) 
    FROM m_order_item as item 
    WHERE item.id_order = m_order.id ) AS total
FROM `m_order` 
WHERE  (  SELECT SUM(price*amount) 
    FROM m_order_item as item 
    WHERE item.id_order = m_order.id ) > 100

或者尝试使用筛选选择的结果

SELECT m_order.id,       
   (  SELECT SUM(price*amount) 
      FROM m_order_item as item 
      WHERE item.id_order = m_order.id ) AS total
  FROM `m_order` 
  HAVING  total >  100

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

v09wglhw2#

不能在中使用别名 WHERE 条款。请参阅手册。把它放进一个盒子里 HAVING 取而代之的是：

SELECT m_order.id,       
 (SELECT SUM(price*amount) FROM m_order_item as item WHERE item.id_order = m_order.id) AS total
FROM `m_order` 
HAVING total > 100

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

我来回答

带有子查询结果的where子句

2条答案

相关问题

热门标签

最新问答