CREATE TABLE `wsz` (
`name` char(2) NOT NULL,
`money` int(11) NOT NULL,
`time` date NOT NULL,
KEY `name` (`name`,`money`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz`
#INSERT INTO `wsz` (`name`, `money`, `time`) VALUES
('a', 10, '2011-06-01'),
('a', 0, '2011-06-01'),
('a', 10, '2011-06-01'),
('a', 0, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 0, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 0, '2011-06-01'),
--------------
只删2011-06-01的重复的记录,最后结果要:a 10 2011-06-01
a 0 2011-06-01
b 6 2011-06-01
b 0 2011-06-01
`name` char(2) NOT NULL,
`money` int(11) NOT NULL,
`time` date NOT NULL,
KEY `name` (`name`,`money`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;#
# 导出表中的数据 `wsz`
#INSERT INTO `wsz` (`name`, `money`, `time`) VALUES
('a', 10, '2011-06-01'),
('a', 0, '2011-06-01'),
('a', 10, '2011-06-01'),
('a', 0, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 0, '2011-06-01'),
('b', 6, '2011-06-01'),
('b', 0, '2011-06-01'),
--------------
只删2011-06-01的重复的记录,最后结果要:a 10 2011-06-01
a 0 2011-06-01
b 6 2011-06-01
b 0 2011-06-01
drop tabl tb;alter table tb_temp rename tb;
INSERT INTO `wsz` ('id',`name`, `money`, `time`) VALUES
(1,'a', 10, '2011-06-01'),
(2,'a', 0, '2011-06-01'),
(3,'a', 10, '2011-06-01'),
(4,'a', 0, '2011-06-01'),
(5,'b', 6, '2011-06-01'),
(6,'b', 0, '2011-06-01'),
(7,'b', 6, '2011-06-01'),
(8,'b', 0, '2011-06-01')delete wsz
where id not in (
select max(id) from wsz
group by name,money)
select max(id) from wsz
group by name,money
分组查询出重复记录id最大的
然后id not in();