表加一列ID int identity(1,1) delete from tb where id in ( select min(id) from tb group by 数字列 )
declare @tb table([NAME] int) insert @tb select 1 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all select 4 union all select 4 union all select 4 union all select 5 union all select 5;DELETE T1 FROM @TB T1 INNER JOIN (SELECT TOP 1 [NAME] FROM @TB ORDER BY [NAME]) T2 ON T1.[NAME] = T2.[NAME]SELECT * FROM @TBDELETE T1 FROM @TB T1 INNER JOIN (SELECT TOP 1 [NAME] FROM @TB ORDER BY [NAME]) T2 ON T1.[NAME] = T2.[NAME]SELECT * FROM @TB/* (1 row(s) affected) NAME ----------- 2 2 3 3 3 4 4 4 5 5(10 row(s) affected)(2 row(s) affected)NAME ----------- 3 3 3 4 4 4 5 5(8 row(s) affected)
生成这样的id1 ,想怎么删就怎么删--> 测试数据: @tb if object_id('tb')is not null drop table tb go create table tb(nu int,id int identity) insert into tb select 1 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all select 4 union all select 4 union all select 4 union all select 4 go
alter table tb add id1 int go update tb set id1=(select count(*) from tb where nu=t.nu and id<=t.id) from tb tselect * from tbnu id id1 ----------- ----------- ----------- 1 1 1 2 2 1 2 3 2 3 4 1 3 5 2 3 6 3 4 7 1 4 8 2 4 9 3 4 10 4(10 行受影响)
请问ls,你的t.nu与t.id是什么?
t.nu 就是你的 那个列 t.id是建表的时候,创建的自增列
SQL2000的话, 必须加个标识列-- ============================================= -- Author: T.O.P -- Create date: 20091127 -- Version: SQL SERVER 2000 -- ============================================= if object_id('[TB]') is not null drop table [TB] go create table [TB]([A] int) insert [TB] select 1 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all select 4 union all select 4 union all select 4 union all select 4alter table TB add ID int identityDELETE A FROM TB A WHERE NOT EXISTS(SELECT 1 FROM TB WHERE A.A = A AND A.ID>ID)SELECT A FROM TBdrop table tb --测试结果: /* A ----------- 2 3 3 4 4 4(所影响的行数为 6 行)*/
表加一列ID int identity(1,1)
delete from tb where id in
(
select min(id)
from tb
group by 数字列
)
declare @tb table([NAME] int)
insert @tb
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 4 union all
select 5 union all
select 5;DELETE T1
FROM @TB T1 INNER JOIN (SELECT TOP 1 [NAME] FROM @TB ORDER BY [NAME]) T2 ON T1.[NAME] = T2.[NAME]SELECT * FROM @TBDELETE T1
FROM @TB T1 INNER JOIN (SELECT TOP 1 [NAME] FROM @TB ORDER BY [NAME]) T2 ON T1.[NAME] = T2.[NAME]SELECT * FROM @TB/*
(1 row(s) affected)
NAME
-----------
2
2
3
3
3
4
4
4
5
5(10 row(s) affected)(2 row(s) affected)NAME
-----------
3
3
3
4
4
4
5
5(8 row(s) affected)
if object_id('tb')is not null drop table tb
go
create table tb(nu int,id int identity)
insert into tb
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 4 union all
select 4
go
alter table tb add id1 int
go
update tb
set id1=(select count(*) from tb where nu=t.nu and id<=t.id)
from tb tselect * from tbnu id id1
----------- ----------- -----------
1 1 1
2 2 1
2 3 2
3 4 1
3 5 2
3 6 3
4 7 1
4 8 2
4 9 3
4 10 4(10 行受影响)
t.id是建表的时候,创建的自增列
SQL2000的话, 必须加个标识列-- =============================================
-- Author: T.O.P
-- Create date: 20091127
-- Version: SQL SERVER 2000
-- =============================================
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] int)
insert [TB]
select 1 union all
select 2 union all
select 2 union all
select 3 union all
select 3 union all
select 3 union all
select 4 union all
select 4 union all
select 4 union all
select 4alter table TB add ID int identityDELETE A
FROM TB A
WHERE NOT EXISTS(SELECT 1 FROM TB WHERE A.A = A AND A.ID>ID)SELECT A FROM TBdrop table tb
--测试结果:
/*
A
-----------
2
3
3
4
4
4(所影响的行数为 6 行)*/