工作总结12.25（group by，模糊查询（concat），时间范围查询，SYSDATE()函数，删除字符串数组，ifnull，concat查询）

1. GROUP BY 的应用场景

GROUP BY 语句用来与聚合函数(aggregate functions such as COUNT, SUM, AVG, MIN, or MAX.)联合使用来得到一个或多个列的结果集。

举例

比如说我们有一个学生表格(student)，包含学号(id)，课程(course)，分数(score)等等多个列，我们想通过查询得到每个学生选了几门课程，此时我们就可以联合使用COUNT函数与GROUP BY语句来得到这一结果

SELECT id, COUNT(course) as numcourse

FROM student

GROUP BY id

因为我们是使用学号来进行分组的，这样COUNT函数就是在以学号分组的前提下来实现的，通过COUNT(course)就可以计算每一个学号对应的课程数。

注意

因为聚合函数通过作用于一组数据而只返回一个单个值，因此，在SELECT语句中出现的元素要么为一个聚合函数的输入值，要么为GROUP BY语句的参数，否则会出错。

例如，对于上面提到的表格，我们做一个这样的查询：

SELECT id, COUNT(course) as numcourse, score

FROM student

GROUP BY id

此时查询便会出错，错误提示如下：

Column ‘student.score' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY clause.

出现以上错误的原因是因为一个学生id对应多个分数，如果我们简单的在SELECT语句中写上score，则无法判断应该输出哪一个分数。如果想用score作为select语句的参数可以将它用作一个聚合函数的输入值，如下例，我们可以得到每个学生所选的课程门数以及每个学生的平均分数：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

也可以

SELECT id, COUNT(course) as numcourse, score

FROM student

GROUP BY id，score

HAVING

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

语法：

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
 

同样使用本文中的学生表格，如果想查询平均分高于80分的学生记录可以这样写：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错

2. 实践案例

SELECT A.YPID,A.YPMC,count(B.YPID) SL from  MS_YLJK_YLFW_YBYP A LEFT JOIN MS_YLJK_YLFW_YBYPXQ B ON A.YPID=B.YPID
    	
   WHERE A.YPMC like '%1%'
	    
   GROUP BY A.YPID,A.YPMC,B.YPID

3. 模糊查询

<select id="getByPage" resultType="com.test.domain.Users" parameterType="com.test.Param">

SELECT * FROM tb_users where isdeleted=1 
<if test="name!=null and name!=''">
     AND nickname LIKE CONCAT('%', '${name}', '%')
</if>
ORDER BY createtime DESC
limit #{fromIndex},#{count}
</select>

4. 时间范围查询

<sql id="selectConfigVo">
        select config_id, config_name, config_key, config_value, config_type, create_by, create_time, update_by, update_time, remark 
		from sys_config
    </sql>

  <select id="selectConfigList" parameterType="Config" resultMap="ConfigResult">
        <include refid="selectConfigVo"/>
        <where>
			<if test="configName != null and configName != ''">
				AND config_name like concat('%', #{configName}, '%')
			</if>
			<if test="configType != null and configType != ''">
				AND config_type = #{configType}
			</if>
			<if test="configKey != null and configKey != ''">
				AND config_key like concat('%', #{configKey}, '%')
			</if>
			<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
				and date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
			</if>
			<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
				and date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
			</if>
		</where>
    </select>
    

5. SYSDATE()函数

SYSDATE()函数将返回当前日期时间，格式为“YYYY-MM-DD HH:MM:SS”的值，以防在函数用于数字上下文。
SYSDATE()函数接受一个可选参数fsp，它确定结果是否应该包含从0到6的小数秒精度。
请参见以下示例 - 
mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2017-08-10 20:43:16 |
+---------------------+
1 row in set

<update id="updateConfig" parameterType="Config">
        update sys_config 
        <set>
            <if test="configName != null and configName != ''">config_name = #{configName},</if>
            <if test="configKey != null and configKey != ''">config_key = #{configKey},</if>
            <if test="configValue != null and configValue != ''">config_value = #{configValue},</if>
            <if test="configType != null and configType != ''">config_type = #{configType},</if>
            <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
            <if test="remark != null">remark = #{remark},</if>
 			update_time = sysdate()
        </set>
        where config_id = #{configId}
    </update>

6. 删除一个字符串数组

public int deleteConfigByIds(String[] configIds);

<delete id="deleteConfigByIds" parameterType="String">
        delete from sys_config where config_id in 
        <foreach item="configId" collection="array" open="(" separator="," close=")">
        	#{configId}
        </foreach>
    </delete>

7. ifnull用法

<select id="selectMenuAllByUserId" parameterType="Long" resultMap="MenuResult">
		select distinct m.menu_id, m.parent_id, m.menu_name, m.url, m.visible, ifnull(m.perms,'') as perms, m.target, m.menu_type, m.icon, m.order_num, m.create_time
		from sys_menu m
			 left join sys_role_menu rm on m.menu_id = rm.menu_id
			 left join sys_user_role ur on rm.role_id = ur.role_id
			 LEFT JOIN sys_role ro on ur.role_id = ro.role_id
		where ur.user_id = #{userId}
		order by m.parent_id, m.order_num
	</select>

8. concat查询

将两个字段查询出来的结果拼接在一起

select concat(m.menu_id, ifnull(m.perms,'')) as perms
		from sys_menu m
			left join sys_role_menu rm on m.menu_id = rm.menu_id
		where rm.role_id = 2
		order by m.parent_id, m.order_num