SELECT
a.sub_bill_no AS '子材料訂單',
CASE
WHEN c.bill_type = 0 THEN
'大貨'
WHEN c.bill_type = 1 THEN
'補件'
END AS '訂單型別',
d.purchase_no AS '成品采購訂單',
c.factory_name AS '加工廠名稱',
d.supplier_name '材料商名稱',
t.attribute_name AS '材料商分組',
a.batch_no AS '批次號',
a.batch_name AS '批次名稱',
sum( b.send_qty ) AS '批次數量',
sum( b.send_qty * d.material_tax_price ) AS '批次金額',
sum( IFNULL( e.send_goods_qty, 0 ) ) AS '累計發貨數量',
sum( IFNULL( e.send_goods_qty, 0 ) * d.material_tax_price ) AS '累計發貨金額',
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) AS '剩余發貨數量',
sum( ( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) * d.material_tax_price ) AS '剩余發貨金額',
a.plan_date AS '最終確認交期',
f.send_goods_time '發貨時間',
IF
(
f.send_goods_time IS NULL,
'未發貨',
IF
( sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0, '全部發貨', '部分發貨' )
) AS '發貨狀態',
IF
(
f.send_goods_time IS NULL,
timestampdiff( DAY, curdate( ), a.plan_date ),
IF
(
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0,
timestampdiff( DAY, date_format( f.send_goods_time, '%Y-%m-%d' ), a.plan_date ),
timestampdiff( DAY, curdate( ), a.plan_date )
)
) AS '處理時長(天)',
IF
(
f.send_goods_time IS NULL,
IF
(
timestampdiff( DAY, curdate( ), a.plan_date ) < 0,
'已延期',
IF
( timestampdiff( DAY, curdate( ), a.plan_date ) < 3, '即將延期', '未延期' )
),
IF
(
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0,
'-',
IF
(
timestampdiff( DAY, curdate( ), a.plan_date ) < 0,
'已延期',
IF
( timestampdiff( DAY, curdate( ), a.plan_date ) < 3, '即將延期', '未延期' )
)
)
) AS '延期狀態'
FROM
t_coodination_batch_bill a
JOIN t_coodination_sub_bill c ON a.sub_bill_row_id = c.row_id
JOIN t_coodination_batch_bill_detail b ON a.row_id = b.batch_row_id
JOIN t_coodination_bill_detail d ON b.bill_detail_row_id = d.row_id
LEFT JOIN t_coodination_logistics_detail e ON e.batch_detail_row_id = b.row_id
LEFT JOIN t_coodination_logistics f ON e.logistic_row_id = f.row_id
JOIN t_company_main m ON m.company_code = a.supplier_code
LEFT JOIN t_company_attribute t ON m.company_md_group = t.attribute_code
WHERE
( a.STATUS = 10 OR a.STATUS = 15 OR a.STATUS = 20 )
AND a.bill_no NOT LIKE 'LSXT%'
GROUP BY
a.batch_no,
a.supplier_code
拿到一條sql有大量的表進行關聯查詢，還有資料的運算，測驗環境下一次查了半分鐘左右，很少做sql優化來著，沒有什么思路，懇求各位大神指導
目前是發現實際業務下t_coodination_batch_bill_detail表最大，不知道將此表作為主表進行查詢能否優化？
uj5u.com熱心網友回復：
SELECT
a.sub_bill_no AS '子材料訂單',
CASE
WHEN c.bill_type = 0 THEN
'大貨'
WHEN c.bill_type = 1 THEN
'補件'
END AS '訂單型別',
d.purchase_no AS '成品采購訂單',
c.factory_name AS '加工廠名稱',
d.supplier_name '材料商名稱',
t.attribute_name AS '材料商分組',
a.batch_no AS '批次號',
a.batch_name AS '批次名稱',
sum( b.send_qty ) AS '批次數量',
sum( b.send_qty * d.material_tax_price ) AS '批次金額',
sum( IFNULL( e.send_goods_qty, 0 ) ) AS '累計發貨數量',
sum( IFNULL( e.send_goods_qty, 0 ) * d.material_tax_price ) AS '累計發貨金額',
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) AS '剩余發貨數量',
sum( ( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) * d.material_tax_price ) AS '剩余發貨金額',
a.plan_date AS '最終確認交期',
f.send_goods_time '發貨時間',
IF
(
f.send_goods_time IS NULL,
'未發貨',
IF
( sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0, '全部發貨', '部分發貨' )
) AS '發貨狀態',
IF
(
f.send_goods_time IS NULL,
timestampdiff( DAY, curdate( ), a.plan_date ),
IF
(
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0,
timestampdiff( DAY, date_format( f.send_goods_time, '%Y-%m-%d' ), a.plan_date ),
timestampdiff( DAY, curdate( ), a.plan_date )
)
) AS '處理時長(天)',
IF
(
f.send_goods_time IS NULL,
IF
(
timestampdiff( DAY, curdate( ), a.plan_date ) < 0,
'已延期',
IF
( timestampdiff( DAY, curdate( ), a.plan_date ) < 3, '即將延期', '未延期' )
),
IF
(
sum( b.send_qty - IFNULL( e.send_goods_qty, 0 ) ) = 0,
'-',
IF
(
timestampdiff( DAY, curdate( ), a.plan_date ) < 0,
'已延期',
IF
( timestampdiff( DAY, curdate( ), a.plan_date ) < 3, '即將延期', '未延期' )
)
)
) AS '延期狀態'
FROM
t_coodination_batch_bill a
JOIN t_coodination_sub_bill c ON a.sub_bill_row_id = c.row_id
JOIN t_coodination_batch_bill_detail b ON a.row_id = b.batch_row_id
JOIN t_coodination_bill_detail d ON b.bill_detail_row_id = d.row_id
LEFT JOIN t_coodination_logistics_detail e ON e.batch_detail_row_id = b.row_id
LEFT JOIN t_coodination_logistics f ON e.logistic_row_id = f.row_id
JOIN t_company_main m ON m.company_code = a.supplier_code
LEFT JOIN t_company_attribute t ON m.company_md_group = t.attribute_code
WHERE
( a.STATUS = 10 OR a.STATUS = 15 OR a.STATUS = 20 )
AND a.bill_no NOT LIKE 'LSXT%'
GROUP BY
a.batch_no,
a.supplier_code
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19435.html
標籤：疑難問題
上一篇：存盤程序執行問題