CREATE TABLE `tab` (
`name` varchar(10) NOT NULL,
`money` varchar(10) NOT NULL,
`mydate` datetime NOT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab`
#INSERT INTO `tab` (`name`, `money`, `mydate`) VALUES ('wsz', '10', '2011-08-01 09:30:55'),
('wsz', '10', '2011-08-01 09:30:57'),
('wsz', '10', '2011-08-01 09:40:57'),
('wsz', '10', '2011-08-01 09:40:27'),
('yrm', '22', '2011-08-01 09:38:55'),
('yrm', '10', '2011-08-01 09:43:18'),
('yrm', '10', '2011-08-01 09:43:19'),
('yrm', '15', '2011-08-01 09:51:15'),
('yrm', '15', '2011-08-01 09:51:15'),
('yrm', '10', '2011-08-01 09:57:15');
--------------------------------
要取出:
只要时间是今天的,并且时间要小于2011-08-01 09:57分以前的记录。重复的用户要合并,钱也要合并,结果如下:wsz 20
yrm 25
`name` varchar(10) NOT NULL,
`money` varchar(10) NOT NULL,
`mydate` datetime NOT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `tab`
#INSERT INTO `tab` (`name`, `money`, `mydate`) VALUES ('wsz', '10', '2011-08-01 09:30:55'),
('wsz', '10', '2011-08-01 09:30:57'),
('wsz', '10', '2011-08-01 09:40:57'),
('wsz', '10', '2011-08-01 09:40:27'),
('yrm', '22', '2011-08-01 09:38:55'),
('yrm', '10', '2011-08-01 09:43:18'),
('yrm', '10', '2011-08-01 09:43:19'),
('yrm', '15', '2011-08-01 09:51:15'),
('yrm', '15', '2011-08-01 09:51:15'),
('yrm', '10', '2011-08-01 09:57:15');
--------------------------------
要取出:
只要时间是今天的,并且时间要小于2011-08-01 09:57分以前的记录。重复的用户要合并,钱也要合并,结果如下:wsz 20
yrm 25
+------+-------+---------------------+
| name | money | mydate |
+------+-------+---------------------+
| wsz | 10 | 2011-08-01 09:30:55 |
| wsz | 10 | 2011-08-01 09:30:57 |
| wsz | 10 | 2011-08-01 09:40:57 |
| wsz | 10 | 2011-08-01 09:40:27 |
| yrm | 22 | 2011-08-01 09:38:55 |
| yrm | 10 | 2011-08-01 09:43:18 |
| yrm | 10 | 2011-08-01 09:43:19 |
| yrm | 15 | 2011-08-01 09:51:15 |
| yrm | 15 | 2011-08-01 09:51:15 |
| yrm | 10 | 2011-08-01 09:57:15 |
+------+-------+---------------------+
10 rows in set (0.00 sec)mysql> select name,sum(money)
-> from tab
-> where mydate<='2011-08-01 09:57'
-> group by name;
+------+------------+
| name | sum(money) |
+------+------------+
| wsz | 40 |
| yrm | 72 |
+------+------------+
2 rows in set (0.02 sec)mysql>实在无法理解你的wsz 20是怎么算出来的?!以下这四条记录不符合你的要求吗? (时间是今天的,并且时间要小于2011-08-01 09:57分以前的记录)
| wsz | 10 | 2011-08-01 09:30:55 |
| wsz | 10 | 2011-08-01 09:30:57 |
| wsz | 10 | 2011-08-01 09:40:57 |
| wsz | 10 | 2011-08-01 09:40:27 |
| wsz | 10 | 2011-08-01 09:30:55 |
| wsz | 10 | 2011-08-01 09:30:57 |
以上09:30这二条算一条
| wsz | 10 | 2011-08-01 09:40:57 |
| wsz | 10 | 2011-08-01 09:40:27 |
这二条09:40算一条09:30的10元+09:40的10元
-> from tab a
-> where mydate<'2011-08-01 09:57'
-> and not exists (
-> select 1 from tab where name=a.name
-> and mydate-interval second(mydate) second=a.mydate-interval second(a.myd
ate) second
-> and mydate>a.mydate)
-> group by name;
+------+------------+
| name | sum(money) |
+------+------------+
| wsz | 20 |
| yrm | 62 |
+------+------------+
2 rows in set (0.00 sec)mysql>仍然无法无天理解你的yrm 25是怎么来的。
| yrm | 22 | 2011-08-01 09:38:55 |这条钱不重复不要
| yrm | 10 | 2011-08-01 09:43:18 |
| yrm | 10 | 2011-08-01 09:43:19 |
以上这二条算一条
| yrm | 15 | 2011-08-01 09:51:15 |
| yrm | 15 | 2011-08-01 09:51:15 |
以上这二条算一条
| yrm | 10 | 2011-08-01 09:57:15 |这条时间不符合不要
-----------------10+15=25