这个问题的解决办法不止一个 :
先建一个表
CREATE TABLE Products (
ProductID int,
ProductName varchar(40),
Unit char(2),
UnitPrice number
)
然后添加一些ProductName 和Unit 都相同的数据。
1、 接下来先展示只需要一个sql就解决问题的方法(限Oracle)
delete from products where productid in
(
select distinct d.productid from
(select c.*,rownum as n2 from Products c)d,
(select a.*,rownum as n1 from Products a)b
where b.unit=d.unit and b.productname=d.productname and b.n1<d.n2
)
在sqlserver中因为没有rownum ,所以只能采用一种变通的手法:
alter table products
add key_col int not null identity(1,1)
delete from products
where productid in
(
select distrinct productid from products as t2
where t2.productname=products.productname and t2.unit=products.unit
and t2.key_col<products.key_col
)
alter table products
drop column key_col
明显我们构造了一个记录行号的列,模仿Oracle的rownum,用完之后删掉。
这种方法有很大的弊端就是效率很低。
2、那么接下来我们演示一个更聪明的办法,在sqlserver中利用如下的触发器
CREATE TRIGGER TR_unique_Value
ON tableA
FOR insert, update
AS
IF EXISTS(
SELECT Value FROM tableA A
WHERE Value IS NOT NULL
GROUP BY Value
HAVING COUNT(*) > 1)
BEGIN
ROLLBACK TRAN;
RAISERROR('操作导致 Value 不唯一', 16, 1);
END
GO
我们可以建立一个与products表结构相同的表products_temp,在这个表上建立上述的触发器,然后从products表向新表导入数据,由于重复行被过滤,新表中已经没有重复的行了,然后我们将products表清空再将新表中的数据导回就可以了。
在oracle中也可以编写类似的触发器
CREATE OR REPLACE TRIGGER remove_pro_repeate
BEFORE INSERT ON Products_temp
FOR EACH ROW
DECLARE
BEGIN
delete from products a
where a.productname=:new.productname
and a.unit=:new.unit
and a.productid<>:new.productid;
END;
我们只需从原表往新表中导入数据
insert into Products_temp select * from products
与sqlserver中不同的是,我们的新表成为了原表的完整备份,而原表已经没有了重复行。
3、当然我们可以写一个存储过程来遍历表,逐行寻找相同的行,然后删除之。
先建一个表
CREATE TABLE Products (
ProductID int,
ProductName varchar(40),
Unit char(2),
UnitPrice number
)
然后添加一些ProductName 和Unit 都相同的数据。
1、 接下来先展示只需要一个sql就解决问题的方法(限Oracle)
delete from products where productid in
(
select distinct d.productid from
(select c.*,rownum as n2 from Products c)d,
(select a.*,rownum as n1 from Products a)b
where b.unit=d.unit and b.productname=d.productname and b.n1<d.n2
)
在sqlserver中因为没有rownum ,所以只能采用一种变通的手法:
alter table products
add key_col int not null identity(1,1)
delete from products
where productid in
(
select distrinct productid from products as t2
where t2.productname=products.productname and t2.unit=products.unit
and t2.key_col<products.key_col
)
alter table products
drop column key_col
明显我们构造了一个记录行号的列,模仿Oracle的rownum,用完之后删掉。
这种方法有很大的弊端就是效率很低。
2、那么接下来我们演示一个更聪明的办法,在sqlserver中利用如下的触发器
CREATE TRIGGER TR_unique_Value
ON tableA
FOR insert, update
AS
IF EXISTS(
SELECT Value FROM tableA A
WHERE Value IS NOT NULL
GROUP BY Value
HAVING COUNT(*) > 1)
BEGIN
ROLLBACK TRAN;
RAISERROR('操作导致 Value 不唯一', 16, 1);
END
GO
我们可以建立一个与products表结构相同的表products_temp,在这个表上建立上述的触发器,然后从products表向新表导入数据,由于重复行被过滤,新表中已经没有重复的行了,然后我们将products表清空再将新表中的数据导回就可以了。
在oracle中也可以编写类似的触发器
CREATE OR REPLACE TRIGGER remove_pro_repeate
BEFORE INSERT ON Products_temp
FOR EACH ROW
DECLARE
BEGIN
delete from products a
where a.productname=:new.productname
and a.unit=:new.unit
and a.productid<>:new.productid;
END;
我们只需从原表往新表中导入数据
insert into Products_temp select * from products
与sqlserver中不同的是,我们的新表成为了原表的完整备份,而原表已经没有了重复行。
3、当然我们可以写一个存储过程来遍历表,逐行寻找相同的行,然后删除之。