我正试图把每天的订单量的总和画出来。
SELECT DATE(FROM_UNIXTIME(timestamp)) AS timey,
SUM(amount) AS cash
FROM orders
WHERE touid = :uid AND status = 1
GROUP BY DATE(FROM_UNIXTIME(timestamp))
工作正常，除了sum=0时，它不会显示在数组中。我不得不手动拼接输出，以限制这个月31个结果。
我已经看到了
date_format和"%m"属性的使用，但是还没有找到解决方法。编辑：
Array (
[0] => Array ( [timey] => 2015-03-22 [cash] => 0.03 )
[1] => Array ( [timey] => 2015-03-23 [cash] => 0.2 )
[2] => Array ( [timey] => 2015-03-29 [cash] => 0.08 )
[3] => Array ( [timey] => 2015-04-03 [cash] => 0.03 )
[4] => Array ( [timey] => 2015-04-04 [cash] => 0.99 )
[5] => Array ( [timey] => 2015-04-06 [cash] => 1.55 )
[6] => Array ( [timey] => 2015-04-07 [cash] => 0.03 )
[7] => Array ( [timey] => 2015-04-10 [cash] => 4 )
[8] => Array ( [timey] => 2015-04-13 [cash] => 5 )
[9] => Array ( [timey] => 2015-04-14 [cash] => 8 )
[10] => Array ( [timey] => 2015-04-17 [cash] => 1 )
[11] => Array ( [timey] => 2015-04-18 [cash] => 4 )
[12] => Array ( [timey] => 2015-05-01 [cash] => 5 )
[13] => Array ( [timey] => 2015-05-02 [cash] => 2 )
[14] => Array ( [timey] => 2015-05-03 [cash] => 1 )
[15] => Array ( [timey] => 2015-05-05 [cash] => 3 )
[16] => Array ( [timey] => 2015-05-06 [cash] => 1 )
[17] => Array ( [timey] => 2015-05-07 [cash] => 12 )
[18] => Array ( [timey] => 2015-05-08 [cash] => 1 )
[19] => Array ( [timey] => 2015-05-11 [cash] => 0.1 )
[20] => Array ( [timey] => 2015-05-13 [cash] => 1 )
[21] => Array ( [timey] => 2015-05-14 [cash] => 1.9 )
[22] => Array ( [timey] => 2015-05-16 [cash] => 2 )
[23] => Array ( [timey] => 2015-05-17 [cash] => 0.15 )
[24] => Array ( [timey] => 2015-05-18 [cash] => 1 )
[25] => Array ( [timey] => 2015-05-19 [cash] => 1.09 )
[26] => Array ( [timey] => 2015-05-23 [cash] => 0.5 )
[27] => Array ( [timey] => 2015-05-24 [cash] => 2.5 )
[28] => Array ( [timey] => 2015-05-26 [cash] => 3 )
[29] => Array ( [timey] => 2015-05-27 [cash] => 2 )
[30] => Array ( [timey] => 2015-05-28 [cash] => 4 )
)
在我用PHP将其切片后输出。
最佳答案
编辑：
好吧，试试这个概念，不知羞耻地取自here。
相应地调整你的名字和子句，但是你得到了这个概念。
CREATE TABLE orders
(
id INT PRIMARY KEY AUTO_INCREMENT,
order_date DATETIME,
product_id INT,
quantity INT,
customer_id INT
);
INSERT INTO orders (order_date, product_id, quantity, customer_id)
VALUES
('2009-08-15 12:20:20', '1', '2', '123'),
('2009-08-15 12:20:20', '2', '2', '123'),
('2009-08-17 16:43:09', '1', '1', '456'),
('2009-08-18 09:21:43', '1', '5', '789'),
('2009-08-18 14:23:11', '3', '7', '123'),
('2009-08-21 08:34:21', '1', '1', '456');
.
CREATE TABLE aCalendar (datefield DATE);
.
DELIMITER |
CREATE PROCEDURE fill_calendar(start_date DATE, end_date DATE)
BEGIN
DECLARE crt_date DATE;
SET crt_date=start_date;
WHILE crt_date < end_date DO
INSERT INTO aCalendar VALUES(crt_date);
SET crt_date = ADDDATE(crt_date, INTERVAL 1 DAY);
END WHILE;
END |
DELIMITER ;
把日历日塞进很多年：
CALL fill_calendar('2007-01-01', '2009-12-31');
CALL fill_calendar('2010-01-01', '2017-12-31'); -- coffee break, 1 minute
-- select count(*) from aCalendar;
-- an index on aCalendar wouldn't be a bad idea
2009年8月销售额：
SELECT aCalendar.datefield AS theDate,
IFNULL(SUM(orders.quantity),0) AS total_sales
FROM orders RIGHT JOIN aCalendar ON (DATE(orders.order_date) = aCalendar.datefield)
WHERE (aCalendar.datefield BETWEEN DATE('2009-08-01') AND DATE('2009-08-31'))
GROUP BY theDate
ORDER BY theDate
theDate total_sales
2009-08-01 0
2009-08-02 0
2009-08-03 0
2009-08-04 0
2009-08-05 0
2009-08-06 0
2009-08-07 0
2009-08-08 0
2009-08-09 0
2009-08-10 0
2009-08-11 0
2009-08-12 0
2009-08-13 0
2009-08-14 0
2009-08-15 4
2009-08-16 0
2009-08-17 1
2009-08-18 12
2009-08-19 0
2009-08-20 0
2009-08-21 1
2009-08-22 0
2009-08-23 0
2009-08-24 0
2009-08-25 0
2009-08-26 0
2009-08-27 0
2009-08-28 0
2009-08-29 0
2009-08-30 0
2009-08-31 0
关于mysql - 选择一个月中每天的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30719716/