mysql需求处理中使用sql整理

然后是一个我这边犯得错误，实话说我是觉得挺蠢的。

需求

一个流程的结束之后，业务的主表联查子表，将数据插入到另外的一个表中，问题就出现在了一个帮助表，这个帮助表需要插入一个id，这个帮助表的联查，需要通过两个参数，一个参数是type一个参数是level，当这两个符合的时候，就能够确定这个帮助表，然后问题就是出现在了这个帮助表，帮助表有一条数据，type唯一并且level是自定义的，所以导致的某一条数据需要特殊处理。

处理

		select per.employee_number,
			   per.last_name,
			   o2.ORGANIZATION_ID   as cityid,
			   o2.ORGANIZATION_NAME as cityName,
			   o1.ORGANIZATION_ID   as organizationId,
			   o1.ORGANIZATION_NAME as organizationName,
			   detail.punish_start_time,
			   detail.effect_time_end,
			   detail.effect_year_end,
			   detail.punish_reason,
			   detail.punish_give_unit,
			   detail.punish_remark,
			   '档案材料收集',
			   per1.last_name,
			   per1.last_name,
			   eff.id,
			   detail.punish_level
		from sdda.ad_materials_regular_collection_detail detail
				 left join sdda.ad_materials_regular_collection col on col.id = detail.collection_id
				 left join sdda.staff_archives_info staff on staff.uuid = detail.uuid
				 left join dhr.per_person_info per on staff_no = employee_number
				 left join dhr.g_organization o1 on per.organization_id = o1.ORGANIZATION_ID
				 left join dhr.g_organization o2 on o1.ORG5 = o2.ORGANIZATION_ID
				 left join dhr.per_person_info per1 on detail.updateBy = per1.employee_number
				 left join leader.leader_punish_effect eff
						   on ( (eff.punish_type = detail.punish_type and eff.punish_level = detail.punish_level)or(eff.punish_type = '扣罚'))
		where col.id = #{bussId}

ad_materials_regular_collection_detail 业务明细表，ad_materials_regular_collection 业务主表，leader_punish_effect 帮助表，实话说到目前为止，我单看条件还是感觉没有问题，不过应该是left join导致的，( (eff.punish_type = detail.punish_type and eff.punish_level = detail.punish_level)or(eff.punish_type = ‘扣罚’))明显我的意思是，当type和level都相等的时候或者type = '扣罚’的时候，但是因为是left join所以导致了其他问题。

		select per.employee_number,
			   per.last_name,
			   o2.ORGANIZATION_ID   as cityid,
			   o2.ORGANIZATION_NAME as cityName,
			   o1.ORGANIZATION_ID   as organizationId,
			   o1.ORGANIZATION_NAME as organizationName,
			   detail.punish_start_time,
			   detail.effect_time_end,
			   detail.effect_year_end,
			   detail.punish_reason,
			   detail.punish_give_unit,
			   detail.punish_remark,
			   '档案材料收集',
			   per1.last_name,
			   per1.last_name,
			   eff.id,
			   detail.punish_level
		from sdda.ad_materials_regular_collection_detail detail
				 left join sdda.ad_materials_regular_collection col on col.id = detail.collection_id
				 left join sdda.staff_archives_info staff on staff.uuid = detail.uuid
				 left join dhr.per_person_info per on staff_no = employee_number
				 left join dhr.g_organization o1 on per.organization_id = o1.ORGANIZATION_ID
				 left join dhr.g_organization o2 on o1.ORG5 = o2.ORGANIZATION_ID
				 left join dhr.per_person_info per1 on detail.updateBy = per1.employee_number
				 left join leader.leader_punish_effect eff on eff.punish_type = detail.punish_type and ((eff.punish_level = detail.punish_level)or(detail.punish_type = '扣罚'))
		where col.id = #{bussId}

然后改了一个括号的位置位置，我的sql确实太差了