MySQL与mabits大小比较、日期比较示例-优快云博客

本文介绍如何将MySQL中查询未来60天房屋使用记录的SQL语句转换为MyBatis的XML配置文件，并提供了详细的代码示例。转换过程中涉及条件判断、日期操作及结果分组。

首先，使用mysql查询从今往后的60天数据

SELECT
	count(*),
	b1.record_date
FROM
	nk_house_use_record AS b1,
	(
		SELECT
			a.theme_id,
			a.theme_name,
			b.house_id,
			b.house_name
		FROM
			nk_house_theme AS a,
			nk_house AS b
		WHERE
			a.theme_id = '08a649fd-b83e-41b5-b88b-99598d4c93fe'
		AND a.theme_id = b.theme_id
		AND a.state = '1'
		AND b.state = '1'
	) AS a1
WHERE
	a1.house_id = b1.house_id
AND time1 = '0'
AND record_date >= CURDATE() #'2016-06-05' 
AND record_date < DATE_SUB(CURDATE(), INTERVAL - 60 DAY)
GROUP BY
	b1.record_date

其次，移植到mabits中

<select id="queryFreeHouseCalender" resultType="com.cn.front.entity.detail.FreeHouseCalender" parameterType="com.cn.front.entity.input.QueryFreeHouse">
        SELECT
            count(*) AS num,
            b1.record_date
        FROM
            nk_house_use_record AS b1,
            (
                SELECT
                    a.theme_id,
                    a.theme_name,
                    b.house_id,
                    b.house_name
                FROM
                    nk_house_theme AS a,
                    nk_house AS b
                WHERE
                    a.theme_id = #{themeID}
                AND a.theme_id = b.theme_id
                AND a.state = '1'
                AND b.state = '1'
            ) AS a1
        WHERE
            a1.house_id = b1.house_id            
        <if test="timeKey == '1' ">and time1 = '0'</if>                
        <if test="timeKey == '2' ">and time2 = '0'</if>                
        <if test="timeKey == '3' ">and time3 = '0'</if>                
        <if test="timeKey == '4' ">and time4 = '0' and time5='0'</if>    
        
        <if test="startDate != null">AND record_date &gt;=  #{startDate}</if>    
        <if test="endDate != null">AND record_date &lt;= #{endDate}</if>    
        
        <if test="startDate == null">AND Date(record_date) &gt;= CURDATE() </if>    
        <if test="endDate == null">AND Date(record_date) &lt; DATE_SUB(CURDATE(), INTERVAL - 60 DAY) </if>            
        GROUP BY
            b1.record_date
    </select>