>我有如下查询
select max(id), count(id)
from table
group by col1, col2
having count(col1)>1 and count(col2)>1;
结果输出如下
**+---------+-----------+
| max(id) | count(id) |
+---------+-----------+
| 43 | 4 |
| 108 | 2 |
| 171 | 2 |
| 177 | 2 |
| 195 | 2 |
+---------+-----------+**
用于查找多个记录具有相同 col1 和 col2 的重复记录
从这里我必须删除除 max(id( 记录之外的所有记录。
我试过这样的
Delete
from payment_records
group by policy_id, ref_txn_no
having count(policy_id)>1 and count(ref_txn_no)>1
where id !=(select max(id)
from payment_records
group by policy_id,ref_txn_no
having count(policy_id)>1 and count(ref_txn_no)>1)
正确吗
id 应该是唯一的。因此,您可以按 id 选择要删除的数据行,而不需要分组等。内部选择返回多个值,因此应使用 IN 语句。
以下代码应该有效,但它不起作用,因为 MySql 无法从嵌套在 where 子句中的表中删除。
DELETE FROM payment_records
WHERE NOT id IN (
SELECT max(id)
FROM payment_records
GROUP BY policy_id, ref_txn_no
HAVING count(policy_id) > 1 AND count(ref_txn_no) > 1
)
但是有一个解决方法,无需创建时态表:
DELETE FROM payment_records
WHERE NOT id IN (
SELECT max_id
FROM (
SELECT max(id) max_id
FROM payment_records
GROUP BY policy_id, ref_txn_no
HAVING count(policy_id) > 1 AND count(ref_txn_no) > 1
) AS t
)
据我所知,MySQL不允许从子查询中的同一表中选择删除。您必须使用临时表。
create temporary table t as
select
max(id)
from payments_records
group by
policy_id,
ref_txn_no;
delete from payments_record
where id not in (select * from t);
drop temporary table t;
我认为这两个语句不需要
按policy_id--->分组,ref_txn_no
---->具有计数(policy_id(>1 和计数 (ref_txn_no(>1
Delete
from payment_records
where id !=(select max(id)
from payment_records
group by policy_id,ref_txn_no
having count(policy_id)>1 and count(ref_txn_no)>1)
试试这个。它解决了我的问题,类似于你。
DELETE FROM table WHERE id NOT IN (
SELECT MAX(id)
FROM table
GROUP BY col1,col2
HAVING COUNT(col1)>1 and COUNT(col2)>1
)
注意:在发布之前,我没有在 SSMS 中尝试过此查询,但它应该可以工作,可能只需很少修改:)