我有一个表格如下:
cust_name cust_address sex orderactive
xxxx yyy m 1
iiii iii f 1
xxxx yyy m 1
我想保留重复的条目并删除唯一的条目,我的结果表(删除重复项后(应如下所示 -
cust_name cust_address sex orderactive
xxxx yyy m 1
xxxx yyy m 1
任何线索或帮助将不胜感激。
您可以使用以下查询:
SELECT cust_name
FROM mytable
GROUP BY cust_name
HAVING COUNT(*) > 1
获取重复记录的cust_name
值。
将此查询用作派生表并联接回原始表以获得所需的结果:
SELECT t1.*
FROM mytable AS t1
JOIN (
SELECT cust_name
FROM mytable
GROUP BY cust_name
HAVING COUNT(*) > 1
) AS t2 ON t1.cust_name = t2.cust_name
您可以通过以下方式使用组:
SELECT *
FROM Persons
WHERE cust_name IN
(SELECT cust_name
FROM Persons
GROUP BY cust_name
HAVING count(cust_name) > 1)
http://sqlfiddle.com
可以使用以下查询从表中删除唯一行。唯一行标识为HAVING COUNT(cust_name) = 1
。
DELETE FROM cust_details
WHERE cust_name
IN (
SELECT cust_name
FROM (SELECT * FROM cust_details) AS cust_details_alias
GROUP BY cust_name HAVING COUNT(cust_name) = 1
);
使用子查询获取重复行的 id,然后将在 where 条件下使用。您的解决方案是:
select DISTINCT * from tbl as tbl1
where cust_name IN (
Select cust_name from tbl where 1 < (
Select count(*) from tbl where cust_name = tbl1.cust_name))