Mysql根据时间/月份进行分组,附SQL中日期字符串互转

>Mysql根据时间的月份进行分组查询

sql:

SELECT DATE_FORMAT(create_time,'%Y%m') months FROM student GROUP BY months

mapper中：

List<String> hasmonths();

xml中：

<select id="hasmonths" resultType="java.lang.String">
     SELECT DATE_FORMAT(create_time,'%Y%m') months FROM biao  GROUP BY months
</select>

实际例子:

String format = "%Y-%m";
或者
String format = "%Y-%m-%d";

mapper

List<StatisticsObjectAndDoubleDTO> findInStock(String storageCode, String storehouseCode, Date parseDate, @Param("format") String format, List<String> storageCodeList);

xml

<select id="findInStock" resultType="com.boot.reservation.entity.dto.StatisticsObjectAndDoubleDTO">
        select min(ifnull(DATE_FORMAT(unload_end_time,#{format}),"1987-01-01")) as name,ifnull(sum(settlement_num),0) as num
        from storage_in_stock
        where status = 02
        <if test="storageCode != null and storageCode != ''">
            and granary_address = #{storageCode}
        </if>
        <if test="storehouseCode != null and storehouseCode != ''">
            and house = #{storehouseCode}
        </if>
        <if test="parseDate != null