我的 mysql 查询中发生了一些奇妙的事情。这是我计算成本的查询:
select inv.price_for_one
from invoice inv,
posinfo pi,
goods g,
stock_item si,
warehouses w
where g.id = :id
and pi.id = w.pos_info_id
and si.warehouses_id = w.id
and g.id = si.goods_id
and inv.stock_item_id = si.id
and pi.client_legal_informations_id = 12
and inv.id = (select max(inv_sub.id)
from invoice inv_sub,
stock_item si_sub
where inv_sub.id in (inv.id)
and inv_sub.warehouses_id = w.id
and inv_sub.archive = false
and inv_sub.expense = false
and inv_sub.stock_item_id = si_sub.id
group by inv_sub.id,
limit 1);
目前还不清楚为什么它返回两行,但应该只返回一行,这是一个重要的条件。我发现子查询返回两行,因此外部查询也返回两张发票。但是为什么内部查询返回两行呢?
我将真实值插入到子查询中查看它返回的内容,结果如下:
select min(inv_sub.id)
from invoice inv_sub,
stock_item si_sub
where inv_sub.id in (285, 286)
and inv_sub.warehouses_id = 623
and inv_sub.archive = false
and inv_sub.expense = false
and inv_sub.stock_item_id = si_sub.id
group by inv_sub.id,
limit 1;
这个查询返回了两行。这是怎么回事,这是为什么?
不知怎的,这个建议没有帮助。此查询仍然返回两行,但我们需要最后一张未注销的发票的数据
SELECT inv.price_for_one AS costPriceGoods
FROM invoice inv,
posinfo pi,
goods g,
stock_item si,
warehouses w
WHERE si.goods_id = g.id
AND pi.client_legal_informations_id = 12
AND w.pos_info_id = pi.id
AND si.warehouses_id = w.id
AND inv.stock_item_id = si.id
AND inv.id = (SELECT DISTINCT inv_sub.id
FROM invoice inv_sub,
stock_item si_sub
WHERE inv_sub.id in (inv.id)
AND inv_sub.warehouses_id = w.id
AND inv_sub.archive = FALSE
AND inv_sub.expense = FALSE
AND inv_sub.stock_item_id = si_sub.id
ORDER BY inv_sub.id
LIMIT 1)
AND g.id = 10121
ORDER BY inv.id;
而且内部查询返回一行
SELECT DISTINCT inv_sub.id
FROM invoice inv_sub,
stock_item si_sub
WHERE inv_sub.id in (287, 288)
AND inv_sub.warehouses_id = 205
AND inv_sub.archive = FALSE
AND inv_sub.expense = FALSE
AND inv_sub.stock_item_id = si_sub.id
ORDER BY inv_sub.id
LIMIT 1
让我们从这样一个事实开始:这样的请求根本不会被执行 - LIMIT 1 之前的逗号会干扰您将得到带有 inv_sub.id=285 和 inv_sub.id=286 的行,它们将被分为 2 组和 min 。 (inv_sub.id) 将在其中计算。限制应该保留这2个的第一行(根据MySql),但是这个动作的含义对我来说不是很清楚。您的查询可以转换为:
唯一的问题是哪个会更快
如果我错了,请纠正我:发票存储在发票表中,我们可以通过请求获取仓库 623 尚未核销的发票列表
此列表中的最后一个将是
max(inv_sub.id)
您需要寻找的。那些。请求必须转换为表格