使用mysql，如何按日和月分组以显示总收入？
例如。 （不基于以下数据）
day month revenue
1 01 10.97
2 01 3.57
3 01 0
等等
这是我的数据的示例：
CREATE TABLE IF NOT EXISTS `sales` (
`id` bigint(255) NOT NULL AUTO_INCREMENT,
`timestamp` int(12) NOT NULL,
`product` int(5) NOT NULL,
`publisher` int(5) NOT NULL,
`market` int(5) NOT NULL,
`revenue` float NOT NULL,
`Units` int(5) NOT NULL,
`Downloads` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=138 ;
--
-- Dumping data for table `sales`
--
INSERT INTO `sales` (`id`, `timestamp`, `revenue`) VALUES
(1, 1394150400, 3.65),
(2, 1394064000, 0),
(4, 1393977600, 0),
(5, 1393891200, 7.42),
(6, 1393804800, 0),
(7, 1393718400, 0),
(8, 1393632000, 0),
(9, 1393545600, 0),
(10, 1393459200, 0),
(11, 1393372800, 0),
(12, 1393286400, 3.65),
(13, 1393200000, 3.65),
(14, 1393177032, 0),
(15, 1393090632, 3.65),
(16, 1393004232, 0),
(17, 1392917832, 0),
(18, 1392831432, 0),
(19, 1392745032, 0),
(20, 1392658632, 0),
(21, 1392572232, 0),
(24, 1391881032, 0),
(23, 1392485832, 0),
(25, 1392336000, 0),
(26, 1392249600, 0),
(27, 1392163200, 0),
(28, 1392076800, 0),
(29, 1391990400, 3.81),
(30, 1391904000, 0),
(31, 1391817600, 0),
(32, 1391731200, 3.65),
(33, 1391644800, 3.58),
(34, 1391558400, 3.58),
(35, 1391472000, 0),
(36, 1391385600, 0),
(37, 1391299200, 0),
(38, 1391212800, 7.23),
(39, 1391126400, 0),
(40, 1391040000, 0),
(41, 1390953600, 3.81),
(42, 1390867200, 4.52),
(43, 1390780800, 0),
(44, 1390694400, 3.65),
(45, 1390608000, 3.81),
(46, 1390585032, 0),
(47, 1390435200, 0),
(48, 1390348800, 3.58),
(49, 1390262400, 0),
(50, 1390176000, 0),
(51, 1390089600, 0),
(52, 1390003200, 0),
(53, 1389916800, 3.58),
(54, 1389893832, 0),
(55, 1389744000, 0),
(56, 1389657600, 0),
(57, 1389571200, 0),
(58, 1389484800, 0),
(59, 1389398400, 3.65),
(60, 1389312000, 3.18),
(61, 1389225600, 0),
(62, 1389139200, 0),
(63, 1389052800, 0),
(64, 1389052800, 0),
(65, 1388966400, 3.65),
(66, 1388880000, 4.05),
(67, 1388793600, 0),
(68, 1388707200, 3.65),
(69, 1388620800, 0),
(70, 1388534400, 0),
(71, 1394236800, 0),
(72, 1394236800, 2.51),
(73, 1394236800, 0),
(74, 1394150400, 5.02),
(75, 1394150400, 2.76),
(76, 1394064000, 7.5),
(77, 1394064000, 8.28),
(78, 1393977600, 0),
(79, 1393977600, 0),
(80, 1393891200, 7.5),
(81, 1393891200, 2.36),
(82, 1393804800, 0),
(83, 1393804800, 0),
(84, 1393718400, 2.76),
(85, 1393718400, 0),
(86, 1393632000, 0),
(87, 1393545600, 0),
(88, 1393545600, 2.76),
(89, 1393459200, 2.51),
(90, 1393459200, 2.51),
(91, 1393433613, 2.51),
(92, 1393433613, 0),
(93, 1393286400, 2.54),
(94, 1393286400, 2.76),
(95, 1393200000, 2.52),
(96, 1393200000, 5.51),
(97, 1394323200, 0),
(98, 1394323200, 5.01),
(99, 1394323200, 5.52),
(100, 1394409600, 0),
(101, 1394409600, 2.05),
(102, 1394409600, 5.27),
(103, 1393113600, 5.08),
(104, 1393027200, 5.09),
(105, 1392854400, 5.32),
(106, 1392854400, 7.63),
(107, 1392940800, 0),
(108, 1392595200, 0),
(109, 1392508800, 7.64),
(110, 1392422400, 0),
(111, 1392336000, 2.58),
(112, 1392163200, 5.57),
(113, 1391990400, 0),
(114, 1391817600, 0),
(115, 1391731200, 15.99),
(116, 1391472000, 10.66),
(117, 1391385600, 2.54),
(118, 1391299200, 2.54),
(119, 1391212800, 5.34),
(120, 1391040000, 0),
(121, 1390953600, 2.55),
(122, 1390780800, 10.9),
(123, 1390608000, 12.72),
(124, 1390435200, 7.64),
(125, 1390262400, 2.55),
(126, 1390089600, 9.92),
(127, 1389916800, 2.55),
(128, 1389744000, 2.55),
(129, 1389571200, 5.1),
(130, 1389398400, 2.55),
(131, 1389225600, 5.1),
(132, 1389052800, 7.65),
(133, 1388880000, 5.1),
(134, 1388793600, 9.99),
(135, 1388620800, 0),
(136, 1394582400, 4.14),
(137, 1394582400, 2.76);
最佳答案
SELECT DATE_FORMAT(FROM_UNIXTIME(`timestamp`),'%d') DAY, DATE_FORMAT(FROM_UNIXTIME(`timestamp`),'%m') MONTH, SUM(`revenue`)
FROM sales
GROUP BY DAY,MONTH
ORDER BY MONTH,DAY
Check the FROM_UNIXTIME Function Here
关于mysql - 按日和月分组的结果(php时间戳)，显示每天的总收入，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22391417/