Question

SQL如何删除2个字段的重复数据，如果重复了就只留一条数据，其他的删除，没有ID字段，在线等....急

只能用一条sql实现

Answer 1

提供一种思路：
新建一个表，结构同你要整理的表，
1、把有重复的记录取一条插入新表
2、删除旧表的有重复的数据
3、把新表的数据插入旧表
4、删除新表完工

追问

哥哥，这样做，还不如直接把数据复制到临时表

Answer 2

这是我以前写的实际sql，可参考这个思路：
DELETE a
-- SELECT *
FROM dm_s_pt a
INNER JOIN
(
SELECT * FROM (
SELECT a.*,
ROW_NUMBER() OVER (PARTITION BY a.Batch,a.Index_code,a.Period,a.Region_code,
a.Operator_code,a.Business,a.Technology,a.Customer_type
order by b.Dp_id ASC) AS Row_Num
FROM dm_s_pt a INNER JOIN dm_s_pt b
ON a.Batch = b.Batch
AND a.Index_code = b.Index_code
AND a.Period=b.Period
AND a.Region_code = b.Region_code
AND ISNULL(a.Operator_code,'') = ISNULL(b.Operator_code,'')
AND ISNULL(a.Business,'') = ISNULL(b.Business,'')
AND ISNULL(a.Technology,'') = ISNULL(b.Technology,'')
AND ISNULL(a.Customer_type,'') = ISNULL(b.Customer_type,'')
AND ISNULL(a.Data_flag,'-1') = ISNULL(b.Data_flag,'-1')
AND a.Dp_id<>b.Dp_id
AND a.Data_flag='SOURCE'
) t WHERE Row_Num>1 /*挑出重复行并编号，然后删掉Row_Num>1的行（只保留编号为1的行）*/
) b
ON a.Batch = b.Batch
AND a.Index_code = b.Index_code
AND a.Period=b.Period
AND a.Region_code = b.Region_code
AND ISNULL(a.Operator_code,'') = ISNULL(b.Operator_code,'')
AND ISNULL(a.Business,'') = ISNULL(b.Business,'')
AND ISNULL(a.Technology,'') = ISNULL(b.Technology,'')
AND ISNULL(a.Customer_type,'') = ISNULL(b.Customer_type,'')
AND ISNULL(a.Data_flag,'-1') = ISNULL(b.Data_flag,'-1')
AND ISNULL(a.Dp_id,'-1') = ISNULL(b.Dp_id,'-1')
AND ISNULL(a.Value,'-999') = ISNULL(b.Value,'-999')

Answer 3

select col1,col2,count(*)
from tab
group by col1,col2
having count(*)=1
union all
select col1,col2,count(*)
from tab
group by col1,col2
having count(*)>1

追问

哥哥，其他的数据是删除，不是要你查询出只有一条的数据，而且只能用一句SQL语句，谢谢

Answer 4

删除重复数据必须保证每条数据有一个列来区分特殊性,一般都有id字段来区分,

DELETE FROM TABLE_NAME
WHERE id

Answer 5

delete from table_name where (col1,col2) not in (select max(col1),max(col2) from table_name group by col1,col2)

追问

delete from tb_Users where (Names,Age) not in (select max(Names),max(Age) from tb_Users group by Names,Age)
为什么这段代码总显示,处 错误？

追答

delete from tb_Users where rowid not in (select max(rowid) from a group by Names,Age)
如果完全没区别 一条语句是做不到的 我这是oracle的写法 rowid这物理地址肯定是唯一的 如果是其他的 目前应该没有一条语句就能解决的办法

追问

rowID不行，没有这个函数，谢谢了

追答

rowid不是函数 是oracle给每个行数据的物理地址字段 如果是其他数据库 真没能一个sql就能解决的办法