查詢 物資明細：

SELECT r.material_id,r.material_category,r.material_name,r.material_quality,

r.standard,r.product_factory,r.material_type,r.measure_unit,(r.num+r.num_sub) as num,

(r.num+r.num_sub)-num_sd-num_yd as current_js_num

FROM

-- 單獨計算各個數(shù)值

(

SELECT material_base.*

,

-- 合同數(shù)量; parent_id

(

SELECT SUM(num) from scm_material_contract

GROUP BY material_id,form_id,audit_status HAVING material_id=material_base.material_id

and form_id=‘1111‘ and audit_status=‘1‘

)

AS num,

(

SELECT SUM(num) from scm_material_contract_add

GROUP BY material_id,parent_id,audit_status HAVING material_id=material_base.material_id

and parent_id=‘1111‘ and audit_status=‘1‘

)

AS num_sub,

(

SELECT SUM(current_js_num) from scm_material_settle_sd

GROUP BY material_id,parent_id,audit_status HAVING material_id=material_base.material_id

and parent_id=‘1111‘ and audit_status=‘1‘

)

AS num_sd,

(

SELECT SUM(current_js_num) from scm_material_settle_yd

GROUP BY material_id,parent_id,audit_status,chongxiao_status HAVING material_id=material_base.material_id

and parent_id=‘1111‘ and audit_status=‘1‘

and chongxiao_status !=‘1‘

)

AS num_yd

FROM(

SELECT material_id,material_category,material_name,material_quality,standard,product_factory,material_type,measure_unit FROM scm_material_base WHERE

material_id in(

-- 找出所有的物料編號

SELECT material_id from scm_material_contract where form_id=‘1111‘ and audit_status=‘1‘

union

SELECT material_id from scm_material_contract_add ma INNER JOIN scm_purchase_contract_add ac ON ma.form_id=ac.sub_contract_id

where ac.contract_id=‘1111‘ and ac.audit_status=‘1‘

)

)as material_base

)as r

原文：https://www.cnblogs.com/hoge66/p/12313129.html

總結

以上是生活随笔為你收集整理的mysql 复杂的sql_mysql 一个复杂的sql的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。