主页 / dba / 问题 / 120122
Accepted
Mark D
Asked: 2015-11-05 12:44:04 +0800 CST

从表中删除值小于聚合值的行

  • 772

我正在尝试从表中删除重复项。

CREATE TABLE media_views (
  `companyid` INT (11) NOT NULL,
  `channel` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `views` INT(11) NOT NULL DEFAULT 0
  PRIMARY KEY (`companyid`,`channel`,`date`,`views`)
) ENGINE=InnoDB;

INSERT INTO media_views (companyid, channel, `date`, views)
     VALUES (1, 0,  '2015-10-01', 15),
            (1, 0,  '2015-10-01', 25),
            (1, 0,  '2015-10-01', 541),
            (1, 5,  '2015-10-01', 2),
            (1, 8,  '2015-10-01', 53),
            (1, 91, '2015-10-01', 8571),
            (3, 12, '2015-10-01', 11),
            (1, 0,  '2015-10-02', 1),
            (1, 0,  '2015-10-02', 13),
            (1, 5,  '2015-10-02', 17),
            (1, 91, '2015-10-03', 37),
            (3, 0,  '2015-10-03', 73);

现在的问题是我有“重复”错误，我有与日期匹配但视图不同的记录。只有视图的最大值才是真正有用的。所以我可以通过执行以下操作来解决这个问题。

SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1;

这清楚地为我提供了以下内容：

+-----------+---------+------------+------------+
| companyid | channel | date       | MAX(views) |
+-----------+---------+------------+------------+
| 1         | 0       | 2015-10-01 | 541        |
| 1         | 0       | 2015-10-02 | 13         |
| 1         | 5       | 2015-10-01 | 2          |
| 1         | 5       | 2015-10-02 | 17         |
| 1         | 8       | 2015-10-01 | 53         |
| 1         | 91      | 2015-10-01 | 8571       |
| 1         | 91      | 2015-10-03 | 37         |
| 3         | 0       | 2015-10-03 | 73         |
| 3         | 12      | 2015-10-01 | 11         |
+-----------+---------+------------+------------+
9 rows in set (0.11 sec)

这很好，但我如何返回并删除重复且小于 max(views) 值的行？

mariadb clustered-primary-key

2 个回答

  1. Best Answer

    您可以选择要继续使用左连接的行 - http://sqlfiddle.com/#!9/3a677/3

    select *
from media_views m1
left join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views)
where m2.companyid is null;

    你可以使用类似的内部连接删除其他的 - http://sqlfiddle.com/#!9/d5d36/2

    delete m1 from media_views m1
join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views);

    并且不要忘记在删除它们后修改主键，这样就不可能了。

    • 1 
  2. delete from media_views
where (companyid,channel,date,views) 
not in 
(
SELECT companyid, channel, date, MAX(views) 
FROM media_views 
GROUP BY companyid, channel, date 
HAVING COUNT(1) >= 1
);
    • 0

相关问题