count统计，mybatis

sql count统计先查询一个表内的相同列名有几个，然后查询这些有多少数量，再附带一些其他属性

select status='1' as status,name,device_model,province,city,district,address,count(*) as badcount from lock_device 
	where province like '福建'
        group by name

因为有用到group by分组，所以再mybatis的xml模糊查询group by 得写在后面

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lock.main.mapper.LockCountMapper">

    <resultMap type="LockCount" id="LockCountResult">
        <result property="status" column="status"    />
        <result property="name" column="name"    />
        <result property="deviceModel" column="device_model"    />
        <result property="province" column="province"    />
        <result property="city" column="city"    />
        <result property="district" column="district"    />
        <result property="address" column="address"    />
        <result property="badcount" column="badcount"    />
    </resultMap>

    <sql id="selectLockCountVo">
        <!-- select id, status='1', create_by, create_time, update_by, update_time, name, picture, alias, sn, version, time_zone, device_model, mac, ip, province, city, district, address, register_time, warranty_period, customer_id, power, volume_sound from lock_device-->
        select status='1' as status,name,device_model,province,city,district,address,count(*) as badcount from lock_device
        group by name
    </sql>

    <select id="selectLockCountList" parameterType="LockCount" resultMap="LockCountResult">
       <!--  <include refid="selectLockCountVo"/> -->
        select status='1' as status,name,device_model,province,city,district,address,count(*) as badcount from lock_device
        <where>
            <if test="name != null  and name != ''"> and name like concat('%', #{name}, '%')</if>
            <if test="deviceModel != null "> and device_model like concat('%', #{deviceModel}, '%')</if>
            <if test="province != null  and province != ''"> and province like concat('%', #{province}, '%')</if>
            <if test="city != null  and city != ''"> and city like concat('%', #{city}, '%')</if>
            <if test="district != null  and district != ''"> and district like concat('%', #{district}, '%')</if>
            <if test="address != null  and address != ''"> and address like concat('%', #{address}, '%')</if>
        </where>
        group by name
    </select>

</mapper>