如何在mysql中获得时间明智的销售报告 [英] How to get time wise sales report in mysql
问题描述
hai,
我需要在mysql中使用Time Wise Sales Report,我得到的报告就像
hai,
I need Time Wise Sales Report in mysql,I get the Report like
Time Amount
19:00:00 2
20:00:00 3
21:00:00 0
22:00:00 0
23:00:00 9
但我需要金额的总和,如
but I need sum of Amount like
Time Amount
19:00:00 2
20:00:00 5
21:00:00 5
22:00:00 5
23:00:00 14
我的尝试:
此查询适用于第一个表
SELECT concat(MID(date_of_call,12,2),':00:00')TimeIntervel,COUNT (*)TotalEntries,date_format(date_of_call,'%d /%m /%Y')FROM ICC_MM_setup_suply
WHERE DATE_FORMAT(date_of_call,'%d /%m /%Y')='31 / 01/2017'
GROUP BY concat(MID(date_of_call,12,2),':00:00')
我需要查询第二个表
What I have tried:
This query is for first table
SELECT concat(MID(date_of_call, 12, 2),':00:00') TimeIntervel,COUNT(*) TotalEntries,date_format(date_of_call,'%d/%m/%Y') FROM ICC_MM_setup_suply
WHERE DATE_FORMAT(date_of_call,'%d/%m/%Y')='31/01/2017'
GROUP BY concat(MID(date_of_call, 12, 2),':00:00')
I need query for 2nd table
推荐答案
从这个例子中适应:
DROP TABLE IF EXISTS `tablename`;
CREATE TABLE `tablename` (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
amount INT NOT NULL
);
INSERT INTO `tablename` (amount)
VALUES
(2),
(3),
(0),
(0),
(0),
(9);
select * from tablename;
SELECT id, (SELECT SUM(amount)
FROM tablename
WHERE id <= a.id)
FROM tablename a;
请参阅演示:如何在mysql中获得时间紧迫的销售报告,MySQL - rextester [ ^ ]
这篇关于如何在mysql中获得时间明智的销售报告的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!