MySQL中大于，大于等于，小于，小于等于的转义写法
一、左边就是原来的符号，右边就是在mybatis中代替的符号
二、如何通过mysql的存储过程创建虚拟表（临时表），并插入1000条数据
这些表通常是用来做数据统计时用到的：比如：查询指定时间段内的每天的数据的总和，
指定时间包括：开始时间：2022-1-1、结束时间：2022-1-30
2022-1-1~2022-1-30,这个时间段内，要求获取每天的数据，但是数据库可能某天是没有数据的，这时，就需要一张有连续id/其他的字段的一张临时表
创建表很简单，插入1000条数据（id连续）具体方式如下（使用存储过程插入数据）：
1.创建存储过程p01
点击查看代码
CREATE PROCEDURE p01 ()
BEGIN
declare i int;
set i=1;
while i<=1000 do
INSERT INTO help(id) VALUES (i);
set i=i+1;
end WHILE;
END;
2.呼叫存储过程p01
CALL p01;
3.移除存储过程p01
DROP PROCEDURE p01;
以上三步就可以把1000条数据（id连续）插入数据库了
三、mysql查询指定时间段内的每天的数据，查询语句示例如下：
1.controller层代码
点击查看代码
private HomePageService homePageService;
@Autowired
public void setHomePageService(HomePageService homePageService) {
this.homePageService = homePageService;
}
@GetMapping("/order_home_page")
@ApiOperation("【订单数据统计】")
@ApiImplicitParams({
@ApiImplicitParam(name = "startDate", value = "开始日期", paramType = "query", dataType="Date",defaultValue = "2022-1-1"),
@ApiImplicitParam(name = "overDate", value = "结束日期", paramType = "query", dataType="Date",defaultValue = "2022-1-30")
})
public Result<OrderHomePageVO> orderHomePage(@ApiIgnore @RequestParam Map<String, Object> params){
OrderHomePageVO vo = homePageService.orderHomePage(params);
return new Result<OrderHomePageVO>().ok(vo);
}
2.service层代码
点击查看代码
/**
* 订单数据统计
* @param params
* @return
*/
OrderHomePageVO orderHomePage(Map<String, Object> params);
3.serviceImpl实现类代码
点击查看代码
private HomePageDao homePageDao;
@Autowired
public void setHomePageDao(HomePageDao homePageDao) {
this.homePageDao = homePageDao;
}
@Override
public OrderHomePageVO orderHomePage(Map<String, Object> params) {
OrderHomePageVO orderHomePageVO = new OrderHomePageVO();
//1.查询指定时间段内的订单信息
HomePageVo1 homePageVo1 = new HomePageVo1();
Integer orderTotal=homePageDao.getOrderTotal(params);
List<Map<String,Object>> map=homePageDao.getEveryMap(params);
homePageVo1.setOrderTotal(orderTotal);
homePageVo1.setMap(map);
orderHomePageVO.setHomePageVo1(homePageVo1);
log.info("【订单统计】-1.查询指定时间段内的订单信息"+homePageVo1);
//2.各宾馆的订单数据
List<Map<String,Object>> hotelMap=homePageDao.getHotelMap(params);
orderHomePageVO.setHotelMap(hotelMap);
log.info("【订单统计】-2.各宾馆的订单数据"+hotelMap);
//3.各服务类型订单数据
List<Map<String,Object>> serviceTypeMap=homePageDao.getServiceTypeMap(params);
orderHomePageVO.setServiceTypeMap(serviceTypeMap);
log.info("【订单统计】-3.各服务类型订单数据"+serviceTypeMap);
//4.各房间类型的订单数据
List<Map<String,Object>> roomTypeMap=homePageDao.getRoomTypeMap(params);
orderHomePageVO.setRoomTypeMap(roomTypeMap);
log.info("【订单统计】-4.各房间类型的订单数据"+roomTypeMap);
//5.各服务星级的订单数据
List<WaiterGradeOrderVO> waiterGradeOrderVOList=homePageDao.getWaiterGradeOrderVO();
if (waiterGradeOrderVOList!=null){
for (WaiterGradeOrderVO waiterGradeOrderVO:waiterGradeOrderVOList) {
if (waiterGradeOrderVO.getStarName()!=null){
List<Long> ids=homePageDao.getIds(waiterGradeOrderVO.getStarName());
if (ids!=null){
Integer allTotal=0;
for (Long waiterGradeId:ids) {
List<Long> waiterUserIds=homePageDao.getWaiterUserIds(waiterGradeId);
if (waiterUserIds.isEmpty()){
waiterGradeOrderVO.setTotal(0);
}else {
Integer waiterTotal = homePageDao.getWaiterOrderTotal(waiterUserIds,params);
allTotal=waiterTotal+allTotal;
}
}
waiterGradeOrderVO.setTotal(allTotal);
}
}
}
orderHomePageVO.setWaiterGradeOrderVOList(waiterGradeOrderVOList);
log.info("【订单统计】-5.各服务星级的订单数据"+waiterGradeOrderVOList);
}
return orderHomePageVO;
}
4.dao+XML代码
4.1某时间段内每日的订单统计信息
其中的help表，就是上面提到的临时表（具有连续id的一张表）
点击查看dao代码
/**
* 某时间段内每日的订单统计信息
* @param params
* @return
*/
List<Map<String, Object>> getEveryMap(Map<String, Object> params);
点击查看dao.xml代码
<select id="getEveryMap" resultType="java.util.Map">
select t1.day, ifnull(u1.total, 0) as num from
(
select date_format(date_sub(#{overDate},interval t.id day),'%Y-%m-%d') as day
from help t,
(
select TIMESTAMPDIFF(day,#{startDate}, #{overDate})+1 as subday
) as subdayt
where t.id < subdayt.subday
) as t1 left join
(
select date_format(u.create_date, '%Y-%m-%d') as r_t , count(id) as total from user_order u WHERE u.status=1 group by r_t
) as u1 on u1.r_t = t1.day
ORDER BY t1.day asc
</select>
4.2各宾馆的订单数据
点击查看dao代码
/**
* 各宾馆的订单数据
* @param params
* @return
*/
List<Map<String, Object>> getHotelMap(Map<String, Object> params);
点击查看dao.xml代码
<select id="getHotelMap" resultType="java.util.Map">
SELECT
A.hotel_name hotelName,
IFNULL((SELECT COUNT(id) FROM user_order WHERE status=1 AND hotel_id=A.id AND create_date between #{startDate} AND #{overDate}),0) total
FROM
hotel A
WHERE
A.status = 1
GROUP BY
A.hotel_name
</select>
