根据多个字段删除重复数据，只保留一条数据

现在根据Person_ID,System_No,CONVERT(varchar(100), Consume_Time, 23),Repast_No 这四个条件来判断
我自己写的：
delete from XF_Consume a 
where
(a.Person_ID,a.Repast_No,CONVERT(varchar(100), a.Consume_Time, 23),a.System_No) in (select Person_ID,Repast_No,CONVERT(varchar(100), Consume_Time, 23),System_No from XF_Consume group by
Person_ID,Repast_No,CONVERT(varchar(100), Consume_Time, 23),System_No having count(*) > 1)
and ID_Key not in (select min(ID_KEY) from
XF_Consume group by Person_ID,Repast_No,CONVERT(varchar(100), Consume_Time, 23),System_No having count(*)>1)
为什么会报错呢

15 个解决方案

#1

delete from XF_Consume a 
INNER JOIN (
SELECT *,ROW_NUMBER()OVER(PARTITION BY Person_ID,System_No,CONVERT(varchar(100), Consume_Time, 23),Repast_No ORDER BY ID_KEY) AS seq FROM XF_Consume b
) t ON t.ID_KEY=a.ID_KEY AND t.seq>1

#2

SQL SERVER 没有这种写法： where (a,b,c) in(1,4,3)

#3

delete mytable t 
where exists(select 1from mytable r where t.ID= r.ID and t.time > r.time )


sql server 可以参考这种语法。

#4

引用 3 楼 wmxcn2000 的回复:

delete mytable t 
where exists(select 1from mytable r where t.ID= r.ID and t.time > r.time )


sql server 可以参考这种语法。

delete  XF_Consume A where 只写到这里，就报错了，语法错误

#5

引用 4 楼 weimei521 的回复:

delete  XF_Consume A where 只写到这里，就报错了，语法错误

把你的表结构贴一下， create table 形式的； 再给出一些测试数据；

#6

引用 5 楼 wmxcn2000 的回复:

Quote: 引用 4 楼 weimei521 的回复:

delete  XF_Consume A where 只写到这里，就报错了，语法错误

把你的表结构贴一下， create table 形式的； 再给出一些测试数据；

表：
IF OBJECT_ID('XF_ConsumeCleanup') IS NULL
CREATE TABLE [dbo].[XF_ConsumeCleanup] (
[ID_KEY] [bigint] IDENTITY (1, 1) NOT NULL ,
[Person_ID] [int] NOT NULL ,
[System_No] [varchar] (10) NOT NULL ,
[Consume_Mode] [int] NOT NULL ,
[Consume_Time] [datetime] NOT NULL ,
[Before_Fund] [money] NULL ,
[Consume_Fund] [money] NOT NULL ,
[Consume_Copy] [int] NULL ,
[Consume_TimeLen] [float] NULL ,
[Consume_Capacity] [float] NULL ,
[Repast_No] [varchar] (3) NULL ,
[Ware_ID] [int] NULL ,
[MOC_No] [varchar] (6) NOT NULL ,
[COM_No] [varchar] (6) NULL ,
[Card_Balance] [money] NOT NULL ,
[DB_Balance] [money] NULL ,
[Card_SmallAccount] [money] NULL ,
[Data_Type] [tinyint] NOT NULL ,
[Remark] [varchar] (20) NULL ,
[SalesPerson] [varchar] (20) NULL ,
[Download_Time] [datetime] NULL,
    PRIMARY KEY (ID_KEY ASC) 
)
GO
数据：
insert into XF_Consume(Person_ID,System_No,Consume_Mode,Consume_Time,Consume_Fund,Before_Fund,Consume_Copy,
Consume_TimeLen,Consume_Capacity,Repast_No,Ware_ID,MOC_No,COM_No,Card_Balance,DB_Balance,Card_SmallAccount,
Data_Type,Remark,Download_Time) values(6,'SF',0,CONVERT(varchar(100),GETDATE(),20),10.00,0.00,1,0,0,'003',-1,
'SF001',null,55.00,55.00,0.00,0,null,null);

#7

你在 4 # 的表名，写的不对，你核实一下；

#8

引用 7 楼 wmxcn2000 的回复:

你在 4 # 的表名，写的不对，你核实一下；

