mybatis的sql语句

插入时改变字段长度

    insert into lpggas_moves (
            lid,state,InnerID,TagID,operator, SydwId,
            OriginID,uid,longitude,latitude , CTID ,CTName
        )
        values (
            #{ entity.lid }, #{ entity.state }, #{ entity.innerid }, REPLACE(#{ entity.tagid },CHAR(10),''),
            #{ entity.operator }, #{ entity.sydwid }, #{ entity.originid }, #{ entity.uid },
           #{ entity.longitude }, #{ entity.latitude } , #{entity.ctid} ,#{entity.ctname}
        )

系列文章目录

MybatisPlus 中QueryWrapper 方法介绍
mybatis的大小于转义问题
mybatis中的使用

意义 表示 符号
小于 < <
大于 > >
小于等于 <= <=
大于等于 >= >=
也可以使用标签<![CDATA[ < ]]>
mybatisplus中的使用

意义 方法名 符号
小于 lt <
大于 gt >
小于等于 le <=
大于等于 ge >=
不等于 ne <>

https://blog.csdn.net/yin_na/article/details/114929244

---

前言

提示：这里可以添加本文要记录的大概内容：

---

is not null 速度很快

一、三个表查询，返回结果

1.union 连接，别的表没有的字段用null或0

近7日加气数量 代码如下（示例）：

 List<SevenDayLpgGasNumberVO> sevenDayLpgGasNumber( @Param("queryDTO")LpggasczrecordQueryDTO queryDTO);

<select id="sevenDayLpgGasNumber"   resultType="net.lab1024.smartadmin.module.business.statistcs.domain.vo.SevenDayLpgGasNumberVO" >
        SELECT DATE_FORMAT( CzTime, '%m-%d' )  as dates,s.DistrictId, IFNULL(COUNT(*), 0) AS a  , 0 AS b,0 AS c
        FROM lpggasczrecord lc LEFT JOIN lpggas l ON l.Id = lc.lpggasId LEFT JOIN sydw s
        ON s.ID = l.`SydwId` WHERE l.Jiezhi LIKE '%液化石油气%' AND DATE_SUB( CURDATE(), INTERVAL 6 DAY ) &lt;= DATE(CzTime)
        <include refid="BigQuAnXian"/>  GROUP BY dates

        UNION
        SELECT DATE_FORMAT( CzTime, '%m-%d' )  as dates,s.DistrictId,0 AS a , IFNULL(COUNT(*), 0) AS b , 0 AS c
        FROM lpggasczrecord lc LEFT JOIN lpggas l ON l.Id = lc.lpggasId LEFT JOIN sydw s ON s.ID = l.`SydwId`
        WHERE l.Jiezhi NOT LIKE '%液化石油气%' AND DATE_SUB( CURDATE(), INTERVAL 6 DAY ) &lt;= DATE(CzTime)
        <include refid="BigQuAnXian"/>   GROUP BY dates

        UNION
        SELECT DATE_FORMAT( cz_date, '%m-%d' )  as dates,s.DistrictId ,0 AS a , 0 AS b,IFNULL(COUNT(*), 0)  AS c
        FROM vehiclegasczrecord v LEFT JOIN sydw s ON s.ID = v.`AddByWho` WHERE DATE_SUB( CURDATE(), INTERVAL 6 DAY )
        &lt;= DATE(cz_date)    <include refid="BigQuAnXianVehicle"/>  GROUP BY dates
        ORDER BY dates
    </select>

二、使用步骤

1.引入库

代码如下（示例）：

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import warnings
warnings.filterwarnings('ignore')
import  ssl
ssl._create_default_https_context = ssl._create_unverified_context

2.读入数据

代码如下（示例）：

data = pd.read_csv(
    'https://labfile.oss.aliyuncs.com/courses/1283/adult.data.csv')
print(data.head())

该处使用的url网络请求的数据。

---

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

