如何解析where子句many子句中的未知列

xsuvu9jc 于 2021-06-15 发布在 Mysql

关注(0)|答案(1)|浏览(366)

我有一个问题，sql选择相同的信息时，我有很多子句的第一列是未知的其他条款。
这是我的sql表达式：

SELECT * ,
(select sum(sell) from product_details where product_details.product_id = products.id) as total ,
(select sell from product_details where product_details.product_id = products.id order by product_details.id desc limit 1) as ysell ,
(select sum(sell) as wsell FROM (select sell from product_details where product_details.product_id = products.id order by product_details.id desc limit 2 ) as weeksell) as wsell
FROM `products`

我尝试从产品中获取信息，第二个表是基于产品id的产品详细信息；
ysell=上次销售
total=总销售额
wsell=limit 7使用sum（）卖出并从最后7个中选择
但当我运行我的表达我得到错误


# 1054 - Unknown column 'products.id' in 'where clause'

在这一行不知道

(select sum(sell) as wsell FROM (select sell from product_details where product_details.product_id = products.id order by product_details.id desc limit 2 ) as weeksell) as wsell

你有什么建议吗。

sql mysql

来源：https://stackoverflow.com/questions/54063364/how-to-resolve-unknown-column-in-where-clausemany-clause

1条答案

按热度按时间

qni6mghb1#

您的问题是，相关子句不能嵌套超过一层深——因此 products 是看不见的。一种解决方案是将其转换为条件聚合。这有点棘手，但有一种方法：

SELECT p.* ,
       sum(pd.sell) as total,
       max(case when pd.id = pdd.max_id then pd.sell end) as ysell,
       sum(case when pd.id >= pdd.id7 then pd.sell end) as wsell
FROM products p JOIN
     product_details pd
     ON pd.product_id = p.id JOIN
     (SELECT pd.product_id, MAX(pd.id) as max_id,
             SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(pd.id ORDER BY pd.id DESC), ',', 7), ',', -1) as id7
      FROM product_details pd
      GROUP BY pd.product_id
     ) pdd
     ON pdd.product_id = p.id
GROUP BY p.id;  -- reasonable assuming `id` is unique/primary key

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

我来回答

如何解析where子句many子句中的未知列

1条答案

相关问题

热门标签

最新问答