mysql怎么写复杂sql_mysql 一个复杂的sql

查询 物资明细：

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

-- 单独计算各个数值

(

SELECT material_base.*

,

-- 合同数量; 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