先消除LEVEL1,LEVEL2,NAME三个列相同的记录 create table c as select distinct LEVEL1,LEVEL2,NAME from a 如果三个列相同的记录,下面的步骤不需要。然后消除LEVEL1,LEVEL2的记录,并且保留一个: delete c where name<>(select min(name) from c c1 where c.a = c1.a)
看不懂了, delete c where name<>(select min(name) from c c1 where c.a = c1.a) c.a c1.a 是什么东西?
把B表的约束删除 merge into b using(select * from a) on (a.LEVEL1=b.LEVEL1) when matched then update set name=a.name when not matched then insert into values(a.LEVEL1,a.name);
sorry! delete c where name<>(select min(name) from c c1 where c.level1 = c1.Level1 and c.level2=c1.level2)
welyngj(平平淡淡) merge是什么东西!!!
The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise a row is inserted using values from a separate subquery.
请问 welyngj(平平淡淡)delete a where exists (select level1,level2 from a group by level1,level2 having count(*)>1)这个的速度是不是比较慢?
delete c where name<>(select min(name) from c c1 where c.level1 = c1.Level1 and c.level2=c1.level2)什么意思?
删除A表重复记录 delete a where Rowid||LEVEL1||LEVEL2 in( select min(Rowid||LEVEL1||LEVEL2 from a group by serialno having count(*) > 1);
或者这样插入B表 insert into b select col1,col2,col3... from a where Rowid||col1||col1||col3 in( select min(Rowid)||col1||col1||col3 from a group by col1,col1,col3 having count(*) > 1);
一:在创建B时先不要建立主键,把A表的数据全部insert到B 二:利用以下语句删除表B中Level1与Level2同时相同的记录 delete from B t1 where rowid < (select max(rowid) from B t2 where t1.level1=t2.level1 and t1.level2=t2.level2) 然后在alter 表B 建立主键
create table c as
select distinct LEVEL1,LEVEL2,NAME from a
如果三个列相同的记录,下面的步骤不需要。然后消除LEVEL1,LEVEL2的记录,并且保留一个:
delete
c
where name<>(select min(name) from c c1 where c.a = c1.a)
delete
c
where name<>(select min(name) from c c1 where c.a = c1.a)
c.a c1.a 是什么东西?
merge into b
using(select * from a)
on (a.LEVEL1=b.LEVEL1)
when matched then
update set name=a.name
when not matched then
insert into values(a.LEVEL1,a.name);
delete
c
where name<>(select min(name) from c c1 where c.level1 = c1.Level1 and
c.level2=c1.level2)
where exists
(select level1,level2
from a
group by level1,level2
having count(*)>1)这个的速度是不是比较慢?
c
where name<>(select min(name) from c c1 where c.level1 = c1.Level1 and
c.level2=c1.level2)什么意思?
一个没有主键的表A中有若干个字段:COL1, COL2, COL3 …… COL100。
现在的问题是,要取得所有COL1、COL2、COL3不重复的记录,如果重复取出任意一条。
例如
COL1 COL2 COL3 COL4 ……
——————————————————
1 2 3 4
1 2 3 7
1 3 5 0
2 3 9 6
1 2 4 4取得
COL1 COL2 COL3 COL4 ……
——————————————————
1 2 3 4
1 3 5 0
2 3 9 6
1 2 4 4
即可。另外尽可能的不借助另一个临时表。
delete a
where Rowid||LEVEL1||LEVEL2 in(
select min(Rowid||LEVEL1||LEVEL2 from a
group by serialno
having count(*) > 1);
insert into b
select col1,col2,col3... from a
where Rowid||col1||col1||col3 in(
select min(Rowid)||col1||col1||col3 from a
group by col1,col1,col3
having count(*) > 1);
二:利用以下语句删除表B中Level1与Level2同时相同的记录
delete from B t1 where rowid < (select max(rowid) from B t2 where t1.level1=t2.level1 and t1.level2=t2.level2)
然后在alter 表B 建立主键