<sql id="header">
        select
          Id,RegId,OriginId, InnerId,Media,
          CzTime,TagId, PersonId,PersonName,
          GunNo
        from lpggasczrecord
 </sql>
  <select id="queryByPage" resultMap="LpggasczrecordVO">
        <include refid="header"/>
        <where>
            <if test="queryDTO.regid != null and queryDTO.regid != ''">
                AND INSTR(RegId,#{queryDTO.regid})
            </if>
            <if test="queryDTO.originid != null and queryDTO.originid != ''">
                AND INSTR(OriginId,#{queryDTO.originid})
            </if>

            <if test="queryDTO.createTimeBegin != null ">
                AND create_time &gt;= #{queryDTO.createTimeBegin}
            </if>
            <if test="queryDTO.createTimeEnd != null ">
                AND create_time &lt;= #{queryDTO.createTimeEnd}
            </if>
 		</where>
    </select>
    

一对多数量统计

SELECT w.id, NAME,NO, w.Jiezhi,remark, w.SydwId,uid,w.create_time,w.update_time, total_nums,full_nums, empty_nums,
  COUNT(DISTINCT wl.id) AS itemCount,COUNT(DISTINCT t2.id),COUNT(DISTINCT t3.id)
  FROM warehouse w
  LEFT JOIN  warehouse_lpggas wl  ON wl.wid = w.id
  LEFT JOIN (SELECT wl.wid ,wl.id FROM warehouse_lpggas wl  LEFT JOIN lpggas l ON  wl.lid = l.`Id` WHERE l.`GasState` ="满瓶在用") t2
  ON t2.wid = w.id
  LEFT JOIN (SELECT wl.wid,wl.id FROM warehouse_lpggas wl  LEFT JOIN lpggas l ON  wl.lid = l.`Id` WHERE l.`GasState` ="空瓶在用") t3
  ON t3.wid = w.id 	
  WHERE w.`SydwId` = 1 GROUP BY w.`id`

数组修改

 void updateStateByArray(@Param("split")String[] split,@Param("distribution") String distribution);
 <update id="updateStateByArray">
         update lpggas set GasState = #{distribution} where Id in
        <foreach collection="split" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </update>

六日内每日数量

SELECT a.days AS dates,IFNULL(a.count, 0) a,IFNULL(b.count, 0) AS b,IFNULL(c.count, 0) AS c,a.DistrictId  FROM 
 (SELECT DATE_FORMAT( CzTime, '%m-%d' ) days,s.DistrictId,
	COUNT(*) COUNT FROM lpggasczrecord lc LEFT JOIN lpggas l ON l.Id = lc.lpggasId
	LEFT JOIN sydw s ON s.ID = l.`SydwId`
 WHERE   s.DistrictID LIKE '37%' AND   l.Jiezhi  LIKE '%液化石油气%'  AND  DATE_SUB( CURDATE(), INTERVAL 6 DAY ) <= DATE(CzTime)  GROUP BY days
 )  a
LEFT JOIN 
(SELECT DATE_FORMAT( CzTime, '%m-%d' ) days,s.DistrictId,
	COUNT(*) COUNT FROM lpggasczrecord lc LEFT JOIN lpggas l ON l.Id = lc.lpggasId
	LEFT JOIN sydw s ON s.ID = l.`SydwId`
 WHERE  s.DistrictID LIKE '37%' AND  l.Jiezhi NOT LIKE '%液化石油气%' AND  DATE_SUB( CURDATE(), INTERVAL 6 DAY ) <= DATE(CzTime)  GROUP BY days
 ) b ON a.days=b.days 
 LEFT JOIN 
 (SELECT DATE_FORMAT( cz_date, '%m-%d' ) days,s.DistrictId,
	IFNULL(COUNT(*),0) COUNT FROM vehiclegasczrecord  v
	LEFT JOIN sydw s ON s.ID = v.`AddByWho`
 WHERE s.DistrictID LIKE '37%' AND   DATE_SUB( CURDATE(), INTERVAL 6 DAY ) <= DATE(cz_date)  GROUP BY days ) c ON a.days = c.days 
 
 ORDER BY a.days

在mybatis里面注释语句的时候，一定不要用–，用 <!- -需要注释的内容–>。

好文要顶 关注我 收藏该文