java工具类:
/** * 解析两个日期段之间的所有日期 * @param beginDateStr 开始日期 ,至少精确到yyyy-MM-dd * @param endDateStr 结束日期 ,至少精确到yyyy-MM-dd * @return yyyy-MM-dd日期集合 */ public static List<String> getDayListOfDate(String beginDateStr, String endDateStr) { // 指定要解析的时间格式 SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd"); // 定义一些变量 Date beginDate = null; Date endDate = null; Calendar beginGC = null; Calendar endGC = null; List<String> list = new ArrayList<String>(); try { // 将字符串parse成日期 beginDate = f.parse(beginDateStr); endDate = f.parse(endDateStr); // 设置日历 beginGC = Calendar.getInstance(); beginGC.setTime(beginDate); endGC = Calendar.getInstance(); endGC.setTime(endDate); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 直到两个时间相同 while (beginGC.getTime().compareTo(endGC.getTime()) <= 0) { list.add(sdf.format(beginGC.getTime())); // 以日为单位,增加时间 beginGC.add(Calendar.DAY_OF_MONTH, 1); } return list; } catch (Exception e) { e.printStackTrace(); return null; } }
mysql具体代码:
select IFNULL(o.time,t.time) time,IFNULL(o.allCount,0) allCount,IFNULL(o.totalAmount,0) totalAmount
from (
select '2018-09-15' time
union
select '2018-09-16' time
union
select '2018-09-17' time
//........ 循环传入的时间list
) t
left join (select DATE_FORMAT(create_time,'%Y-%m-%d') time,count(1) allCount,sum(total_amount) totalAmount
from t_order
where DATE_FORMAT(create_time,'%Y-%m-%d') BETWEEN '2018-09-15' and '2018-09-17'
group by time) o
on o.time = t.time