mysql - 库存可用性能Mysql
问题描述
我正在尝试在 MYSQL 中获取可用库存的值,但是当行数超过35000时,请求失败且没有响应。我使用子查询来获取可用库存的价值。
这是我的代码:
SELECT
p.`product-id` AS id,
p.`product-id` AS product_id,
ped.purchase_entry_id AS purchase_id,
p.`product-name` AS name,
m.name AS manufacturer,
p.`product-type` AS product_type,
ped.id AS batch_no,
ped.internal_sales_rate AS barcode,
c.name as category,
ped.size AS size,
IFNULL(ped.sales_rate,0) AS sales_rate,
IFNULL(ped.purchase_rate,0) AS purchase_rate,
IFNULL(SUM(ped.units),0) AS units_purchased,
(select IFNULL(SUM(sed.qty),0) FROM sales_entry_details sed WHERE (sed.batch_no = ped.id)) AS units_sold,
(select IFNULL(SUM(sr.qty),0) FROM sales_return_item sr WHERE (sr.batch_no = ped.id)) AS retured,
(select IFNULL(SUM(pri.qty),0) FROM purchase_return_items pri WHERE (pri.batch_no = ped.id)) AS purchase_return,
(select IFNULL(SUM(ast.qty),0) FROM adjustment_stock ast WHERE (ast.batch_no = ped.id)) AS adjustment,
(select IFNULL(SUM(ast2.batch_no),0) FROM adjustment_stock ast2 WHERE (ast2.batch_no = ped.id)) AS isChecked
FROM purchase_entry_details ped
LEFT JOIN products p on p.`product-id` = ped.product_id
LEFT JOIN category c ON c.id = p.`product-type`
LEFT JOIN manufacturer m ON m.id = p.manufacturer
GROUP BY ped.id;
有没有更好的选择来获取 SQL 中可用的股票价值?
解决方案
查询看起来不错。由于没有限制标准(无WHERE
子句),您将按顺序阅读整个表格。
您当然希望batch_no
在所涉及的表中建立索引。但我猜这些是某个批处理表的外键,所以索引应该已经存在。
我唯一注意到的是您选择了adjustment_stock
两次。您可以通过将子查询移动到您的FROM
子句来避免这种情况:
SELECT
p.`product-id` AS id,
p.`product-id` AS product_id,
ped.purchase_entry_id AS purchase_id,
p.`product-name` AS name,
m.name AS manufacturer,
p.`product-type` AS product_type,
ped.id AS batch_no,
ped.internal_sales_rate AS barcode,
c.name as category,
ped.size AS size,
IFNULL(ped.sales_rate,0) AS sales_rate,
IFNULL(ped.purchase_rate,0) AS purchase_rate,
IFNULL(SUM(ped.units),0) AS units_purchased,
IFNULL(sed.sum_qty),0) AS units_sold,
IFNULL(sr.sum_qty),0) AS retured,
IFNULL(pri.sum_qty),0) AS retured,
IFNULL(ast.sum_qty),0) AS adjustment,
IFNULL(ast.sum_batch_no),0) AS isChecked
FROM purchase_entry_details ped
LEFT JOIN products p on p.`product-id` = ped.product_id
LEFT JOIN category c ON c.id = p.`product-type`
LEFT JOIN manufacturer m ON m.id = p.manufacturer
LEFT JOIN
(
select batch_no, sum(qty) as sum_qty from sales_entry_details group by batch_no
) sed on sed.batch_no = ped.id
LEFT JOIN
(
select batch_no, sum(qty) as sum_qty from sales_return_item group by batch_no
) sr on sr.batch_no = ped.id
LEFT JOIN
(
select batch_no, sum(qty) as sum_qty from purchase_return_items group by batch_no
) pri on pri.batch_no = ped.id
LEFT JOIN
(
select batch_no, sum(qty) as sum_qty, sum(batch_no) as sum_batch_no
from adjustment_stock group by batch_no
) ast on ast.batch_no = ped.id
GROUP BY ped.id;
(不过,将批号加起来似乎有点奇怪。)
推荐阅读
- python - 如何从拥抱脸使用 deberta 模型并使用 .compile() 和 . summary() 用它
- c# - 渐进式 ASP Web 应用程序中的多个 Azure AD B2C 登录屏幕
- openjdk-11 - JFR 活动开始时间
- html - JQuery keyup 函数 - 错误/div 显示/隐藏一些问题
- webrtc - WebRTC chrome:无法为 mid='0' 的 m 部分设置本地音频描述 recv 参数
- mysql - 时间戳字段设置为零日期,当它应该是 CURRENT_TIMESTAMP
- wordpress - 如何在 Wordpress 上创建寻宝游戏?
- docker - 尝试使用 curl 从 Azure Pipeline 中的另一个容器访问 Docker 容器时连接被拒绝
- python - 通过附加特定键值将 python 列表转换为 JSON
- java - Apache Ignte:如何在 XML 缓存配置中定义 CacheInterceptors