查询子选择的问题
我试图合并两个大的查询在一起，无法使它工作。我有一个几乎可以工作的查询，但我无法让服务器接受嵌套查询中的p.products_id。有人能帮忙解决这个问题吗？
带有多个UNION ALL的奇怪的内部选择会生成一个范围内所有日期的列表。(改编自https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range)。
嵌套级别的数量似乎过多，但这是使查询工作起来的唯一方法。
select p.products_id,
( SELECT count(*)
from ( select ( SELECT if(v1.quantity, v1.quantity, null)
FROM inventory_history v1
where v1.products_id = p.products_id
AND v1.inventory_date <= t1.date
ORDER BY v1.inventory_date desc limit 1
) as quantity
FROM ( SELECT a.date
FROM ( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS date
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
) a
WHERE a.date >= curdate() - INTERVAL 400 DAY
) t1
having quantity is not null
) x
) as count
from products p
where p.master_categories_id=264;错误1054 (42S22)：'where子句‘中未知列'p.products_id’
我要做的是: inventory_history包含按日期划分的库存级别的快照。数据是稀疏存储的--只有在库存发生变化时才会添加记录。大多数查询是将稀疏数据转换为范围内所有日期的结果，然后计算库存数量> 0的天数。
服务器版本: 10.1.35-MariaDB FreeBSD端口
小提琴：https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=e199d540f40e4362e6aa1d667e17fcac
回答 1
Database Administration用户
发布于 2021-05-13 20:15:48
可能有一种方法可以重新排列关联子查询的部分，以便它可以直接引用外部级别的products_id，而不是在自己的子查询/派生表中，但是最后我没有耐心去找到它。
相反，我可以为您提供一种不同的方法来计算相同的结果，使用变量：
SELECT
p.products_id
, (
SELECT
SUM(
DATEDIFF(to_date, GREATEST(from_date, CURDATE() - INTERVAL 400 DAY)) * factor
)
FROM
(
SELECT
IF(h.products_id = @p, @d, CURDATE() + INTERVAL 1 DAY) AS to_date
, @d := h.inventory_date AS from_date
, (quantity > 0) AS factor
, @p := h.products_id AS products_id
FROM
inventory_history AS h
ORDER BY
h.products_id ASC
, h.inventory_date DESC
) AS v1
WHERE
v1.products_id = p.products_id
AND v1.to_date >= CURDATE() - INTERVAL 400 DAY
) AS count
FROM
(SELECT @p := null, @d := null) AS init
, products AS p
WHERE
p.master_categories_id=264
;在两个变量的帮助下-- @p用于跟踪当前的products_id，@d用于将inventory_date传递到同一产品的前一条记录-- v1派生表将inventory_history转换为一组日期范围，并带有一个标志(factor)以指示该范围是否符合count结果的条件。
派生表的定义有两个层次，因此不能引用主查询的products_id。它只是返回整个集合。但是，使用SELECT的v1是p.products_id有效的地方，也是对products_id进行过滤的地方。
然后，计数本身就是使用DATEDIFF(end_date, start_date)函数将日期范围的长度相加的问题。开始日期表达式(GREATEST(...))只需确保从现在起，最早日期范围的起始点不早于400天。
下面是使用您的测试设置在dbfiddle.uk上对此解决方案进行现场演示的链接：
https://dba.stackexchange.com/questions/289457
复制相似问题
腾讯云开发者
