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

[weimei521]

现在根据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)
为什么会报错呢

[道素]

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

[薛定谔的DBA]

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

[卖水果的net]

delete mytable t where exists(select 1from mytable r where t.ID= r.ID and t.time > r.time ) sql server 可以参考这种语法。

[weimei521]

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

[中国风]

引用 13 楼 weimei521 的回复:

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

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

[weimei521]

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 我是这么做的！

[weimei521]

引用 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 只写到这里，就报错了，语法错误

[卖水果的net]

引用 4 楼 weimei521 的回复:

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

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

[weimei521]

引用 5 楼 wmxcn2000 的回复:

[quote=引用 4 楼 weimei521 的回复:] delete XF_Consume A where 只写到这里，就报错了，语法错误

把你的表结构贴一下， create table 形式的； 再给出一些测试数据；[/quote] 表： 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);

[卖水果的net]

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

[weimei521]

引用 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

[weimei521]

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

[卖水果的net]

-- 留下最早的一条 
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

[中国风]

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

[中国风]

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