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