SELECT
t.*
FROM
(
SELECT
count( 1 ) Total
FROM
(
SELECT
count( 1 ) AS total
FROM
pp_replenishment a
JOIN sys_ordersource b ON a.OrderSourceId = b.OrderSourceId
JOIN sys_ordersource_authorization c ON b.OrderSourceId = c.OrderSourceId
JOIN pp_replenishment_strategy d ON a.MarketId = d.MarketId
AND a.StrategyId = d.Id
WHERE
a.customerid = 1
AND a.MarketId = 58
AND TIMESTAMPDIFF(
HOUR,
a.LastModificationTime,
NOW()) > 4+8
AND a.`Status` = 'normal'
AND b.IsActive = 1
AND b.IsDelete = 0
AND c.IsActive = 1
AND d.IsEnabled = 1
AND d.IsDeleted = 0
) a
WHERE
a.Total > 1000
) t
LIMIT 0,
1;
任务描述
需求描述
业务背景
1）业务方临时数据处理、统计、导出等无法得到及时帮助
2）生产问题排查排期周期过长
业务文档
https://alidocs.dingtalk.com/i/nodes/mExel2BLV541nqKkSZX5MzAlWgk9rpMq?utm_scene=team_space
验收标准
及时响应业务问题并处理妥善，保持系统稳定性
1.优化后：
SELECT
t.*
FROM
(
SELECT
count( 1 ) Total
FROM
(
SELECT
count( 1 ) AS total
FROM
pp_replenishment a force index(idx_customerid_marketid_strategyid_status_lastmodifitime)
JOIN sys_ordersource b ON a.OrderSourceId = b.OrderSourceId
JOIN sys_ordersource_authorization c ON b.OrderSourceId = c.OrderSourceId
JOIN pp_replenishment_strategy d ON a.MarketId = d.MarketId
AND a.StrategyId = d.Id
WHERE
a.customerid = 1
AND a.MarketId = 97
AND a.LastModificationTime<SUBDATE(now(),INTERVAL 12 hour)
AND a.`Status` = 'normal'
AND b.IsActive = 1
AND b.IsDelete = 0
AND c.IsActive = 1
AND d.IsEnabled = 1
AND d.IsDeleted = 0
) a
WHERE
a.Total > 1000
) t
LIMIT 0,1