两种情况的模板例:
1.根据某字段不同连表不同表
type = 1:联商品表
type = 2:联项目表
select t1.id,
case
when t1.type = 1 THEN t3.product_name
END as product_name,
case
when t1.type = 2 THEN t4.project_name
END as project_name
from bus_order_item t1
left join bus_order t2 on t1.order_id = t2.id
left join bus_product t3 on t1.product_id = t3.id
left join bus_project t4 on t1.project_id = t14.id
2.根据字段不同查询条件不同
SELECT t1.id,
CASE
WHEN t1.type = 1 THEN t3.product_name
END ASproduct_name,
CASE
WHEN t1.type = 2 THEN t4.project_name
END as project_name
from bus_order_item t1
LEFT JOIN bus_order t2 on t1.order_id = t2.id
LEFT JOIN bus_product t3 on t1.product_id = t3.id
LEFT JOIN bus_project t4 on t1.project_id = t14.id
WHERE
CASE
WHEN t1.type = 2 THEN DATEDIFF(t1.create_date,NOW()) = 0
WHEN t1.type = 1 THEN 1 = 1
END
AND 1 = 1
3.根据值不同查询不同字段(并拼接)
select t1.id,
case when t2.grade is null then '0分' else CONCAT(t2.grade,'分') end grade,
t1.assess_date,t3.nurse_name
#CONCAT(t2.grade,'分') as grade
from bus_assess_his t1
LEFT JOIN bus_assess_his_item t2 ON t1.id = t2.assess_his_id
LEFT JOIN bus_nurse t3 ON t1.nurse_id = t3.id
where t1.patient_id = 70
ORDER BY t1.create_date DESC
4.根据值不同查询不同字段
select t2.student_name as studentName, t2.sex, t2.tel, t3.course_name as courseName, t5.org_id as orgId,
CASE t4.charge_type
WHEN 1 THEN (t4.contract_amount - t4.retreat_amount - t4.surplus_amount)
WHEN 2 THEN t4.contract_amount
END as remainAmount
from lq_class_student t1
left join lq_student t2 on t1.student_id = t2.student_id
left join lq_course t3 on t1.course_id = t3.course_id
left join lq_purchase_record t4 on t1.purchase_record_id = t4.purchase_record_id
left join lq_class t5 on t1.class_id = t5.class_id