我想创建一个销售报表,用户可以在其中看到每天、每周和每月的销售额。
这是我的表:
CREATE TABLE IF NOT EXISTS `sales_act` (
`id` int(11) NOT NULL auto_increment,
`sale` decimal(10,2) NOT NULL default '0.00',
`paymethod` smallint(2) NOT NULL default '0',
`saledate` datetime NOT NULL default '0000-00-00 00:00:00',
`status` smallint(2) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
INSERT INTO `sales_act` (`id`, `sale`, `paymethod`, `saledate`, `status`) VALUES
(1, '150.00', 3, '2016-07-30 14:37:25', 2),
(2, '50.00', 1, '2016-08-14 21:38:34', 1),
(3, '150.00', 3, '2016-08-15 14:23:21', 2),
(4, '100.00', 1, '2016-08-15 14:25:12', 1),
(5, '50.00', 2, '2016-08-15 14:27:31', 3);
我在这里阅读stackoverflow的一些例子,但我似乎不能使它工作,这就是我到目前为止,但不工作:(
$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")');
while ($row = $result->fetch_assoc()) {
echo '<pre>';
print_r($row);
echo '</pre>';
}
谁能告诉我我做错了什么?提前感谢!!: D 您错过的FROM子句应该使用MONTH(您已经将日期设置为日期时间)
'SELECT MONTH(saledate) AS Month, SUM(sale) AS `salessum`
FROM sales_act
WHERE `status` < 3 GROUP BY MONTH(saledate)'
您忘记在这一行指定数据库:
$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")');
更好:
$result = $mysqli->query('SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month, SUM(sale) AS `salessum` FROM sales_act WHERE `status` < 3 GROUP BY DATE_FORMAT(saledate, "%m-%Y")');
SELECT DATE_FORMAT(saledate, "%m-%Y") AS Month,
SUM(sale) AS `salessum`
FROM sales_act
WHERE status < 3
GROUP BY DATE_FORMAT(saledate, "%m-%Y")