一.查询一年12个月的订单数量，ps DUAL是虚拟表
SELECT a.count as count, b.time2 FROM (
SELECT count(*) AS count,
FROM_UNIXTIME( UNIX_TIMESTAMP(c.in_date), '%m' ) AS mon FROM ti_supplyorder c
where
<if test="order_state!=null">
c.order_state = #{order_state} AND
</if>
FROM_UNIXTIME( UNIX_TIMESTAMP(c.in_date), '%Y' ) = #{year}
GROUP BY mon
) a
RIGHT JOIN (
SELECT '01' AS time2 FROM DUAL UNION ALL
SELECT '02' AS time2 FROM DUAL UNION ALL
SELECT '03' AS time2 FROM DUAL UNION ALL
SELECT '04' AS time2 FROM DUAL UNION ALL
SELECT '05' AS time2 FROM DUAL UNION ALL
SELECT '06' AS time2 FROM DUAL UNION ALL
SELECT '07' AS time2 FROM DUAL UNION ALL
SELECT '08' AS time2 FROM DUAL UNION ALL
SELECT '09' AS time2 FROM DUAL UNION ALL
SELECT '10' AS time2 FROM DUAL UNION ALL
SELECT '11' AS time2 FROM DUAL UNION ALL
SELECT '12' AS time2 FROM DUAL
) b ON a.mon = b.time2 order by b.time2
二、查询七天内的订单数量和金额 （如果是两年？）
例子一、
MySql查询时日期补全（连续）的一种方法
某一日，需要查询订单表中前7天每天的订单数，sql依次写出来：
SELECT
DATE_FORMAT(order_time,'%Y-%m-%d')date,
count(*) orderCount
FROM
orderWHERE
date_sub(curdate(),INTERVAL7DAY) <date(order_time)
GROUPBYDATE_FORMAT(order_time,'%Y-%m-%d')
ORDERBYdateASC
一执行，数据出来了，不过不连续，数量也没有7条，老板生意惨淡，很多时候一天不能来一个单，但程序不能没有数据，所以这里直观的办法就是补上差的数据，怎么补？
第一想到的就是用上层语言来补，比如java：
//从数据库中查询指定天数之前到当前每天的订单数List<Map<String, Object>>orders=orderDao.queryOrder(7);
//补全数据库中不存在的日期，订单数为0List<String>dayList=DateUtil.dayList(7,1,false);//得到7天前到今天每天的日期
boolean exists=false;
for (int i=0;i<dayList.size();i++) {
exists=false;
for (Map<String, Object>hs : orders) {
if(dayList.get(i).equals(hs.get("date"))) {
exists=true;
break;
}
}
if(!exists){
finalStringdate=dayList.get(i);
orders.add(i,newHashMap<String,Object>(){{
put("orderCount",0);
put("datat",date);
}});
}
}
returnorders;
ok了，没问题。
转过头，老板说7天业绩看不出我店半年不开张开张吃半年的本质，要求要自定义开始结束时间来查，我说，好嘞您！
不过上面那种方法就存在问题了，真要选2年的日期来查，那得循环多少次啊？于是开动脑筋，由这里得到启发： mysql按日期分组（group by）查询统计的时候，没有数据补0的解决办法，下面来写sql：
SELECT
od.date,
od.orderCount
FROM
(
SELECT
DATE_FORMAT(order_time,'%Y-%m-%d')date,
count(*) orderCount
FROM
order
WHERE
order_time>'开始时间'andorder_time<'结束时间'
GROUPBYDATE_FORMAT(order_time,'%Y-%m-%d')
UNION(SELECT0,'2017-03-01')
UNION(SELECT0,'2017-03-02')
UNION(SELECT0,'2017-03-03')
UNION(SELECT0,'2017-03-04')
UNION(SELECT0,'2017-03-05')
UNION(SELECT0,'2017-03-06')
UNION(SELECT0,'2017-03-07')
UNION(SELECT0,'2017-03-08')
-- 很多个UNION......
)ASod
GROUPBYod.dateORDERBYod.dateASC
妥了！至于sql中间那一长串的UNION语句（红色部分），那就根据选择的日期用java生成吧！
估计还有更好的方法，欢迎赐教！
例子2：
CURDATE() 函数返回当前的日期
DATE_SUB() 函数从日期减去指定的时间间隔
select a.day,ifnull(b.count,0) as count,IFNULL(b.totalMoney,0) as totalMoney
from (SELECT curdate() as day
union all
SELECT date_sub(curdate(), interval 1 day) as day
union all
SELECT date_sub(curdate(), interval 2 day) as day
union all
SELECT date_sub(curdate(), interval 3 day) as day
union all
SELECT date_sub(curdate(), interval 4 day) as day
union all
SELECT date_sub(curdate(), interval 5 day) as day
union all
SELECT date_sub(curdate(), interval 6 day) as day
) a left join (
select date(add_time) as datetime, count(cust_id) as count,IFNULL(SUM(all_total_amount),0) as totalMoney
from ti_orderinfo
group by date(add_time)
) b on a.day = b.datetime
ORDER BY a.`day`
来自印象笔记---2018-01-30
网友评论