34,591
社区成员
发帖
与我相关
我的任务
分享
create table tba
(
a varchar(8),
b varchar(8),
c decimal(10,2),
d int,
e int
)
go
insert into tba
select '0001', '铅笔', 0.5, 100, 50 union all
select '0001', '铅笔', 0.4, 100, 40 union all
select '0001', '铅笔', 0.4, 100, 40 union all
select '0002', '钢笔', 3.9, 100, 390 union all
select '0002', '钢笔', 4.2, 100, 420 union all
select '0002', '钢笔', 3.9, 100, 390
go
select distinct * into mytemp from tba
select * from mytemp
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-25 09:01:02
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] varchar(4),[B] varchar(4),[C] numeric(2,1),[D] int,[E] int)
insert [tb]
select '0001','铅笔',0.5,100,50 union all
select '0001','铅笔',0.4,100,40 union all
select '0001','铅笔',0.4,100,40 union all
select '0002','钢笔',3.9,100,390 union all
select '0002','钢笔',4.2,100,420 union all
select '0002','钢笔',3.9,100,390
--------------开始查询--------------------------
delete
t
from
(select id=row_number()over(order by getdate()),* from tb) t
where
exists(select 1 from (select id=row_number()over(order by getdate()),* from tb)m where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e and id>t.id)
select * from tb
----------------结果----------------------------
/* A B C D E
---- ---- --------------------------------------- ----------- -----------
0001 铅笔 0.5 100 50
0001 铅笔 0.4 100 40
0002 钢笔 4.2 100 420
0002 钢笔 3.9 100 390
(4 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (A nvarchar(8),B nvarchar(4),C numeric(2,1),D int,E int)
insert into [tb]
select '0001',N'铅笔',0.5,100,50 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0002',N'钢笔',3.9,100,390 union all
select '0002',N'钢笔',4.2,100,420 union all
select '0002',N'钢笔',3.9,100,390
select A,B,C,D,E from
(
select *,row_number() over(partition by B order by C) rank from [tb]
)TT
where rank<3
A B C D E
-------- ---- --------------------------------------- ----------- -----------
0002 钢笔 3.9 100 390
0002 钢笔 3.9 100 390
0001 铅笔 0.4 100 40
0001 铅笔 0.4 100 40
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (A nvarchar(8),B nvarchar(4),C numeric(2,1),D int,E int)
insert into [tb]
select '0001',N'铅笔',0.5,100,50 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0002',N'钢笔',3.9,100,390 union all
select '0002',N'钢笔',4.2,100,420 union all
select '0002',N'钢笔',3.9,100,390
select distinct * into #tr from tb
select * from #tr
A B C D E
-------- ---- --------------------------------------- ----------- -----------
0001 铅笔 0.4 100 40
0001 铅笔 0.5 100 50
0002 钢笔 3.9 100 390
0002 钢笔 4.2 100 420
(4 行受影响)
(1 行受影响)
--> Title : Generating test data [tb]
--> Author : wufeng4552
--> Date : 2009-11-25 08:56:43
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (A nvarchar(8),B nvarchar(4),C numeric(2,1),D int,E int)
insert into [tb]
select '0001',N'铅笔',0.5,100,50 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0002',N'钢笔',3.9,100,390 union all
select '0002',N'钢笔',4.2,100,420 union all
select '0002',N'钢笔',3.9,100,390
alter table tb
add id int identity
delete t from [tb] t
where exists(select 1 from tb where a=t.a and b=t.b and c=t.c and d=t.d and e=t.e
and id>t.id)
alter table tb
drop column ID
go
select * from tb
/*
A B C D E
-------- ---- --------------------------------------- ----------- -----------
0001 铅笔 0.5 100 50
0001 铅笔 0.4 100 40
0002 钢笔 4.2 100 420
0002 钢笔 3.9 100 390
(4 個資料列受到影響)
*/
SELECT DISTINCT A,B,C,D,E INTO tmp FROM tb;
DROP TABLE tb;
EXEC sp_rename 'tmp','tb','OBJECT';
然后向tb表重建所有对象.如索引,触发器等.
SELECT DISTINCT A,B,C,D,E INTO tmp FROM tb;
TRUNCATE TABLE tb;
INSERT tb SELECT A,B,C,D,E FROM tmp;
DROP TABLE tmp;
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (A nvarchar(8),B nvarchar(4),C numeric(2,1),D int,E int)
insert into [tb]
select '0001',N'铅笔',0.5,100,50 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0001',N'铅笔',0.4,100,40 union all
select '0002',N'钢笔',3.9,100,390 union all
select '0002',N'钢笔',4.2,100,420 union all
select '0002',N'钢笔',3.9,100,390
select distinct * into aa from tb
select * from aa
这样不就可以吗?
select distinct * from tb