--哦,没看清楚题目意思。如果要保留。那就: delete NUm_table a where not exists(select 1 from NUm_table where fTable_Num=a.fTable_Num and 主键字段<a.主键字段)
他们那是SQL2005的语法,你的不支持。
表名:Table_Code 字段名: cf_code 主键名: zjDELETE Table_Code a WHERE NOT EXISTS (SELECT 1 FROM Table_Code WHERE cf_code = a.cf_code AND zj < a.zj)
;with fuckyou as (select px=row_number()over(partition by fTable_Num order by (select 1), * from Num_TABLE) ) delete fuckyou where px!=1
大哥,能用SQL2000的语法写吗?
delete from deletetest where exists (select 1 from deletetest a where deletetest.col1=a.col1 and deletetest.id>a.id)
哦,不好意思,手误。 delete NUm_table from NUm_table a where not exists(select 1 from NUm_table where fTable_Num=a.fTable_Num and 主键字段<a.主键字段)
即:DELETE Table_Code from Table_Code a WHERE NOT EXISTS (SELECT 1 FROM Table_Code WHERE cf_code = a.cf_code AND zj < a.zj)
最好先对表增加一个ID字段,该字段由小到大排序 然后 delete from Num_Table a where exists ( select 1 from Num_Table where a.fTable_Num=fTable_Num and a.id>id) 删除记录,即读取一条记录判断ID比它小并且有跟他fTable_Num字段一样的记录则删除它 否则不删除
--怎么会把只有一条记录的删掉呢?--> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (id int,name varchar(2)) insert into [tb] select 1,'aa' union all select 2,'aa' union all select 3,'bb'delete tb from tb a where not exists(select 1 from tb where id<a.id)select * from [tb] --结果: id name ----------- ---- 2 aa 3 bb
我测试数据出来,是删除了不重复的数据 - -! 初始数据主键 重复字段 1 1 2 2 3 2 4 3 5 3 6 4执行命令--> DELETE Table_Code from Table_Code a WHERE NOT EXISTS (SELECT 1 FROM Table_Code WHERE cf_code = a.cf_code AND zj < a.zj)测试结果--> 主键 重复字段 3 2 5 3
(
SELECT rn = ROW_NUMBER()OVER(PARTITION BY fTable_Num ORDER BY GETDATE()), *
FROM Num_Table
)
DELETE t
WHERE rn>1
as
(select px=row_number()over(partition by fTable_Num order by (select 1),
*
from Num_TABLE)
)
delete happy where px!=1
(select fTable_Num from NUm_table group by fTable_Num having count(*)>1)
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ';' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 3
'ROW_NUMBER' 不是可以识别的 函数名。
和上面一样 语法错误服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ';' 附近有语法错误。
服务器: 消息 195,级别 15,状态 1,行 3
'ROW_NUMBER' 不是可以识别的 函数名。
--哦,没看清楚题目意思。如果要保留。那就:
delete NUm_table a where not exists(select 1 from NUm_table
where fTable_Num=a.fTable_Num and 主键字段<a.主键字段)
WHERE NOT EXISTS
(SELECT 1
FROM Table_Code
WHERE cf_code = a.cf_code AND zj < a.zj)
;with fuckyou
as
(select px=row_number()over(partition by fTable_Num order by (select 1),
*
from Num_TABLE)
)
delete fuckyou where px!=1
大哥,能用SQL2000的语法写吗?
delete NUm_table from NUm_table a where not exists(select 1 from NUm_table where fTable_Num=a.fTable_Num and 主键字段<a.主键字段)
WHERE NOT EXISTS
(SELECT 1
FROM Table_Code
WHERE cf_code = a.cf_code AND zj < a.zj)
然后 delete from Num_Table a where exists ( select 1 from Num_Table where a.fTable_Num=fTable_Num and a.id>id)
删除记录,即读取一条记录判断ID比它小并且有跟他fTable_Num字段一样的记录则删除它 否则不删除
--怎么会把只有一条记录的删掉呢?--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,name varchar(2))
insert into [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'bb'delete tb from tb a where not exists(select 1 from tb where id<a.id)select * from [tb]
--结果:
id name
----------- ----
2 aa
3 bb
我测试数据出来,是删除了不重复的数据 - -!
初始数据主键 重复字段
1 1
2 2
3 2
4 3
5 3
6 4执行命令-->
DELETE Table_Code from Table_Code a
WHERE NOT EXISTS
(SELECT 1
FROM Table_Code
WHERE cf_code = a.cf_code AND zj < a.zj)测试结果-->
主键 重复字段
3 2
5 3