订单统计

订单表格式如下

 

 

 

 

1.下单统计

SELECT
-- 	YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time ))  , DAY ( FROM_UNIXTIME( create_time )) ,
	CONCAT_WS('/',YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time ))  , DAY ( FROM_UNIXTIME( create_time )) ) as date,
	COUNT(*) 
FROM
	toy_order 
WHERE
	1 
GROUP BY
	YEAR (
	FROM_UNIXTIME( create_time )),
	MONTH (
	FROM_UNIXTIME( create_time )),
	DAY (
	FROM_UNIXTIME( create_time ));

得到如下：

 

 

 

2.每日的销售额

SELECT
-- YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time )) , DAY ( FROM_UNIXTIME( create_time )) ,
CONCAT_WS('/',YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time )) , DAY ( FROM_UNIXTIME( create_time )) ) as date,
SUM(`pay_price`)
FROM
toy_order
WHERE
1
GROUP BY
YEAR (
FROM_UNIXTIME( create_time )),
MONTH (
FROM_UNIXTIME( create_time )),
DAY (
FROM_UNIXTIME( create_time ));

得到：

 

 

3.统计每月的下单量

SELECT
-- 	YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time ))  , DAY ( FROM_UNIXTIME( create_time )) ,
	CONCAT_WS('/',YEAR ( FROM_UNIXTIME( create_time )) , MONTH ( FROM_UNIXTIME( create_time ))  ) as date,
	COUNT(*) 
FROM
	toy_order 
WHERE
	1 
GROUP BY
	YEAR (
	FROM_UNIXTIME( create_time )),
	MONTH (
	FROM_UNIXTIME( create_time ))
	

去掉日就行了，以此类推；