删除表中重复数据，只保留ID最小的数据,该怎么解决

删除表中重复数据，只保留ID最小的数据

SQL code

这是表的结构CREATE TABLE [dbo].[Table_1](    [ID] [int] IDENTITY(1,1) NOT NULL,    [GDSCode] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [ExHotelCode] [int] NULL,    [ExRoomCode] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [ExRatePlaneCode] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [HotelID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [RoomID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,    [RatePlaneID] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED (    [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]这是删除重复数据的SQL语句：DELETE FROM Table_1 WHERE --c.ID=2(GDSCode, a.ExHotelCode, a.ExRoomCode,a.ExRatePlaneCode,a.HotelID, a.RoomID,a.RatePlaneID) IN (SELECT c.GDSCode, c.ExHotelCode, c.ExRoomCode,c.ExRatePlaneCode, c.HotelID, c.RoomID,c.RatePlaneID FROM Table_1 c GROUP BY c.GDSCode,c.ExHotelCode, c.ExRoomCode,c.ExRatePlaneCode, c.HotelID, c.RoomID,c.RatePlaneID HAVING COUNT(*)>1)AND a.ID NOT IN(SELECT MIN(ID) FROM Table_1 b GROUP BY b.GDSCode, b.ExHotelCode, b.ExRoomCode,b.ExRatePlaneCode, b.HotelID, b.RoomID,b.RatePlaneID HAVING COUNT(*)>1)

执行上面的SQL语句，报错：
消息 102，级别 15，状态 1，第 2 行
',' 附近有语法错误。
消息 156，级别 15，状态 1，第 6 行
关键字 'AND' 附近有语法错误。

哪位帮忙看看这是怎么回事啊？

[解决办法]
Sql server啥时候也支持in的多字段了？
[解决办法]

探讨

Sql server啥时候也支持in的多字段了？

[解决办法]
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

可以去看下
[解决办法]

SQL code

delete a from  TB a where  exists(select 1 from TB where Name=a.Name and ID<a.ID)--只保留一条记录
[解决办法]
delete a from Table_1 a  
where  exists(select 1 from Table_1 where Name=a.Name and ID>a.ID)


[解决办法]


处理表重复记录(查询和删除)_整理贴4

ORACLE中不熟悉... 

 

查看更多 下一篇