本文目录
一、insert语句
二、select查询语句
三、批量添加
四、与时间比较相关的
项目中经常会用到的sql语句有：
一、insert语句
<!--保存用户信息-->
<insert id="save">
insert into mainsite_product_message
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="customerName != null and customerName != ''">
customer_name,
</if>
<if test="customerPhone != null and customerPhone != ''">
customer_phone,
</if>
<if test="customerEmail != null and customerEmail != ''">
customer_email,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="customerStatus != null and customerStatus != ''">
customer_status,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="customerName != null and customerName != ''">
#{customerName},
</if>
<if test="customerPhone != null and customerPhone != ''">
#{customerPhone},
</if>
<if test="customerEmail != null and customerEmail != ''">
#{customerEmail},
</if>
<if test="createTime != null">
#{createTime},
</if>
<if test="customerStatus != null and customerStatus != ''">
#{customerStatus},
</if>
</trim>
</insert>
<!--修改用户信息-->
<update id="update">
</update>
<!--批量更改客户状态-->
<update id="updateStatus">
update mainsite_product_message set customer_status = 1 where id in
<foreach collection="array" item="id" open="(" separator="," close=")" index="index">
#{id}
</foreach>
</update>
<!--根据主键删除留言，物理删除-->
<delete id="deleteByPrimaryKey">
delete from mainsite_product_message where id = #{id}
</delete>
<!--查询留言列表-->
<select id="selectByCondition"
resultType="com.uiotsoft.mainsite.module.productmessage.entity.ProductMessage">
select
id as id,
site_id as siteId,
site_name as siteName,
customer_name as customerName,
customer_phone as customerPhone,
customer_email as customerEmail,
product_want as productWant,
create_time as createTime,
customer_status as customerStatus,
search_keyword as searchKeyword
from mainsite_product_message
<trim prefix="WHERE (" suffix=")" prefixOverrides="AND|OR">
<if test="customerName != null and customerName != ''">
customer_name like CONCAT('%', #{customerName}, '%')
</if>
<if test="customerPhone != null and customerPhone != ''">
and customer_phone = #{customerPhone}
</if>
<if test="createTime != null">
and create_time = #{createTime}
</if>
<if test="customerEmail != null and customerEmail != ''">
and customer_email = #{customerEmail}
</if>
<if test="productWant != null and productWant != ''">
and product_want = #{productWant}
</if>
<if test="siteId != null and siteId != '' and siteId != 1">
and site_id = #{siteId}
</if>
<if test="siteName != null and siteName != ''">
and site_name = #{siteName}
</if>
<if test="customerStatus != null and customerStatus != ''">
and customer_status = #{customerStatus}
</if>
<if test="searchKeyword != null and searchKeyword != ''">
and search_keyword = #{searchKeyword}
</if>
</trim>
ORDER BY create_time DESC
</select><trim><choose><when></when><otherwise></otherwise></choose></trim>
二、select查询语句
<select id="selectByContentListBySiteIdAndCategoryIds" resultType="java.util.Map">
SELECT
c.*,
m.*,
u.avatar avatar,
u.username username
FROM
site_model_content c LEFT JOIN site_model_${tableName} m
ON
c.content_id = m.content_id
LEFT JOIN
t_sys_user u ON u.user_id = c.user_id
<trim prefix="WHERE (" suffix=")" prefixOverrides="AND|OR">
c.status=1
<if test="siteId!=null">
AND c.site_id = #{siteId}
</if>
<if test="categoryIds!=null">
AND
c.category_id in
<foreach item="item" index="index" collection="categoryIds" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test='isRecommend!=null and isRecommend==1'>
AND
c.recommend = 1
</if>
<if test='whereParam!=null and valueParam!=null'>
AND m.${whereParam} = #{valueParam}
</if>
<if test='isPic!=null and isPic==1'>
AND
c.thumb!=''
</if>
<if test='isPic!=null and isPic==0'>
AND
c.thumb=''
</if>
<if test='siteType!=null'>
AND
c.site_type like CONCAT('%', #{siteType}, '%')
</if>
</trim>
<choose>
<when test="orderBy==1">
order by c.content_id
</when>
<when test="orderBy==2">
order by c.inputdate DESC
</when>
<when test="orderBy==3">
order by c.inputdate
</when>
<when test="orderBy==4">
order by c.updatedate DESC
</when>
<when test="orderBy==5">
order by c.updatedate
</when>
<when test="isHot==1">
order by c.view_num DESC
</when>
<otherwise>
order by c.content_id DESC
</otherwise>
</choose>
</select>三、批量添加
<!-- 批量添加 -->
<insert id="insertMore" useGeneratedKeys="true" keyProperty="siteId">
INSERT INTO
t_cms_site(
site_name,
`domain`,
template,
is_mobile,
mobile_tpl,
status
)
values
<foreach item="item" collection="list" separator=",">
(#{item.siteName},#{item.domain},#{item.template},
#{item.isMobile},#{item.mobileTpl},#{item.status})
</foreach>
</insert>四、与时间比较相关的
SELECT
count(*)
FROM
crm_contract
<where> isDel = 0
<if test="departmentId != null and departmentId != null">
and departmentId LIKE CONCAT(#{departmentId},'%')
</if>
<if test="directorAccount != null and directorAccount !='' ">
and directorAccount = #{directorAccount}
</if>
<if test="executeState != null and executeState != ''">
and executeState = #{executeState}
</if>
<if test="auditState != null and auditState != ''">
and auditState = #{auditState}
</if>
<if test="contractType != null and contractType != ''">
and contractType = #{contractType}
</if>
<if test="categoryId != null and categoryId != ''">
and categoryId = #{categoryId}
</if>
<if test="customerName != null and customerName != ''">
and customerName like CONCAT("%",#{customerName},"%")
</if>
<if test="director != null and director != ''">
and director like CONCAT("%",#{director},"%")
</if>
<if test="signUser != null and signUser != ''">
and signUser like CONCAT("%",#{signUser},"%")
</if>
<if test="startDate != null">
<![CDATA[ and DATE_FORMAT(endDate,"%Y-%m-%d") >= DATE_FORMAT(#{startDate},"%Y-%m-%d") ]]>
</if>
<if test="endDate != null">
<![CDATA[ and DATE_FORMAT(endDate,"%Y-%m-%d") <= DATE_FORMAT(#{endDate},"%Y-%m-%d")]]>
</if>
</where>select
c.*
from crm_contract c left join crm_customer_info i on c.customerId =
i.customerId
<where>
c.isDel = 0
<if test="startDate != null and startDate != ''">
and c.endDate >= #{startDate}
</if>
<if test="endDate != null and endDate != ''">
and c.endDate <= #{endDate}
</if>
<if test="customerName != null and customerName != ''">
and c.customerName like CONCAT(#{customerName},"%")
</if>
<if test="director != null and director != ''">
and c.director like CONCAT(#{director},"%")
</if>
<if test="auditState != null and auditState != ''">
and c.auditState = #{auditState}
</if>
</where>
limit #{start},#{end}<select id="selectAll" resultType="java.util.Map">
SELECT
log_id AS logId,
page_name AS pageName,
template_name AS templateName,
publish_status AS publishStatus,
consuming_time AS consumingTime,
C.description,
create_time AS createTime,
C.site_id AS siteId,
C.category_id AS categoryId,
C.site_name as siteName,
D.category_name as categoryName
FROM
(
SELECT
log_id,
page_name,
template_name,
publish_status,
consuming_time,
A.description,
create_time,
A.site_id,
category_id,
site_name
FROM
mainsite_publish_log A
LEFT JOIN t_cms_site B ON A.site_id = B.site_id
) C
LEFT JOIN t_cms_category D ON (C.category_id = D.category_id)
<where>
<if test="startTime != null">
<![CDATA[ and DATE_FORMAT(create_time,"%Y-%m-%d %H:%i:%s") >= DATE_FORMAT(#{startTime},"%Y-%m-%d %H:%i:%s") ]]>
</if>
<if test="endTime != null">
<![CDATA[ and DATE_FORMAT(create_time,"%Y-%m-%d %H:%i:%s") <= DATE_FORMAT(#{endTime},"%Y-%m-%d %H:%i:%s") ]]>
</if>
</where> order by log_id desc
</select>
拓展：
Mysql动态SQL语句标签
完结！
标签：xml,customer,name,sql,site,status,SQL,id,CONCAT From： https://blog.51cto.com/u_16128050/6342864