MySQL统计一段时间不同分类销售额,当日无数据的为0

先查询出一段日期集合

-- 根据天数查
SELECT @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date 
FROM
			( SELECT 
				@cdate := date_add( CURDATE(), INTERVAL 1 DAY ) 
				FROM ord_order_parts LIMIT 10 
				-- 查出十天
			) oop
	) a
-- 根据月份查
SELECT DATE_FORMAT(@cdate := date_add( @cdate, INTERVAL - 1 MONTH ),'%Y-%m') date 
FROM
			( SELECT 
				@cdate := date_add( CURDATE(), INTERVAL 1 MONTH ) 
				FROM ord_order_parts LIMIT 10  
				-- 查出十个月
			) oop
	) a

需要统计的数据,进行了分类查询

SELECT 
   DATE_FORMAT(oop.create_time,'%Y-%m-%d') time,
   IFNULL(count(oop.amount),0) num,
   IFNULL(SUM(oop.selling_price),0) actualAmount, 
   IFNULL(SUM(oop.amount),0) orderAmount,
   IFNULL(count(CASE WHEN sp.brand_name = #{brandName} THEN oop.amount END),0) gitiNum,
   IFNULL(SUM(CASE WHEN sp.brand_name = #{brandName} THEN oop.selling_price ELSE 0 END), 0) gitiActualAmount,
   IFNULL(SUM(CASE WHEN sp.brand_name = #{brandName} THEN oop.amount ELSE 0 END), 0) gitiAmount,
   IFNULL(count(CASE WHEN sp.brand_name != #{brandName} THEN oop.amount END),0) otherNum,
   IFNULL(SUM(CASE WHEN sp.brand_name != #{brandName} THEN oop.selling_price ELSE 0 END), 0) otherActualAmount,
   IFNULL(SUM(CASE WHEN sp.brand_name != #{brandName} THEN oop.amount ELSE 0 END), 0) otherAmount
   FROM ord_order_parts oop 
   LEFT JOIN sys_parts sp 
   on oop.parts_code = sp.parts_code 
   LEFT JOIN sys_parts_category spc 
   on sp.category_code = spc.category_code 
   where oop.tenant_code = #{tenantCode}
   and oop.org_code = #{orgCode}
   and sp.tenant_code = #{tenantCode} 
   and sp.org_code = #{orgCode}
   and spc.tenant_code = #{tenantCode} 
   and spc.org_code = #{orgCode}
   and oop.create_time >= #{beginDate} 
   and oop.create_time <= NOW()
   and spc.category_name = #{categoryName}
   GROUP BY time ORDER BY time DESC

最后二者左连接查询

SELECT 
a.date,
IFNULL(b.num,0) totalNum,
IFNULL(b.actualAmount,0) actualAmount,
IFNULL(b.orderAmount,0) orderAmount,
IFNULL(b.gitiNum,0) gitiNum,
IFNULL(b.gitiActualAmount,0) gitiActualAmount,
IFNULL(b.gitiAmount,0) gitiAmount,
IFNULL(b.otherNum,0) otherNum,
IFNULL(b.otherActualAmount,0) otherActualAmount,
IFNULL(b.otherAmount,0) otherAmount 
FROM
	( SELECT @cdate := date_add( @cdate, INTERVAL - 1 DAY ) date 
FROM
			( SELECT 
				@cdate := date_add( CURDATE(), INTERVAL 1 DAY ) 
				FROM ord_order_parts LIMIT 10 
			) oop
	) a
LEFT JOIN
 (
   SELECT 
   DATE_FORMAT(oop.create_time,'%Y-%m-%d') time, 
   -- 如果按月份查就去掉 -%d
   IFNULL(count(oop.amount),0) num,
   IFNULL(SUM(oop.selling_price),0) actualAmount, 
   IFNULL(SUM(oop.amount),0) orderAmount,
   IFNULL(count(CASE WHEN sp.brand_name = #{brandName} THEN oop.amount END),0) gitiNum,
   IFNULL(SUM(CASE WHEN sp.brand_name = #{brandName} THEN oop.selling_price ELSE 0 END), 0) gitiActualAmount,
   IFNULL(SUM(CASE WHEN sp.brand_name = #{brandName} THEN oop.amount ELSE 0 END), 0) gitiAmount,
   IFNULL(count(CASE WHEN sp.brand_name != #{brandName} THEN oop.amount END),0) otherNum,
   IFNULL(SUM(CASE WHEN sp.brand_name != #{brandName} THEN oop.selling_price ELSE 0 END), 0) otherActualAmount,
   IFNULL(SUM(CASE WHEN sp.brand_name != #{brandName} THEN oop.amount ELSE 0 END), 0) otherAmount
   FROM ord_order_parts oop 
   LEFT JOIN sys_parts sp 
   on oop.parts_code = sp.parts_code 
   LEFT JOIN sys_parts_category spc 
   on sp.category_code = spc.category_code 
   where oop.tenant_code = #{tenantCode}
   and oop.org_code = #{orgCode}
   and sp.tenant_code = #{tenantCode} 
   and sp.org_code = #{orgCode}
   and spc.tenant_code = #{tenantCode} 
   and spc.org_code = #{orgCode}
   and oop.create_time >= #{beginDate} 
   and oop.create_time <= NOW()
   and spc.category_name = #{categoryName}
   GROUP BY time ORDER BY time DESC
 ) b
on a.date = b.time