记录一下常用的sql语句

求和并且四舍五入

SELECT ROUND(SUM(cost_vue),2) FROM gao_cost where 1=1;

mybatis循环,in语句

<if test="integer_array != null">
		and org_id IN
	<foreach collection="integer_array" item="id" index="index"
		open="(" close=")" separator=",">
         #{id}
	</foreach>
</if>

递归查询父ID下的所有数据的sql

SELECT level, DATA.* FROM(
		SELECT
		@ids as _ids,
		( SELECT @ids := GROUP_CONCAT(dic_id)
		FROM gao_dic
		WHERE FIND_IN_SET(parent_id, @ids)
		) as cids,
		@l := @l+1 as level
		FROM gao_dic,
		(SELECT @ids :=#{dic_id}, @l := 0 ) b
		WHERE @ids IS NOT NULL
		) dic_id, gao_dic DATA
		WHERE parent_id!=0 and  FIND_IN_SET(DATA.dic_id, _ids)
		ORDER BY level, dic_id;

sql语句下的两种查询时间区间的方式

#第一种
<if test="start_task_end_time != null and start_task_end_time != ''"> 
	AND task_end_time <![CDATA[ >=  ]]>#{start_task_end_time}
</if>
<if test="end_task_end_time != null and end_task_end_time != ''"> 
	AND task_end_time <![CDATA[ <=  ]]>#{end_task_end_time}
</if>
#第二种
<if test="tijiao_start_time != null">
	and project_time &gt;= #{tijiao_start_time}
</if>
<if test="tijiao_end_time != null">
	and project_time &lt;= #{tijiao_end_time}
</if>

根据角色-员工-菜单以及中间表查询的权限菜单sql语句，并且去除多角色的重复菜单

select distinct(m.menu_id),m.menu_name,m.menu_link,m.parent_id,m.menu_target from
		heat_employee e LEFT JOIN heat_employee_role er ON e.employee_id=er.employee_id
		LEFT JOIN heat_role r ON er.role_id=r.role_id
		LEFT JOIN heat_role_menu rm ON
		r.role_id=rm.role_id
		LEFT JOIN heat_menu m ON rm.menu_id=m.menu_id
		<where>
			1=1
			<if test="employee_id != null">
				and e.employee_id=#{employee_id}
			</if>
			<if test="parent_id_no != null">
				AND m.parent_id !=0
			</if>
			<if test="parent_id == 0">
				AND m.parent_id =0
			</if>
			<if test="parent_id != null">
				AND m.parent_id =#{parent_id}
			</if>
			<if test="menu_leave != null">
				AND m.menu_leave =#{menu_leave}
			</if>
			<if test="menu_state != null">
				AND m.menu_state =#{menu_state}
			</if>
			<if test="menu_type != null">
				AND m.menu_type =#{menu_type}
			</if>
		</where>
		ORDER BY m.menu_sort ASC