56,937
社区成员




SELECT t1.*, count(1) as partsSize FROM (
SELECT
t.goods_id,
'19768' as model_id,
t.parts_classify as parts_group,
t.parts_default,
t.parts_id AS partsId,
if(
t.parts_numbers>0,t.parts_numbers,
if(cm1.parts_numbers>0,cm1.parts_numbers,if(cm2.parts_numbers>0,cm2.parts_numbers,if(cm3.parts_numbers>0,cm3.parts_numbers,cm4.parts_numbers)))
) AS parts_numbers,
if(
t.time_fee_total>-1,t.time_fee_total,
if(cm1.time_fee_total>-1,cm1.time_fee_total,if(cm2.time_fee_total>-1,cm2.time_fee_total,if(cm3.time_fee_total>-1,cm3.time_fee_total,cm4.time_fee_total)))
) time_fee_total,
if(cm1.predict_working_hours>=0,cm1.predict_working_hours,if(cm2.predict_working_hours>=0,cm2.predict_working_hours,
if(cm3.predict_working_hours>=0,cm3.predict_working_hours,cm4.predict_working_hours)))
as predict_working_hours,
if(NOT ISNULL(cm1.parts_oe_code),cm1.parts_oe_code,if(NOT ISNULL(cm2.parts_oe_code),cm2.parts_oe_code,
if(NOT ISNULL(cm3.parts_oe_code),cm3.parts_oe_code,cm4.parts_oe_code))) AS parts_oe_code,
(
(p.car_owner_sales_price*if(
t.parts_numbers>0,t.parts_numbers,
if(NOT ISNULL(cm1.parts_numbers),cm1.parts_numbers,if(NOT ISNULL(cm2.parts_numbers),cm2.parts_numbers,if(NOT ISNULL(cm3.parts_numbers),cm3.parts_numbers,cm4.parts_numbers)))
) + if(
t.time_fee_total>-1,t.time_fee_total,
if(NOT ISNULL(cm1.time_fee_total),cm1.time_fee_total,if(NOT ISNULL(cm2.time_fee_total),cm2.time_fee_total,if(NOT ISNULL(cm3.time_fee_total),cm3.time_fee_total,cm4.time_fee_total)))
))
) total_amount,
p.id,
p.parts_name,
p.parts_brand,
p.parts_brand_code,
p.parts_classify,
p.car_owner_sales_price,
p.cost_price,
p.unit,
p.goods_describe,
p.states,
p.skill_of_technician,
p.technician_sales_price,
p.parts_type,
p.parts_spec,
p.version_no,
p.is_activity,
imi.img_url
FROM goods_of_parts t
INNER JOIN goods_terminal_display gd ON gd.goods_id=t.goods_id AND gd.terminal_val=1
LEFT JOIN parts_config p ON p.parts_classify=t.parts_classify and p.is_deleted = 0 AND p.states = 1
LEFT JOIN img_manager_info imi on imi.business_tab='parts_config' and imi.business_id=p.id
LEFT JOIN car_parts_of_model cm1 on cm1.car_brand_id=1929 and cm1.car_audi_id=1930 and cm1.car_model_id=19768 and cm1.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm2 on cm2.car_brand_id=1929 and cm2.car_audi_id=1930 and cm2.car_model_id=0 and cm2.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm3 on cm3.car_brand_id=1929 and cm3.car_audi_id=0 and cm3.car_model_id=0 and cm3.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm4 on cm4.car_brand_id=0 and cm4.car_audi_id=0 and cm4.car_model_id=0 and cm4.car_parts_id=p.id
WHERE t.parts_id=0
and (cm1.id>0 or cm2.id>0 or cm3.id>0 or cm4.id>0)
UNION ALL
SELECT
t.goods_id,
'19768' as model_id,
t.parts_classify as parts_group,
t.parts_default,
t.parts_id AS partsId,
if(
t.parts_numbers>0,t.parts_numbers,
if(cm1.parts_numbers>0,cm1.parts_numbers,if(cm2.parts_numbers>0,cm2.parts_numbers,if(cm3.parts_numbers>0,cm3.parts_numbers,cm4.parts_numbers)))
) AS parts_numbers,
if(
t.time_fee_total>-1,t.time_fee_total,
if(cm1.time_fee_total>-1,cm1.time_fee_total,if(cm2.time_fee_total>-1,cm2.time_fee_total,if(cm3.time_fee_total>-1,cm3.time_fee_total,cm4.time_fee_total)))
) time_fee_total,
if(cm1.predict_working_hours>=0,cm1.predict_working_hours,if(cm2.predict_working_hours>=0,cm2.predict_working_hours,
if(cm3.predict_working_hours>=0,cm3.predict_working_hours,cm4.predict_working_hours)))
as predict_working_hours,
if(NOT ISNULL(cm1.parts_oe_code),cm1.parts_oe_code,if(NOT ISNULL(cm2.parts_oe_code),cm2.parts_oe_code,
if(NOT ISNULL(cm3.parts_oe_code),cm3.parts_oe_code,cm4.parts_oe_code))) AS parts_oe_code,
(
(p.car_owner_sales_price*if(
t.parts_numbers>0,t.parts_numbers,
if(NOT ISNULL(cm1.parts_numbers),cm1.parts_numbers,if(NOT ISNULL(cm2.parts_numbers),cm2.parts_numbers,if(NOT ISNULL(cm3.parts_numbers),cm3.parts_numbers,cm4.parts_numbers)))
) + if(
t.time_fee_total>-1,t.time_fee_total,
if(NOT ISNULL(cm1.time_fee_total),cm1.time_fee_total,if(NOT ISNULL(cm2.time_fee_total),cm2.time_fee_total,if(NOT ISNULL(cm3.time_fee_total),cm3.time_fee_total,cm4.time_fee_total)))
))
) total_amount,
p.id,
p.parts_name,
p.parts_brand,
p.parts_brand_code,
p.parts_classify,
p.car_owner_sales_price,
p.cost_price,
p.unit,
p.goods_describe,
p.states,
p.skill_of_technician,
p.technician_sales_price,
p.parts_type,
p.parts_spec,
p.version_no,
p.is_activity,
imi.img_url
FROM goods_of_parts t
INNER JOIN goods_terminal_display gd ON gd.goods_id=t.goods_id AND gd.terminal_val=1
LEFT JOIN parts_config p ON p.id = t.parts_id
LEFT JOIN img_manager_info imi on imi.business_tab='parts_config' and imi.business_id=p.id
LEFT JOIN car_parts_of_model cm1 on cm1.car_brand_id=1929 and cm1.car_audi_id=1930 and cm1.car_model_id=19768 and cm1.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm2 on cm2.car_brand_id=1929 and cm2.car_audi_id=1930 and cm2.car_model_id=0 and cm2.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm3 on cm3.car_brand_id=1929 and cm3.car_audi_id=0 and cm3.car_model_id=0 and cm3.car_parts_id=p.id
LEFT JOIN car_parts_of_model cm4 on cm4.car_brand_id=0 and cm4.car_audi_id=0 and cm4.car_model_id=0 and cm4.car_parts_id=p.id
WHERE t.parts_id>0
and (cm1.id>0 or cm2.id>0 or cm3.id>0 or cm4.id>0)
ORDER BY goods_id,parts_classify,total_amount
) t1
GROUP BY goods_id,parts_group
ORDER BY goods_id,parts_group desc;