mysql - 复杂的清除MYSQL

Question

我需要删除除带有日期和名称的最大数字之外的数字

CREATE TABLE IF NOT EXISTS `test_1m` (
  `name` varchar(11) NOT NULL,
  `rate` varchar(15) NOT NULL,
  `dt` datetime NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `test_1m` (`name`, `rate`, `dt`) VALUES
('kurkuma', '0.00002150', '2018-04-06 12:04:01'),
('kurkuma', '0.00002151', '2018-04-06 12:04:02'),
('kurkuma', '0.00002152', '2018-04-06 12:04:03'),
('kurkuma', '0.00002153', '2018-04-06 12:04:04'),
('kurkuma', '0.00002159', '2018-04-06 12:04:05'),
('kurkuma', '0.00002154', '2018-04-06 12:04:06'),
('kurkuma', '0.00002155', '2018-04-06 12:04:07'),
('kurkuma', '0.00002156', '2018-04-07 12:04:08'),
('kurkuma', '0.00002157', '2018-04-07 12:04:09'),
('pahlava', '0.0000001', '2018-04-06 12:04:02'),
('pahlava', '0.0000002', '2018-04-07 12:04:02');

必须是

('kurkuma', '0.00002159', '2018-04-06 12:04:05'),
('kurkuma', '0.00002157', '2018-04-07 12:04:09'),
('pahlava', '0.0000001', '2018-04-06 12:04:02'),
('pahlava', '0.0000002', '2018-04-07 12:04:02');

也许是这样的。只是不起作用=)

DELETE * FROM `test_1m`
WHERE NOT (
    SELECT max(rate)
    FROM `test_1m`
    GROUP BY `name`, DATE(`dt`)
)

Answer 1

在 MySQL 中，您必须使用 join 对同一个表进行表达过滤:

delete t
    from test_1m t join
         (select name, date(dt) as dt, max(rate) as rate
          from test_1m
          group by name, date(dt)
         ) tt
         on tt.name = t.dt and tt.dt = t.date and
            t.rate < tt.rate;

如果这要删除大量记录(例如表的百分之几以上)，则截断并重新插入可能会更快:

create temporary table tokeep as
    select t.*
    from test_1m t
    where not exists (select 1
                      from test_1m t2
                      where t2.name = t.name and
                            date(t2.dt) = date(t.dt) and
                            t2.rate > t.rate
                      );

truncate table test_1m;

insert into test_1m
    select * from tokeep;

当然，在尝试此操作之前测试代码并备份表。