mysql 查询一年中每月的数据数量 返回月份 和数量 返回值写什么

Question

 <mapper namespace="sxkj.agriculture.admin.repository.TabMemberDao">
    <resultMap id="tabMemberMap" type="sxkj.agriculture.admin.entity.TabMember">
        <result property="memberid" column="memberID" />
        <result property="nickname" column="nickname" />
        <result property="loginname" column="loginName" />
        <result property="password" column="password" />
        <result property="avatarimg" column="avatarImg" />
        <result property="addressid" column="addressID" />
        <result property="integral" column="integral" />
        <result property="type" column="type" />
        <result property="deleted" column="deleted" />
        <result property="createtime" column="createTime" />
        <result property="deliverytime" column="deliveryTime" />
        <association property="tabAddress" javaType="sxkj.agriculture.admin.entity.TabAddress">
            <id property="addressid" column="addressID" />
            <result property="receiveraddress" column="receiverAddress" />
            <result property="memberid" column="memberID" />
            <result property="receivername" column="receiverName" />
            <result property="receiverphone" column="receiverPhone" />
            <result property="deleted" column="deleted" />
        </association>

        <association  property="statistics" javaType="sxkj.agriculture.admin.entity.Statistics">
            <result property="yeas" column="yeas" />
            <result property="count" column="count" />
        </association>
    </resultMap>

 <select id="searchMenberCount" parameterType="map" resultType="sxkj.agriculture.admin.entity.Statistics">
         SELECT str_to_date(createTime,'%Y-%m') years,count(1)  FROM tab_member where deleted=1 and str_to_date(createTime,'%Y-%m-%d')  BETWEEN str_to_date(#{startTime} ,'%Y-%m-%d') and str_to_date(#{endTime},'%Y-%m-%d')  GROUP BY YEAR(createTime), month(createTime)
    </select>

 List<Statistics> searchMenberCount(@Param("startTime") String startTime, @Param("endTime") String endTime);

Answer 1

可以定义个dto类，里面有月份和数量属性。
public class SearchDto{
private String years；//月份
private Integer count; //数量

    //get,set方法

}
在mybatis的sql xml 文件里面定义该类的resultMap。

sql语句的配置：

SELECT str_to_date(createTime,'%Y-%m') years,count(1) as num FROM tab_member where deleted=1 and str_to_date(createTime,'%Y-%m-%d') BETWEEN str_to_date(#{startTime} ,'%Y-%m-%d') and str_to_date(#{endTime},'%Y-%m-%d') GROUP BY YEAR(createTime), month(createTime)

 返回值就写  List<SearchDto>