-- This query is not editable, but generally faster
Select
in_type,
in_type_serial,
count(*)
from ec_product_in
group by
in_type,
in_type_serial
having count(*) > 1
-- This query is editable, but generally slower
Select A1.id
from ec_product_in A1
where exists (Select 'x' from ec_product_in A2
where A1.in_type = A2.in_type
and a1.in_type_serial= A2.in_type_serial
and A1.ROWID <> A2.ROWID)
-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)
找出需要删除的行
/* Formatted on 2011-8-17 9:59:53 (QP5 v5.114.809.3010) */
-- This query is editable, but generally slower
SELECT A1.*, A1.ROWID
FROM ec_product_in A1
WHERE EXISTS
(SELECT 'x'
FROM ec_product_in A2
WHERE A1.in_type = A2.in_type
AND a1.in_type_serial = A2.in_type_serial
AND A1.ROWID <> A2.ROWID)
-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)
AND a1.id NOT IN
(SELECT A1.id
FROM ec_product_in A1
WHERE EXISTS
(SELECT 'x'
FROM ec_product_in A2
WHERE A1.in_type = A2.in_type
AND a1.in_type_serial =
A2.in_type_serial
AND A1.ROWID > A2.ROWID)-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)
)