sql大全_终身受用-时刻更新
1、Map类型的数据进行inset插入
有时候需要简单地把一个Map中所有的key和value获取出来，拼到sql语句中。MyBatis提供的一种方法是遍历Map中的entrySet，然后把key扔进index里面，value扔进item中。具体的一个使用的例子如下：
<insert id="operationName" parameterType="map">
INSERT INTO table_name(hot_word, cnt)
VALUES
<foreach item="value" index="key" collection="mapData.entrySet()" open="(" separator="),(" close=")">
#{key}, #{value}
</foreach>
ON DUPLICATE KEY UPDATE
cnt=VALUES(cnt)
</insert>
2、获取数据结果为list<map<String,Object>>
1、mapper
public interface HealerJeanMapper {
List<Map<String,Object>> sqlMap();
}
2、mapper.xml
<select id="sqlMap" resultType="java.util.HashMap">
SELECT h.id as id ,h.subject as subject FROM healerjean h;
</select>
3、解释：
1、返回类型必须是java.util.HashMap
2、map中的value 必须是Objecrt
4、controller测试
@RequestMapping("sqlMap")
@ResponseBody
public List<Map<String,Object>> sqlMap(){
return healerJeanMapper.sqlMap();
}
[外链图片转存失败(img-NdRiKKK0-1566553583165)(https://raw.githubusercontent.com/HealerJean/HealerJean.github.io/master/blogImages/WEFASDFUMM.png)]
3、mybatis 自定义参数、返回类型map
1、参数
<select id="getUserCount" parameterMap="getUserCountMap" statementType="CALLABLE"resultType="java.util.HashMap
>
CALL ges_user_count(?,?)
</select>
<parameterMap type="java.util.Map" id="getUserCountMap">
<parameter property="sexid" mode="IN" jdbcType="INTEGER"/>
<parameter property="userCount" mode="OUT" jdbcType="INTEGER"/>
</parameterMap>
2、结果，返回字段一一匹配，想想之前@Results和@Result
<resultMap id="BaseResultMap" type="com.taotao.pojo.TbUser" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="created" property="created" jdbcType="TIMESTAMP" />
<result column="updated" property="updated" jdbcType="TIMESTAMP" />
</resultMap>
3、解释
property和column分别指定实体类属性和数据表的列名。
4、If的使用
1、mapper接口
public interface CustomerMapper {
List<Customer> findCustomerList( @Param("name") String name,
@Param("status") Integer status,
@Param("offset") Integer offset,
@Param("limit") Integer limit);
}
2、mapper.xml sql’语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.duodian.admore.dao.mybatis.customer.CustomerMapper">
<select id="findCustomerList" resultType="com.duodian.admore.entity.db.customer.Customer">
SELECT t.* from crm_customer t
WHERE t.isVisiblisVisiblee = 1
<if test="name != null and name != ''">
and t.name like CONCAT('%','${name}','%' )
</if>
<if test="status != null">
and t.status = #{status}
</if>
order by t.id DESC
limit #{offset}, #{limit}
</select>
<select id="countCustomerList" resultType="java.lang.Long">
select count(*) from crm_customer t
WHERE t.isVisible = 1
<if test="name != null and name != ''">
and t.name like CONCAT('%','${name}','%' )
</if>
<if test="status != null">
and t.status = #{status}
</if>
</select>
</mapper>
5、where include的使用（ 加入模糊查询筛选条件）自带去除第一个and
5.1、mapper
@Override
public Page<SignetApplyRecord> findVerifySignetData(Long admId, Pageable pageable, SignetQuery query) {
query.setStartDate(DateHelper.getDateFirstTime(query.getStartDate()));
query.setEndDate(DateHelper.getDateLastTime(query.getEndDate()));
query.setOffset(pageable.getOffset());
query.setLimit(pageable.getPageSize());
List<SignetApplyRecord> list = signetMapper.findVerifySignetList(query);
Long count = signetMapper.countVerifySignet(query);
return new PageImpl<>(list, pageable, count);
}
5.2、xml
<select id="findVerifySignetList" resultType="com.duodian.admore.entity.db.signet.SignetApplyRecord">
SELECT t.*,d.nickName userNickName,ifnull(e.realName,f.realName) userAuthName,b.name applyAdmName,ura.remittanceAccount
FROM signet_apply_record t
LEFT JOIN user d ON d.id = t.userId
LEFT JOIN user_remittance_account ura ON ura.userId = t.userId
<if test="currAdmId4Auth != null">
LEFT JOIN sys_admin_user_customer h ON h.admId = #{currAdmId4Auth} AND h.userId = t.userId
LEFT JOIN (SELECT wt.pid,count(*) cc FROM workflow_task_examine_details wte LEFT JOIN workflow_task wt ON wt.id = wte.taskId WHERE wt.taskType = 23 AND wte.admId = #{currAdmId4Auth} GROUP BY wt.pid) w ON w.pid = t.id
</if>
<where>
<include refid="findVerifySignetWhere"></include>
</where>
order by t.id desc
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<sql id="findVerifySignetWhere">
<if test="taskTypeList != null and taskTypeList.size() > 0">
and t.taskType IN
<foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
#{at}
</foreach>
</if>
<if test="signetType != null">
and t.type = #{signetType}
</if>
<if test="status != null">
and t.status = #{status}
</if>
<if test="currAdmId4Auth != null ">
<![CDATA[ and (h.admId = #{currAdmId4Auth} OR w.cc > 0) ]]>
</if>
</sql>
5.3、自带去除第一个and
<select id="findUserAppsSpreadEffectList" resultType="com.duodian.admore.entity.db.apps.AppsSpreadEffectReport">
SELECT t.*,b.trackName,b.smallIcon FROM apps_spread_effect_report t
LEFT JOIN apps_user_app a ON a.trackId = t.trackId
LEFT JOIN apps_app b ON b.trackId = t.trackId
<where>
<if test="userId != null">
a.userId = #{userId}
</if>
<if test="startDate != null ">
<![CDATA[ and t.spreadDate >= #{startDate} ]]>
</if>
<if test="endDate != null ">
<![CDATA[ and t.spreadDate <= #{endDate} ]]>
</if>
</where>
order by a.id desc
</select>
<!--自带取去除第一个and-->
SELECT * FROM OD_LINK_TYPE
<where>
LINK_URI != BASE_TYPE_URI
<if test="linkUri != null">
AND LINK_URI LIKE '%${linkUri}%'
</if>
<if test="linkLabel != null">
AND LINK_LABEL LIKE '%${linkLabel}%'
</if>
<if test="baseTypeUri != null">
AND BASE_TYPE_URI=#{baseTypeUri}
</if>
</where>
6、、foreach list结合作为参数在mapper中的查询（taskTypeList 为List）
<if test="taskTypeList != null and taskTypeList.size() > 0"> and t.taskType IN
<foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
#{at}
</foreach>
</if>
<sql id="findVerifySignetWhere">
<if test="startDate != null">
<![CDATA[ and t.cdate >= #{startDate} ]]>
</if>
<if test="endDate != null">
<![CDATA[ and t.cdate <= #{endDate} ]]>
</if>
<if test="userParam != null and userParam != '' and userParam!= 'undefined'">
AND (t.userId = #{userParam} OR d.nickName LIKE CONCAT('%',#{userParam},'%' ) OR e.realName LIKE
CONCAT('%',#{userParam},'%' ) OR f.realName LIKE CONCAT('%',#{userParam},'%' ) OR g.email LIKE
CONCAT('%',#{userParam},'%' ))
</if>
<if test="taskType != null">
and t.taskType = #{taskType}
</if>
<if test="taskTypeList != null and taskTypeList.size() > 0"> and t.taskType IN
<foreach collection="taskTypeList" index="index" item="at" open="(" separator="," close=")">
#{at}
</foreach>
</if>
<if test="signetType != null">
and t.type = #{signetType}
</if>
<if test="status != null">
and t.status = #{status}
</if>
<if test="currAdmId4Auth != null ">
<![CDATA[ and (h.admId = #{currAdmId4Auth} OR w.cc > 0) ]]>
</if>
</sql>
7、choose when
<if test="flag != null">
<choose>
<when test="flag == 1">
and t.status = 0
</when>
<when test="flag == 2">
and t.status = 1
</when>
<when test="flag == 3">
and t.expressStatus = 1
</when>
<when test="flag == 4">
and t.status = -2
</when>
</choose>
</if>
8、ifnull （如果不是空返回第一个，否则返回第二个）
ifnull(b.realName,c.realName) authName,
<select id="findRedStartSpread" parameterType="com.duodian.admore.dao.db.redstart.query.RedStartSpreadQuery" resultType="com.duodian.admore.dao.db.redstart.bean.RedStartHistoryBean">
SELECT
k.trackId,
e.smallIcon,
e.formattedPrice,
e.price,
e.fileSizeBytes,
e.trackName,
f.name admName,
a.nickName userName,
ifnull(b.realName,c.realName) authName,
DATE_FORMAT(k.spreadDateStart, '%Y-%m-%d') AS ymd,
k.userId
FROM
redstart_spread k
9。制作参数为map值进行传入（opt项目SkinsController）
1、controller接收参数
@RequestMapping("data")
@ResponseBody
public ResponseBean data(String name,
Integer type,
Integer status,@RequestParam(value = "page",defaultValue = "0") Integer page){
int pageSize = 15;
Pageable pageable = new PageRequest(page,pageSize);
Page<SkinAppInfoData> dataPage = skinsService.findSkinAppInfoList(pageable,"name",name,"type",type,"status",status);
return ResponseBean.buildSuccess(dataPage);
}
2、service 制作map参数（下面的功能是模糊查询?分页）
1、service接口
public Page<SkinAppInfoData> findSkinAppInfoList(Pageable pageable, Object... param) throws AppException;
2、service开始实现 （pageable 主要是利用里面的参数制作limit参数的）
@Override
public Page<SkinAppInfoData> findSkinAppInfoList(Pageable pageable, Object... param) throws AppException {
Map data = MyBatisHelper.mergeParameterMap(pageable,param);
if(data.get("startDate") != null){
Date startDate = (Date) data.get("startDate");
data.put("startDate", com.duodian.admore.core.helper.DateHelper.getDateFirstTime(startDate));
}
if(data.get("endDate") != null){
Date endDate = (Date) data.get("endDate");
data.put("endDate", com.duodian.admore.core.helper.DateHelper.getDateLastTime(endDate));
}
List<SkinAppInfoData> dataList = skinsMapper.findSkinList(data);
for(SkinAppInfoData skinAppInfoData :dataList){
List<ChannelJson> channelJsonList = new ArrayList<>();
if(skinAppInfoData.getChannelJson()!=null&&!"".equals(skinAppInfoData.getChannelJson())) {
JSONArray jsonArray = JSONArray.fromObject(skinAppInfoData.getChannelJson());
for (int i = 0; i < jsonArray.size(); i++) {
ObjectMapper objectMapper = new ObjectMapper();
try {
ChannelJson channelJson = objectMapper.readValue(jsonArray.get(i).toString(), ChannelJson.class);
channelJsonList.add(channelJson);
} catch (IOException e) {
e.printStackTrace();
}
}
}
skinAppInfoData.setChannelJsonList(channelJsonList);
}
Long count = skinsMapper.countSkinList(data);
return new PageImpl<SkinAppInfoData>(dataList,pageable,count);
}
3、MyBatisHelper工具栏
public class MyBatisHelper {
public static final String PARAM_OFFSET = "offset";
public static final String PARAM_LIMIT = "limit";
public MyBatisHelper() {
}
public static Map<String, Object> mergeParameterMap(Object... parameter) {
if (parameter.length % 2 != 0) {
throw new IllegalArgumentException("parameter须为key-value对应参数");
} else {
Map<String, Object> map = new HashMap();
for(int i = 0; i < parameter.length; i += 2) {
map.put(parameter[i].toString(), parameter[i + 1]);
}
return map;
}
}
public static Map<String, Object> mergeParameterMap(Pageable pageable, Object... parameter) {
if (parameter.length % 2 != 0) {
throw new IllegalArgumentException("parameter须为key-value对应参数");
} else {
Map<String, Object> map = new HashMap();
map.put("offset", pageable.getOffset());
map.put("limit", pageable.getPageSize());
for(int i = 0; i < parameter.length; i += 2) {
map.put(parameter[i].toString(), parameter[i + 1]);
}
return map;
}
}
}
4、mapper.java （下面这两个其实就是专门用来做制作page分页的）
public interface SkinsMapper {
public List<SkinAppInfoData> findSkinList(Map param);
public Long countSkinList(Map param);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.duodian.admore.dao.db.skins.SkinsMapper">
<select id="findSkinList" resultType="com.duodian.admore.data.skins.SkinAppInfoData">
SELECT
A1.`appid`,
A1.`appSecret`,
A1.`icon`,
A1.`makerMemo`,
A1.`haveBackstage`,
A1.`channelJson`,
A1.`filePath`
FROM `skin_app_info_check` a1
where A1.status not in (9)
<if test="name != null and name != ''">
AND (A1.trackId = #{name}
OR A1.name LIKE CONCAT('%',#{name},'%' )
OR A1.appid LIKE CONCAT('%',#{name},'%' )
OR A1.appSecret LIKE CONCAT('%',#{name},'%' )
)
</if>
<if test="type != null and type != '' ">
and A1.type = #{type}
</if>
<if test="status != null and status != '' ">
and A1.status = #{status}
</if>
order by A1.cdate desc
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<select id="countSkinList" resultType="java.lang.Long">
SELECT
count(*)
FROM `skin_app_info_check` A1
where A1.status != 9
<if test="name != null and name != ''">
AND (A1.trackId = #{trackId} OR A1.name LIKE CONCAT('%',#{name},'%' ))
</if>
<if test="type != null">
and A1.type = #{type}
</if>
<if test="status != null">
and A1.status = #{status}
</if>
</select>
</mapper>
5、有分组的
<select id="findCouponTaoKeDataByParam" resultType="com.duodian.youhui.entity.db.coupon.CouponTaoKeData">
SELECT c.itemTitle,COUNT(c.itemId),sum(c.estimateAmount),c.adzoneName,c.adzonePid FROM coupon_taoke_data c WHERE 1=1
<where>
<include refid="findCouponTaoKeDataByParamSQL"></include>
</where>
GROUP by c.itemId
<if test="offset != null and limit != null">
limit #{offset}, #{limit}
</if>
</select>
<select id="countFindCouponTaoKeDataByParam">
select count(*) from (
SELECT c.itemTitle FROM coupon_taoke_data c WHERE 1=1
<where>
<include refid="findCouponTaoKeDataByParamSQL"></include>
</where>
GROUP by c.itemId
) as a
</select>
<sql id="findCouponTaoKeDataByParamSQL">
<if test="startDate != null ">
<![CDATA[ and c.createTime >= #{endDate} ]]>
</if>
<if test="endDate != null ">
<![CDATA[ and c.createTime <= #{endDate} ]]>
</if>
<if test="adzonePid != null and adzonePid != ''">
<![CDATA[ and c.adzonePid <= #{adzonePid} ]]>
</if>
</sql>
10、模糊查询，对象作为参数传入，类似于9中的map
1、query对象 (查询参数)
public class SysUserQuery implements Serializable {
private static final long serialVersionUID = -4564423981924197001L;
private Long id;
private Integer offset;
private Integer limit;
private Date startDate;
private Date endDate ;
private String userid;
private String userParam;
private Integer status;
}
2、controller层
@RequestMapping("data")
@ResponseBody
public ResponseBean data(@RequestParam(defaultValue = "0")Integer page, @RequestParam(defaultValue = "15")Integer pageSize, SysUserQuery query){
try {
Pageable pageable = new PageRequest(page,pageSize);
return ResponseBean.buildSuccess(sysDingUserService.getDingUserData(pageable,query));
} catch (AppException e) {
return ResponseBean.buildFailure(e.getMessage());
} catch (Exception e) {
logger.error(e.getMessage(),e);
return ResponseBean.buildFailure(ErrorCodeEnum.系统错误);
}
}
3、service层，将pageable分页对象放入
@Override
public Page<SysDingUser> getDingUserData(Pageable pageable, SysUserQuery query) {
query.setOffset(pageable.getOffset());
query.setLimit(pageable.getPageSize());
List<SysDingUser> list = sysMapper.findSysDingUserList(query);
Long count = sysMapper.countSysDingUser(query);
return new PageImpl<>(list, pageable, count);
}
}
4、mybatis查询语句
<select id="findSysDingUserList" resultType="com.duodian.admore.entity.db.admin.SysDingUser">
SELECT t.*, a.admId,b.name admName FROM sys_ding_user t
LEFT JOIN sys_admin_user_ding a ON a.userid = t.userid
LEFT JOIN sys_admin_user b ON b.id = a.admId
<where>
<if test="userParam != null and userParam != ''">
and (t.userid = #{userParam} OR t.name LIKE CONCAT('%',#{userParam},'%' ) OR t.email LIKE CONCAT('%',#{userParam},'%' )
OR t.orgEmail LIKE CONCAT('%',#{userParam},'%' ) OR t.mobile LIKE CONCAT('%',#{userParam},'%' ) OR t.position LIKE CONCAT('%',#{userParam},'%' ))
</if>
</where>
ORDER BY t.id ASC
limit #{offset}, #{limit}
</select>
11、sql取出制作DTO对象
1、对于数据库字段匹配的，可以直接选择
2、对于不匹配的使用 as 转化
<select id="findRedStartSpread" parameterType="com.duodian.admore.dao.db.redstart.query.RedStartSpreadQuery" resultType="com.duodian.admore.dao.db.redstart.bean.RedStartHistoryBean">
SELECT
k.trackId,
e.smallIcon,
e.formattedPrice,
e.price,
e.fileSizeBytes,
e.trackName,
f.name admName,
a.nickName userName,
DATE_FORMAT(k.spreadDateStart, '%Y-%m-%d') AS ymd,
k.userId
FROM
redstart_spread k
11.1、RedStartHistoryBean
public class RedStartHistoryBean implements Serializable {
private Long userId;
private String ymd;
private String trackId;
private String smallIcon;
private String bundleId;
private String formattedPrice;
private BigDecimal price;
private BigInteger fileSizeBytes;
private String trackName;
private String fileSizeDesc; //文件大小描述
private String authName;
private String admName;
private String userName;
11.2、RedStartSpreadQuery
public class RedStartSpreadQuery implements Serializable {
private Long userId;
private String trackId;
private String trackName;
private Date startDate;
private Date endDate;
private String name;
private Integer status;
private Integer offset;
private Integer limit;
private Date point;
private String userParam;
private String appParam;
12、只有一个参数传入的时候，不使用注解@Param,不能直接写参数名字了 而是使用下面的_parameter
1、mapepr.java
List<CustomerChance> getCustomerList(Long adminId);
2、mapper.xml
<select id="getCustomerList" resultType="com.duodian.admore.entity.db.customer.CustomerChance">
SELECT *
FROM `crm_customer_chance` c
where c.isVisible = 1
<if test="_parameter != null">
and c.adminId = #{_parameter}
</if>
</select>
13、加入原生符号
t.cdate >= #{startDate}
<![CDATA[]]>和转义字符被<![CDATA[]]>这个标记所包含的内容将表示为纯文本，比如<![CDATA[<]]>表示文本内容“<”。
此标记用于xml文档中，我们先来看看使用转义符的情况。我们知道，在xml中，”<”、”>”、”&”等字符是不能直接存入的，否则xml语法检查时会报错，如果想在xml中使用这些符号，必须将其转义为实体，如”<”、”>”、”&”，这样才能保存进xml文档。
13.1、举例说明
<sql id="pageSuffix">
) a where rownum <![CDATA[<=]]> #{end,jdbcType=INTEGER} ) b where b.rn <![CDATA[>]]> #{start,jdbcType=INTEGER}
</sql>
14、一个条件匹配多个字段
<if test="userParam != null and userParam != ''">
AND (t.userId = #{userParam}
OR a.nickName LIKE CONCAT('%',#{userParam},'%' )
OR b.realName LIKE CONCAT('%',#{userParam},'%' )
OR c.realName LIKE CONCAT('%',#{userParam},'%' )
OR t.customerId LIKE CONCAT('%',#{userParam},'%' )
OR t.customerName LIKE CONCAT('%',#{userParam},'%'))
</if>
15、count详解
1、这样输出结果只有一行，因为count（*）本来代表的就是一个数字，本身就是一行
SELECT count(*) as "count",idfa from apps_click_record a; # WHERE a.keywordId = '169995' GROUP by idfa ORDER BY count(*) DESC ;
2、使用group分组 （下面二者是一样的） 其实这里就表示分组之后每组的个数
SELECT count(*) as "count",idfa from apps_click_record a WHERE a.keywordId = '169995' GROUP by idfa ORDER BY count(*) DESC ;
SELECT count(idfa) as "count",idfa from apps_click_record a WHERE a.keywordId = '169995' GROUP by idfa ORDER BY count(*) DESC ;
3、count（*） 和 * 的查询 是错误的
下面是错误的
SELECT count(*) as "count",* from apps_click_record a;
4、查找数group by分组后的个数，使用嵌套
select count(*) from
(
select sum(b.id) from B b group by b.type
) m
18、delete删除表中数据
1,delete from user as u where u.userid=6; 错误
2,delete from user u where u.userid=6; 错误
3,delete from user where userid=6; 正确
4,delete u.* from user u where u.userid=6; 正确
5,delete u from user u where u.userid=6; 正确
19、请求分页参数
19.1、congtroller
@GetMapping("getOriginData")
public Wrapper<?> getOriginData(String phone, Integer bankId, PageQuery pageQuery){
19.2、DOTO
@Setter
@ApiModel("分页对象")
@Accessors(chain = true)
public class PageQuery {
@ApiModelProperty(value = "开始页数，从1开始",example = "1", required = true,dataType = "java.lang.Integer")
private Integer pageNum = 1;
@ApiModelProperty(value = "每页数量",example = "20", required = true,dataType = "java.lang.Integer")
private Integer pageSize = 20;
public Integer getPageSize() {
return pageSize == null ? 20 : pageSize;
}
public Integer getPageNum() {
return pageNum == null ? 1 : pageNum;
}
}
20、timestamp 多个日期，如果可能为空，则建议使用datetime
重点：2、@Temporal(TemporalType.DATE)插入数据库中的日期会自动变成 00.00.00，应该使用TIMESTAMP，sql中是datetime
普通字段不要设置为timestamp，timestamp列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。如果我们在save实体的时候，没有给相关timestamp设置值，那么他就会自动由mysql将当前时间设置进去， cdate和udate，都是在java基础上控制的
正确数据
createTime datetime default null ,
clickTime datetime default null,
@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(value = "创建时间")
private Date createTime;
@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(value = "点击时间")
private Date clickTime;
下面这个不可能为空，所以也是正确的
cdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
udate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
@Temporal(TemporalType.TIMESTAMP)
@Column(columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP",insertable = true,updatable = false)
@ApiModelProperty(hidden = true)
private Date cdate;
@UpdateTimestamp
@Temporal(TemporalType.TIMESTAMP)
@ApiModelProperty(hidden = true)
private Date udate;
21、多条件排序
举例：订单降序 1，订单升序 2 ，成交额降序 3，成交额升序 4，
<select id="findCouponTaoKeDataByParam" resultType="com.duodian.youhui.data.coupon.CouponTaoKeItemGoodSummaryData">
SELECT c.itemTitle,COUNT(c.itemId) as orderSize,sum(c.estimateAmount) AS sumEstimateAmount ,c.adzoneName,c.adzonePid,c.createTime,c.itemId FROM coupon_taoke_data c
<where>
c.dataType = 1 and c.status = 1
<include refid="findCouponTaoKeDataByParamSQL"></include>
</where>
GROUP by c.itemId,c.adzonePid
<if test="order != null">
<choose>
<when test="order == 1">
order by orderSize DESC
</when>
<when test="order == 2">
order by orderSize asc
</when>
<when test="order == 3">
order by sumEstimateAmount DESC
</when>
<when test="order == 4">
order by sumEstimateAmount asc
</when>
</choose>
</if>
<if test="offset != null and limit != ''">
limit #{offset}, #{limit}
</if>
</select>
2、给排序添加非空条件
使用order byorderid desc实现降序时
orderid 为null数据的会排在数据的最后面；
但是，order by orderid升序时
orderid 为null的数据则会排在最前面
如果想要将orderid为null的数据排在最后，就需要加上is null。
select * from b_programme u order by u.orderid is null, u.orderid
23、参数传入为0，判断null的时候
23.1、第一种解决方法，不建议
id传值为0时(前提是id对应的类型为long 或者 Integer，String型无此问题)，发现并没有执行if里的sql，因为在mybatis中会自动把0当成‘’空字符串，所以建议以后传入这种类型的数据，最好还是不要传入0，可以将0改变为其他的数字，比如5等
<if test="status == 5">
and c.connectStatus = 0 and c.createAdminId is not NULL
</if>
List<CouponItemGood> dataByParam(@Param("status") Integer status,
<if test="status !=null and status !=''">
<if test="status == 5">
and c.connectStatus = 0 and c.createAdminId is not NULL
</if>
<if test="status == 1">
and c.connectStatus = 1 and c.status = 1 and c.createAdminId is not NULL
</if>
<if test="status == 2">
and c.connectStatus = 1 and c.status = 0 and c.createAdminId is not NULL
</if>
<if test="status == 3">
and c.status = 2 and c.createAdminId is not NULL
</if>
<if test="status == 4">
and c.createAdminId is NULL
</if>
<if test="status == 6 ">
and c.status not in (0) and c.zhiboStatus=1
</if>
<if test="status == 7 ">
and c.scheduleDealStatus = 0 and c.createAdminId is not NULL
</if>
</if>
23.2、第二种解决方法
使用时增加多一个or status == 0判断
<if test="status != null and status != '' or status == 0">
24、查询随机数优化
24.1 、性能比较差的一个
RAND() 函数返回的是一个小于1的随机数
BY RAND() LIMIT 1
<!--失效之后，从选品库中随便找一个 -->
<select id="wechatSuiJiItemGoodImageUrl" resultType="java.lang.String">
<![CDATA[
SELECT c.erWeiMaInfoUrl FROM coupon_item_good c WHERE
TIMESTAMPDIFF(DAY ,cdate,now()) < 5 ORDER BY RAND() LIMIT 1
]]>
</select>
24.2、优化
SQL ROUND() 语法
SELECT ROUND(column_name,decimals) FROM table_name
| 参数 | 描述 |
|---|---|
| column_name | 必需。要舍入的字段 |
| decimals | 非必需，规定返回的小数位数，如果不给值，则自动四舍五入取整取整，select round(100.9) ; 101 |
随机选择一个id，然后选择一个大于他的数据，limit控制为1
随机选择一个推广位，具体条件就是下面and中连接的and t1.status 开始
> <select id="findUserCouponAdzone" resultType="com.duodian.youhui.entity.db.coupon.CouponAdzone">
<![CDATA[
SELECT *
FROM `coupon_adzone` AS t1
JOIN (SELECT ROUND(RAND() * (SELECT MAX(id)
FROM `coupon_adzone`)) AS id) AS t2
WHERE t1.id >= t2.id
and t1.status = 1 AND t1.adzoneType = 3 and (TIMESTAMPDIFF(HOUR, t1.userUseTime, now()) > #{timeDiff} OR t1.userInfoId IS NULL )
ORDER BY t1.id ASC
LIMIT 1;
]]>
</select>
25、 GROUP_CONCAT -sql语句将某一列的值查询成，逗号分隔的字符串
select GROUP_CONCAT(c.id) from coupon_item_good;
返回结果
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22,23,24,25,26,27,28,29,30,31,32,33
26、find_in_set 查询字段为逗号隔开的字段属性
字段 pnum为逗号隔开的字符串
1,2,3,4,21,9
select * from test t where find_in_set(2,t.pnum) ;
27、when，case、进行条件判断
**
case cp.ssid when 'aa' then '0' else'1' end as flag, "+
" case cp.ssid when 'aa' then '3001' else '0000' end as retCode,"+
"SELECT cp.ssid AS ssid," +
" cp.accname AS accname," +
" cp.PAYSUMUNTAX AS PAYSUMUNTAX," +
" cp.PAYTAX AS PAYTAX," +
" cp.payaftersum AS payAmt," +
" cp.benetype AS benetype," +
" cp.memo AS memo," +
" case cp.ssid when 'aa' then '0' else'1' end as flag, "+
" case cp.ssid when 'aa' then '3001' else '0000' end as retCode,"+
" to_char(sysdate,'yyyyMMdd') as drawDate,"+
" to_char(sysdate,'HH24mmss') as drawTime"+
" FROM CSIP_OA_PAYLISTINFO CPI" +
" JOIN CSIP_OA_TASKFILE CT" +
" ON CPI.PK_PAYLISTINFO = CT.PK_PAYLISTINFO" +
" JOIN CSIP_OA_PAYLIST CP" +
" ON CP.PK_TASKFILE = CT.PK_TASKFILE" +
" WHERE CPI.INNER_LISTNO = '"+inworkflowno+"'" ;
28、#和$项目中使用的区别
28.1、#{变量名}可以进行预编译、类型匹配等操作，#{变量名}会转化为jdbc的类型,${变量名}不进行数据类型匹配，直接替换。
select * from tablename where id = #{id}
假设id的值为12，其中如果数据库字段id为字符型，那么#{id}表示的就是'12'，如果id为整型，那么id就是12，并且MyBatis会将上面SQL语句转化为jdbc的select * from tablename where id=?，把?参数设置为id的值。
select * from tablename where id = ${id}
如果字段id为整型，sql语句就不会出错，但是如果字段id为字符型， 那么sql语句应该写成select * from table where id = '${id}'。
28.2、方式能够很大程度防止sql注入。因为#会自动转换，而&为直接替换,所以$方式无法防止sql注入。
28.3、项目中的使用，尽量使用# ，少用& 臭小子，明白了吧
#适用于普通的参数传入
$方式一般用于传入数据库对象，例如传入表名。
order为 A ASC, A DESC ,B DESC ，B asc数据，这里直接使用#是错误的
<when test="order != null">
order by ${order}
</when>
29、mysql除法
余数可以为0，得到的结果为NUll
SELECT 1/0 from dual ;
30、mysql 取小数 convert ROUND cast
1、convert（四舍五入）
select convert(10000,decimal(10,2));
# 四舍五入，decimal(10,2)后面的代表最大长度10以及保留的小数位数2
select convert(10569.3645,decimal(10,2)); #10569.36
select convert(10569.3665555,decimal(10,2)); #10569.37
2、round（自动四舍五入）
# round 第二个表示保留几位，如果之前有小数，那么不足的补上0。肯定是够的。
# 第二个如果为负数 -1 代表个位数为0 ROUND(114.6,-1) 结果 110，
# -2 代表个位数和十分位 为0 例如 ROUND(114.6,-2) 结果 100
# 100.35 100 0.60 110
SELECT ROUND(100.3465,2),ROUND(100,2),ROUND(0.6,2),ROUND(114.6,-1) ;
3、cast，强制转换
select cast(10*1/4 as decimal(18,2)) from dual
31、子查询
31.1、子查询中的变量是不可以是哦用外面的变量的
比如下面的 #{userInfoId} 是不可以使用u的。只能是使用已知的参数
select
o.payAmount,
o.estimateAmount,
(o3.notValidOrderSize +o.orderSize) as orderSize
from user_info u
left join (select IFNULL(count(o1.orderNo),0 ) as orderSize ,
IFNULL(sum(o1.estimateAmount) ,0) as estimateAmount ,
IFNULL(sum(o1.payAmount) ,0) as payAmount,
o1.userInfoId as userInfoId
from user_order o1
where o1.userInfoId = #{userInfoId} and
o1.orderStatus !='订单失效'
)
o on o.userInfoId = u.id
left join (select IFNULL(count(o2.orderNo),0 ) as notValidOrderSize,
o2.userInfoId as userInfoId
from user_order o2
where o2.orderStatus ='订单失效' and o2.userInfoId = #{userInfoId}
)
o3 on o3.userInfoId = u.id
where u.id = #{userInfoId};
u.id =
32、sql查询结果加法
select
o.payAmount,
o.estimateAmount,
(o3.notValidOrderSize +o.orderSize) as orderSize
from user_info u
33、某一个或者多个字段查找重复数据的sql语句
33.1、表中有id和name 两个字段，查询出name重复的所有数据
group by username having count(*) > 1
select *
from healerjean a
where (a.username) in (
select username
from healerjean
group by username
having count(*) > 1
)
33.2、删除分组中重读的数据，只保留id最小的记录
delete from healerjean
where username in (
select username
from healerjean
group by username
having count(username) > 1
)
and id not in (
select min(id) #每组中最小的数据
from healerjean
group by username
having count(username)>1)
33.3、查找表中多余的重复记录（多个字段）
下面这个就保证了，肯定是二者同时存在才会出现count(*)>1
如果没有having则，会出现先根据peopleId分组，然后组内，再根据seq 分组。
group by peopleId,seq having count(*) > 1
select *
from vitae a
where (a.peopleId,a.seq) in (
select peopleId,
seq
from vitae
group by peopleId,seq
having count(*) > 1)
33.4、选择表中多余的重复记录（多个字段），只留有id最小的记录
select *
from vitae a
where (a.peopleId,a.seq) in (
select peopleId,
seq
from vitae
group by peopleId,seq
having count(*) > 1)
and id not in ( select min(id)
from vitae
group by peopleId,seq
having count(*) > 1)
感兴趣的，欢迎添加博主微信，
哈，博主很乐意和各路好友交流，如果满意，请打赏博主任意金额，感兴趣的在微信转账的时候，备注您的微信或者其他联系方式。添加博主微信哦。
请下方留言吧。可与博主自由讨论哦
| 支付包 | 微信 | 微信公众号 |
|---|---|---|
