求:按时间合并 按人名合并记录


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