求:按时间合并 按人名合并记录
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`mydate` datetime NOT NULL,
`name` char(6) NOT NULL,
`money` float(8,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `mydate` (`mydate`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# 导出表中的数据 `test`
#
INSERT INTO `test` (`id`, `mydate`, `name`, `money`) VALUES (1, '2011-01-01 00:00:00', 'zhang', '10.00'),
(2, '2011-02-02 00:00:00', 'zhang', '2.00'),
(3, '2011-02-02 00:00:00', 'li', '6.00'),
(4, '2011-02-05 00:00:00', 'li', '2.00');
------------------------------------------
结果:
date--------name ----------money
2011-01-----zhang-----------10
2011-02-----zhang-----------2
------li-------------8
作者: wuszh1688 发布时间: 2011-05-19
SQL code
select mydate,name,sum(money) from test group by mydate,name;
作者: rucypli 发布时间: 2011-05-19
SQL code
select left(mydate,7) `date`,name,sum(money) from test group by date,name order by date,3; +---------+-------+------------+ | date | name | sum(money) | +---------+-------+------------+ | 2011-01 | zhang | 10.00 | | 2011-02 | zhang | 2.00 | | 2011-02 | li | 8.00 | +---------+-------+------------+ 3 rows in set (0.00 sec)
作者: vipper23 发布时间: 2011-05-19