CREATE TABLE [dbo].[XF_Consume](
[ID_KEY] [bigint] IDENTITY(1,1) NOT NULL,
[Person_ID] [int] NOT NULL,
[System_No] [varchar](10) NOT NULL,
[Consume_Mode] [int] NOT NULL,
[Consume_Time] [datetime] NOT NULL,
[Consume_Fund] [money] NOT NULL,
[Before_Fund] [money] NOT NULL,
[Consume_Copy] [int] NULL,
[Consume_TimeLen] [float] NULL,
[Consume_Capacity] [float] NULL,
[Repast_No] [varchar](3) NULL,
[Ware_ID] [int] NULL,
[MOC_No] [varchar](6) NOT NULL,
[COM_No] [varchar](6) NULL,
[Card_Balance] [money] NOT NULL,
[DB_Balance] [money] NULL,
[Card_SmallAccount] [money] NULL,
[Data_Type] [tinyint] NOT NULL,
[Remark] [varchar](20) NULL,
[Download_Time] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
[ID_KEY] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

#9

Repast_No 这个是餐别，可以设置刷卡几次，现在刷卡一次的我解决了，但是如果可以刷卡2次或者3次，根绝这个次数来限制保留多少条数据，要怎么解决

#10

-- 留下最早的一条 
delete XF_Consume
where exists(select 1 from XF_Consume r 
                where XF_Consume.person_ID= r.person_ID 
                -- and  -- 这里加其他的条件
                and XF_Consume.Consume_Time > r.Consume_Time 
             )
go

#11

处理表重复记录(查询和删除)_整理贴4 
http://bbs.csdn.net/topics/240034273

#12

本帖最后由 roy_88 于 2016-07-27 14:17:34 编辑

DELETE  a  FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY Person_ID,Repast_No,CONVERT(varchar(100), Consume_Time, 23),System_No ORDER BY ID_KEY) AS RN FROM XF_Consume) AS a WHERE RN>1

#13

先结贴把，那个比较麻烦，不是保留固定的，而且去查询别的表，根据设置的次数来判断保留多少条！

#14

引用 13 楼 weimei521 的回复:

先结贴把，那个比较麻烦，不是保留固定的，而且去查询别的表，根据设置的次数来判断保留多少条！

用#12方法试试，应该是你要的效果

#15

declare @Repast_No_Count int   --次数
declare @Repast_No varchar(3) --餐别
declare @Use_Repast varchar(3000)--餐别次数设置
declare @index1   int     --初始位置
declare @index2 int
declare @LastIndex int  --最后一次出现的位置   
select @Use_Repast =Use_Repast from ST_CardType;
set @index1 =0;
set @index2 =0;
if @Use_Repast != ''  --不为空
Begin
  select @index1=charindex('|',@Use_Repast,@index1+1) from ST_CardType;  --获取'|'第一次出现的位置
  select @LastIndex =len(Use_Repast)-(CHARINDEX('|',REVERSE(Use_Repast))-1) from ST_CardType;--获取'|'最后一次出现的位置
  while @index1 <@LastIndex   --如果不是最后一条设置参数
  BEGIN  
    select @index2=charindex(';',@Use_Repast,@index2+1) from ST_CardType;  --获取';' 出现的位置
    select @Repast_No=SUBSTRING(@Use_Repast,@index1-3,3) from ST_CardType;
    select @Repast_No_Count=SUBSTRING(Use_Repast,@index1+1,@index2-@index1-1) from ST_CardType;
    if(@Repast_No_Count>0)
    BEGIN      
     delete   from XF_Consume
     where ID_Key in(
     select ID_Key from (
     select ROW_NUMBER() over(partition by System_No,person_ID,CONVERT(nvarchar,GETDATE(),23) order by ID_Key ) as number,*
     from XF_Consume
     where Repast_No=@Repast_No) b
     where number>@Repast_No_Count
     )
    END
    
    select @index1=charindex('|',@Use_Repast,@index1+1) from ST_CardType; 
  END
  
  select @Repast_No=SUBSTRING(@Use_Repast,@index1-3,3) from ST_CardType;
  select @Repast_No_Count=reverse(substring(reverse(Use_Repast),1,charindex('|',reverse(Use_Repast)) - 1)) from ST_CardType;
  if @Repast_No_Count >0
    BEGIN          
     delete  from XF_Consume
     where ID_Key in(
     select ID_Key from (
     select ROW_NUMBER() over(partition by System_No,person_ID,CONVERT(nvarchar,GETDATE(),23) order by ID_Key ) as number,*
     from XF_Consume
     where Repast_No=@Repast_No) b
     where number>@Repast_No_Count
     )
    End
End

我是这么做的！