数据库日期

最新推荐文章于 2024-12-01 18:01:21 发布

转载最新推荐文章于 2024-12-01 18:01:21 发布 · 159 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/gavin-yao/p/10531698.html

文章标签：

#数据库 #java

博客主要介绍了MySQL中大于等于、小于等于的写法。因在MyBatis框架的xml中，<= 和 >= 解析会报错，需进行转译，并给出了两种写法示例。还展示了按年、月、周查询开发者下已上线应用以及按条件分页查询用户操作日志的SQL语句。

<select id="selectAppByYearAndDevId" resultType="java.util.Map">
SELECT
app_name AS appName,
app_id As appId,
DATE_FORMAT(create_time, '%Y') AS date
FROM
aisv_g_app_info
WHERE
DATE_FORMAT(create_time, '%Y') = #{year}
AND is_del = 0
AND app_state = 5
AND developer_id = #{developerId}
</select>


<select id="selectAppByMonthAndDevId" resultType="java.util.Map">
SELECT
app_name AS appName,
app_id As appId,
DATE_FORMAT(create_time, '%Y-%m') AS date
FROM
aisv_g_app_info
WHERE
DATE_FORMAT(create_time, '%Y-%m') = #{month}
AND is_del = 0
AND app_state = 5
AND developer_id = #{developerId}
</select>


<select id="selectAppByWeekAndDevId" resultType="java.util.Map">
SELECT
app_name AS appName,
app_id As appId,
DATE_FORMAT(create_time, '%Y-%m-%d') AS date
FROM
aisv_g_app_info
WHERE
DATE_FORMAT(create_time,'%Y-%m-%d') <![CDATA[ <= ]]>#{todayStr} AND
DATE_FORMAT(create_time,'%Y-%m-%d')<![CDATA[ >= ]]>#{sevenDayBeforeStr}
AND is_del = 0
AND app_state = 5
AND developer_id = #{developerId}
</select>

SELECT *
FROM
aisv_g_user_operation_log
<where>
1=1
<if test="param.userName!= null and param.userName !=''">
and
user_name =#{param.userName}
</if>
<if test="param.userIp != null and param.userIp !=''">
and
USER_IP =#{param.userIp }
</if>
<if test="param.operationType != null and param.operationType !=''">
and
OPERATION_TYPE =#{param.operationType}
</if>
<if test="param.startdate != null and param.startdate !=''">
and
OPERATION_TIME >=#{param.startdate}
</if>
<if test="param.enddate != null and param.enddate !=''">
and
OPERATION_TIME <=#{param.enddate}
</if>
</where>
order by OPERATION_TIME DESC
limit #{page.begin},#{page.pageSize}
</select>

@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd")
private LocalDateTime transactionDate;

MySQL中大于等于小于等于的写法

由于在mybatis框架的xml中<= , >=解析会出现问题,编译报错,所以需要转译
第一种写法：

原符号 < <= > >= & ’ "
替换符号 < <= > >= & ' "
例如：sql如下：

unix_timestamp(target.mc_end_date)&lt;= unix_timestamp(#{MonthEndTime})；unix_timestamp(target.mc_start_date) &gt;= unix_timestamp(#{MonthBeginTime})

第二种写法：
大于等于

<![CDATA[ >= ]]>

小于等于

<![CDATA[ <= ]]>

例如：sql如下：

mc_end_date <![CDATA[ >= ]]> #{endTime} and  mc_start_date <![CDATA[ <= ]]> #{startTime}

转载于:https://www.cnblogs.com/gavin-yao/p/10531698.html