文章目录
MySQL中时间的使用
mysql 时间查询
<resultMap id="SectorStatisticMap2" type="java.util.HashMap">
<result column="s_create_statistic_date" jdbcType="DATE"/>
<result column="use_man_time" jdbcType="INTEGER"/>
</resultMap>
<select id="selectSector" resultType="java.util.List" resultMap="SectorStatisticMap2">
SELECT s_create_statistic_date name,sum(use_man_time) value FROM `sector_statistic` WHERE
s_mechanism_id = #{mechanismId}
<if test="startDate != null and startDate != '' ">
and DATE_FORMAT(s_create_statistic_date, '%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')
</if>
<if test="endDate != null and endDate != '' ">
and DATE_FORMAT(s_create_statistic_date, '%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d')
</if>
GROUP BY s_create_statistic_date
</select>
mysql时间计算时间间隔
语法
DATEDIFF(datepart,startdate,enddate)
startdate 和 enddate 参数是合法的日期表达式。datepart 参数可以是下列的值:年月日时分秒等
https://www.runoob.com/sql/func-datediff.html
设置更新时间和创建时间的默认值
update
Excel数据导入时间处理
以接下来要用到的就是HSSFDateUtil这个工具类来处理时间,
Date setupTime = HSSFDateUtil.getJavaDate(Double.valueOf(sto.getSetupTime()));
sto.getSetupTime():从Excel中获取到的日期(便是上文中的“43780”这串数字)
group by 的使用
group by 只显示一条
怎么显示多条?
在来一个group by
GROUP BY device_id, id
group by 时取每组前10条
https://blog.csdn.net/junzi528/article/details/84404412
1、直接查询出来就去重了,每组只有一个最高分
select
s2.stuid,s2.subid,s2.grade
from
(
select
if( s1.subid = @subid, @rank := @rank + 1, @rank := 1 ) as rank,
@subid := subid as tmp_subid,
s1.stuid,s1.subid,s1.grade
from
( select stuid,subid,grade from student_grade order by subid, grade desc ) s1,
( select @subid := null, @rank := 1 ) tmp
) s2
where
s2.rank <= 2
2、直接查询出来,然后利用TreeSet进行去重,利用list进行
group by 和 order by 一起使用问题处理??
根据公司id 分组,但是要根据时间排序,每个分组中取最新的三百条数据。有大佬知道这个sql 怎么写嘛
group by 时分页
另一种实现方法:
SELECT SQL_CALC_FOUND_ROWS uid from article where 1 = 1 group by uid limit 0,20;
执行此句获得相应页的数据之后,紧接着进行一次查询
SELECT FOUND_ROWS() as total 即可获得满足条件的总记录数,使用起来非常方便。
mysql分组查询group by时分页的实现。
https://blog.csdn.net/zhang197093/article/details/49425261
一对多关联查询分页
MybatisPlus增删改查
1、实体进行CRUD
1.实体继承Model类
2.mapper继承BaseMapper
3.controller 实体类参数直接调用方法
2、controller类直接写crud
service继承IService,实现类继承ServiceImpl
3、updateById
mybatis-plus 的默认配置 在调用 baseMapper.updateById 时 ,如果实体类中的字段属性为null,那么不将该属性更新到数据库
1)修改mybatis-plus配置
mybatis-plus:
global-config:
db-config:
update-strategy: NOT_NULL
# 字段策略
# IGNORED:"忽略判断,会更新NULL和空串",
# NOT_NULL:"非 NULL判断,会更新空串",
# NOT_EMPTY:"非空判断,不会更新NULL和空串",
# 默认是NOT_NULL
2)自己写sql调用就好了。
MybatisPlus分页
1、分页配置类
@Configuration
@MapperScan("com.watson.traffic.blog.trafficblog.mapper")
public class MybatisPlusConfig {
/**
* 分页配置
*
* @return 分页
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
*
*上面过期的时候的替代
*/
// @Bean
// public MybatisPlusInterceptor mybatisPlusInterceptor() {
// MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
// return interceptor;
// }
//
// @Bean
// public ConfigurationCustomizer configurationCustomizer() {
// return configuration -> configuration.setUseDeprecatedExecutor(false);
// }
}
//https://blog.csdn.net/qq_41359651/article/details/112260207
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
2、QueryWrapper分页查询
此方法是使用PaginationInterceptor 作为分页插件.
@Test
void contextLoads() {
Page<User> page = new Page<>(1, 2);
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "wdh");
Page<User> page1 = userMapper.selectPage(page, queryWrapper);
page.setRecords(page1.getRecords()).getRecords().forEach(System.out::println);
}
3、pageHelp的使用
依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
代码
PageHelper.startPage(page,pageSize);
List<Company> List=companyDao.selectAll();
PageInfo pageInfo = new PageInfo(list);
return pageInfo;
4、Mybatis逻辑分页原理解析RowBounds
1
MybatisPlus查询与忽略指定字段
1、忽略字段
@TableField(exist = false)
2、查询指定字段
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name", "age");
//只需要查询出name和age两个字段的数据
要查询出除manager_id和create_time外其它所有字段的数据
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(User.class, info -> !info.getColumn().equals("manager_id")
&& !info.getColumn().equals("create_time"));
3、实体类字段与数据库字段不一致的问题
a、自己写sql 取别名
b、result标签指定
<resultMap id="BaseResultMap" type="com.cpiinfo.sysmgt.entity.Organization">
<result column="id" property="id" jdbcType="VARCHAR" />
c、字段映射
@TableField("l_level")
private String level; //级别
d、表映射
@TableName("sys_user")
public class User {
4、数据库存储字段类型为JSON
实体类怎么操作?
字段:@TableField(typeHandler = FastjsonTypeHandler.class)
类:@TableName(value = "t_store_expenditure_total",autoResultMap = true)
MybatisPlus使用聚合函数
1、求和
QueryWrapper<WoAssignsTask> queryWrapper1 = creatQueryWrapper(dto);
queryWrapper1.select("IFNULL(sum(operation_progress),0) as exceptionTotal");
Map<String, Object> map = getMap(queryWrapper1);
mybatis 传参
1、mapper参数为map,map中包含list
a、mapper层,
此时不用@param注解,直接取map的key,参数是对象的时候同理
List<WoAssignsExceptionAssignRepairDTO> listWoAssignsExceptionAssignRepairDTO(Map<String, Object> map);
b、xml层
select s.id ,s.device_id ,s.exception_code ,s.diagnosis_type_name ,MAX(grade) grade,
s.event_desc , s.fault_details_id, w.id as woAssignsExceptionAssignId
from security_base_exception s join wo_assigns_exception_assign w on s.id = w.security_base_exception_id
and w.repair_status ='unrepair'
and w.handle_opinions = 'dispatch'
<if test="diagnosisTypeName != null and diagnosisTypeName.trim() != ''">
AND s.diagnosis_type_name = #{diagnosisTypeName}
</if>
<if test="grade != null and grade.trim() != '' ">
AND s.grade = #{grade}
</if>
<if test="pvNode != null ">
<!-- AND s.device_id in (#{map.pvNode})-->
AND s.device_id in
<foreach collection="pvNode" item="oneItem" separator=","
open="(" close=")">
#{oneItem}
</foreach>
</if>
group by device_id,diagnosis_type_name
</select>
2、mybatis 执行传入的任意sql语句
a、mapper层
/**
* 自定义sql查询
* @param sqlContent
* @return
*/
public List<LinkedHashMap<String, Object>> customQueryItem(String sqlContent);
b、mapper.xml
<select id="customQueryItem" parameterType="String" resultType="java.util.LinkedHashMap">
<![CDATA[
SELECT * FROM (${value}) obj
]]>
</select>
3、执行sql脚本文件
import org.apache.ibatis.jdbc.ScriptRunner;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Exec {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url ="jdbc:mysql://localhost:3306/userinfo";
private static final String username = "gfstack";
private static final String password = "gfstack";
private static final File file = new File("C:\\sql\\userinfo.sql");
public static void main(String[] args) throws Exception {
mybatisExec();
}
private static void mybatisExec() throws Exception {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
ScriptRunner runner = new ScriptRunner(conn);
try {
runner.setStopOnError(true);
runner.runScript(new FileReader(file));
} catch (Exception e) {
e.printStackTrace();
}
conn.close();
}
}
分页查询优化
原因:limit语句的查询时间与起始记录的位置成正比,limit语句对记录很多的表并不适合直接使用。
1) 尽量给出查询的大致范围
select * from order where orderId > 1000000 limit 0,10 0.03s
通过索引而不是全表扫描来找到数据,再进行分页
2) 子查询法
select a.* from order a JOIN
(select order_id from order LIMIT 1000000, 10) b on a.order_id=b.order_id 0.5s
内层子查询通过索引扫描先进行分页得到数据,再关联外层查询通过索引查找得到最终数据,因为索引文件比数据文件小,所以内层查询时间耗时相对较小,而且内层查询中只包含索引列(覆盖索引),数据就在查询索引上面,无需进行额外的查找
案例sql
CREATE TABLE `teacher` (
`TID` varchar(10) NOT NULL,
`Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`addTime` datetime DEFAULT NULL,
`status` varchar(100) DEFAULT NULL,
`total` int DEFAULT NULL,
PRIMARY KEY (`TID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
行转列
统计每月数据,未找查到数据
SELECT
SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 11 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '0'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 10 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '1'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 9 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '2'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 8 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '3'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 7 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '4'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 6 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '5'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 5 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '6'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 4 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '7'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 3 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '8'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '9'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '10'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '11'
FROM teacher
函数解释
# //对时间戳格式化成 2018-10
# FROM_UNIXTIME(addTime,'%Y-%m')
# //SQL获取当前时间格式 2019-08 ,根据expr值不同,依次获取前一个月1,前两个月2 ···
# DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH),'%Y-%m')
# // 函数用于以不同的格式显示日期/时间数据。
# DATE_FORMAT(date,format)
# //函数从日期减去指定的时间间隔。
# DATE_SUB(date,INTERVAL expr type)
# //函数返回当前的日期。
# CURDATE()
统计某个值每个月的累计
能统计出来
SELECT
SUM(case when month(addTime)='1' AND `status` = '1' then total else 0 end) as '1月',
SUM(case when month(addTime)='2' AND `status` = '1' then total else 0 end) as '2月',
SUM(case when month(addTime)='3' AND `status` = '1' then total else 0 end) as '3月',
SUM(case when month(addTime)='4' AND `status` = '1' then total else 0 end) as '4月',
SUM(case when month(addTime)='5' AND `status` = '1' then total else 0 end) as '5月',
SUM(case when month(addTime)='6' AND `status` = '1' then total else 0 end) as '6月',
SUM(case when month(addTime)='7' AND `status` = '1' then total else 0 end) as '7月',
SUM(case when month(addTime)='8' AND `status` = '1' then total else 0 end) as '8月',
SUM(case when month(addTime)='9' AND `status` = '1' then total else 0 end) as '9月',
SUM(case when month(addTime)='10' AND `status` = '1' then total else 0 end) as '10月',
SUM(case when month(addTime)='11' AND `status` = '1' then total else 0 end) as '11月',
SUM(case when month(addTime)='12' AND `status` = '1' then total else 0 end) as '12月'
FROM teacher
where date_format(addTime,'%Y')='2021'
统计每月的累计条数
此处不能用count,能统计出来
SELECT
SUM(case when month(addTime)='1' AND `status` = '1' then 1 else 0 end) as '1月',
SUM(case when month(addTime)='2' AND `status` = '1' then 1 else 0 end) as '2月',
SUM(case when month(addTime)='3' AND `status` = '1' then 1 else 0 end) as '3月',
SUM(case when month(addTime)='4' AND `status` = '1' then 1 else 0 end) as '4月',
SUM(case when month(addTime)='5' AND `status` = '1' then 1 else 0 end) as '5月',
SUM(case when month(addTime)='6' AND `status` = '1' then 1 else 0 end) as '6月',
SUM(case when month(addTime)='7' AND `status` = '1' then 1 else 0 end) as '7月',
SUM(case when month(addTime)='8' AND `status` = '1' then 1 else 0 end) as '8月',
SUM(case when month(addTime)='9' AND `status` = '1' then 1 else 0 end) as '9月',
SUM(case when month(addTime)='10' AND `status` = '1' then 1 else 0 end) as '10月',
SUM(case when month(addTime)='11' AND `status` = '1' then 1 else 0 end) as '11月',
SUM(case when month(addTime)='12' AND `status` = '1' then 1 else 0 end) as '12月'
FROM teacher
where date_format(addTime,'%Y')='2021'
统计数据
查询年数据
select year(addTime) as 年,
count(*) as 销售合计
from teacher t
group by year(addTime)
查询年月数据
select year(addTime) as 年,
month(addTime)as 月,
count(*)as 销售合计
from teacher t
group by year(addTime),
month(addTime)
查询年月日数据
select year(addTime)as 年,
month(addTime)as 月,
day(addTime)as 日,
count(*)as 销售合计
from teacher t
group by year(addTime),
month(addTime),
day(addTime)
sql 数据分月统计,表中只有每天的数据
现在要求求一年中每个月的统计数据(一条sql)
SELECT
MONTH ( 那个日期的字段 ),
SUM( 需要统计的字段, 比如销售额什么的 )
FROM
表
WHERE
YEAR ( 那个日期的字段 ) = 2010 -- 这里假设你要查 2010年的每月的统计。
GROUP BY
MONTH ( 那个日期的字段 )