mysql查询最近7天 每天销售额 统计销售额

sql统计每一天的支出数目，支出金额，收入数目，收入金额
finance_type=0：收入
finance_type=1：支出

部分建表语句：

CREATE TABLE `finance` (
  `finance_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `finance_userid` int(11) DEFAULT NULL COMMENT '会员ID',
  `finance_type` char(1) DEFAULT NULL COMMENT '收类型(0:*收入,1:支出)',
#...
  `finance_money` varchar(255) float(10,2) DEFAULT '0' COMMENT'财务金额',
  `create_date` varchar(19) DEFAULT NULL COMMENT '收支时间',
  PRIMARY KEY (`finance_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='收支表';

1
2
3
4
5
6
7
8
9
10

sql查询语句：

SELECT DATE_FORMAT(create_date,'%Y-%m-%d') as statistics_date, 
COUNT(IF(finance_type=0,finance_id,null)) as counts_income,
cast(IFNULL(sum(IF(finance_type=0,finance_money,null)),0) as decimal(15,2)) AS total_income, 
COUNT(IF(finance_type=1,finance_id,null)) as counts_expenses,
cast(IFNULL(sum(IF(finance_type=1,finance_money,null)),0) as decimal(15,2)) AS total_expenses
FROM finance
#where finance_type=0
GROUP BY DATE_FORMAT(create_date,'%Y-%m-%d')
ORDER BY DATE_FORMAT(create_date,'%Y-%m-%d') desc;
1
2
3
4
5
6
7
8
9