when case then:根据某字段不同联表不同表查询&根据字段不同查询条件不同

最新推荐文章于 2021-01-19 13:02:51 发布

武六七

最新推荐文章于 2021-01-19 13:02:51 发布

阅读量2.8k

点赞数 2

分类专栏： SQL 文章标签： when case then mySQL 不同字段连不同表不同字段条件不同

本文链接：https://blog.csdn.net/w567wzf/article/details/100116477

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

两种情况的模板例：

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