<1>
Q:一个表, 包含完全相同重复数据若干,怎么删除多余记录:
A:
ORACLE的ROWID AND ROWNUM
1.ORACLE中是非常容易解决的,用ROWID就行了。
delect from mytable x
where rowid <> (select max(rowid)
from mytable y
where y.field1 = x.field1
and y.field2 = x.field2
.....
);
2.存储过程:
create procedure sp_cleardata
as
begin
begin tran
insert into table #b select distinct * from table_a
delete from table_a
insert into table_a select * from #b
commit tran
end
3.可以用游标:
给你个例子
drop table userinfo
create table userinfo
(
username char(10),
sex char(4),
phone char(10)
)
insert userinfo select 'aaa','男','12300' union all
select 'bbb','男','12300' union all
select 'aaa','男','12300' union all
select 'bbb','男','12300' union all
select 'aaa','男','12300' union all
select 'ccc','男','12300' union all
select 'aaa','男','12300'
--(1)定义
declare delcur cursor
for select count(*) as num ,username from userinfo
group by username
--(2)打开
open delcur
--(3)操作
declare @num int,@name varchar(10)
fetch next from delcur into @num,@name
while(@@fetch_status=0)
begin
declare second cursor for select * from userinfo where username=@name
open second
fetch next from second
while(@num >1)
begin
delete from userinfo where current of second
set @num=@num-1
fetch next from second
end
close second
deallocate second
fetch next from delcur into @num,@name
end
close delcur
deallocate delcur
<2>
Q:如何在一表中用一SQL语句查询出两条完全相同的记录:
A:
select col1,col2,col3...
from t
group by col1,col2,col3...
having count(*)>1
<3>
Q:删除一个表中所有与另一个表完全相同的记录
A:
delete from t1 where exists(select 1 from t2 b where t1.f1=b.f1 and t1.f2=b.f2)
delete 表1
from 表1 a,表2 b where a.col1=b.col1 and a.col2=b.col2 and ...--所有关连字段
delete from 表1 where checksum(*) in(select checksum(*) from 表2)
checksum生成一行数据的hash值
分组算法1:
create table #table1
(hzh char(2),
kwh char(2),
zl datetime,
spmc char (4))
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb','2001-5-6 00:01:001','1111')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb','2001-5-6 00:02:002','2222')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc','2001-5-6 00:03:003','3333')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc','2001-5-6 00:04:004','4444')
insert into #table1 (hzh,kwh,zl,spmc) values('ab','bb','2001-5-6 00:05:005','5555')
select #table1.hzh, #table1.kwh, #table1.zl, #table1.spmc
from #table1,
(
select hzh,kwh,max(zl) as zl
from #table1
group by hzh,kwh) t
where #table1.zl = t.zl
select * from #table1
drop table #table1
分组算法2:
create table #table1
(hzh char(2),
kwh char(2),
zl int,
spmc char (4))
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb',3,'1111')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb',4,'2222')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc',5,'3333')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc',6,'4444')
insert into #table1 (hzh,kwh,zl,spmc) values('ab','bb',7,'5555')
select #table1.hzh, #table1.kwh, #table1.zl, #table1.spmc
from #table1,
(
select hzh,kwh,max(zl) as zl
from #table1
group by hzh,kwh) t
where #table1.zl = t.zl and #table1.hzh=t.hzh and #table1.kwh=t.kwh
select * from #table1
drop table #table1
解决了代码和名称对应的问题,还有case when 语句得使用和别名得好处
=======================================
SELECT (
case (select distinct T_TRAIN.Fld_Depart_Train_Code from T_TRAIN where T_TRAIN.Fld_Train_Code=T_TRAIN_SEGMENT_RELATION.Fld_Train_Code)
when null then T_TRAIN_SEGMENT_RELATION.Fld_Train_Code
else (select distinct T_TRAIN.Fld_Depart_Train_Code from T_TRAIN where T_TRAIN.Fld_Train_Code=T_TRAIN_SEGMENT_RELATION.Fld_Train_Code)
end ) as Train_Code,
T_TRAIN_SEGMENT_RELATION.Fld_Segment_Code,
d1.Fld_Station_Name,
d2.Fld_Station_Name,
T_TRAIN_SEGMENT_RELATION.Fld_Segment_Distance,
T_TRAIN_SEGMENT_RELATION.Fld_Engine_Dep_Name,
T_TRAIN_SEGMENT_RELATION.Fld_Owner_Dep_Name,
(select distinct Fld_Engine_Type_Name from T_ENGINE_DEP where T_TRAIN_SEGMENT_RELATION.Fld_Engine_Type=T_ENGINE_DEP.Fld_Engine_Type) as Fld_Engine_Type
FROM T_TRAIN_SEGMENT_RELATION , T_STATION d1 , T_STATION d2
where T_TRAIN_SEGMENT_RELATION.Fld_Segment_Start_Station_Code=d1.Fld_Station_Code and
T_TRAIN_SEGMENT_RELATION.Fld_Segment_End_Station_Code=d2.Fld_Station_Code ;
============================================
对游标进行更新得操作:
create procedure collection_train as
declare @Fld_Segment_Code Char(10)
declare @Fld_Segment_Start_Station Char(20),@Fld_Segment_End_Station Char(20)
declare @Start_Fld_Station_Code Char(4),@End_Fld_Station_Code Char(4)
declare @i int
select @i=0
declare cur_result cursor for
Select Fld_Segment_Code,Fld_Segment_Start_Station,Fld_Segment_End_Station from T_TRAIN_SEGMENT_RELATION
for update of Fld_No
open cur_result
fetch cur_result into @Fld_Segment_Code,@Fld_Segment_Start_Station,@Fld_Segment_End_Station
while @@sqlstatus = 0
begin
select @i=@i+1
update T_TRAIN_SEGMENT_RELATION set Fld_No=@i
where current of cur_result
fetch cur_result into @Fld_Segment_Code,@Fld_Segment_Start_Station,@Fld_Segment_End_Station
end
close cur_result
deallocate cursor cur_result ;
Q:一个表, 包含完全相同重复数据若干,怎么删除多余记录:
A:
ORACLE的ROWID AND ROWNUM
1.ORACLE中是非常容易解决的,用ROWID就行了。
delect from mytable x
where rowid <> (select max(rowid)
from mytable y
where y.field1 = x.field1
and y.field2 = x.field2
.....
);
2.存储过程:
create procedure sp_cleardata
as
begin
begin tran
insert into table #b select distinct * from table_a
delete from table_a
insert into table_a select * from #b
commit tran
end
3.可以用游标:
给你个例子
drop table userinfo
create table userinfo
(
username char(10),
sex char(4),
phone char(10)
)
insert userinfo select 'aaa','男','12300' union all
select 'bbb','男','12300' union all
select 'aaa','男','12300' union all
select 'bbb','男','12300' union all
select 'aaa','男','12300' union all
select 'ccc','男','12300' union all
select 'aaa','男','12300'
--(1)定义
declare delcur cursor
for select count(*) as num ,username from userinfo
group by username
--(2)打开
open delcur
--(3)操作
declare @num int,@name varchar(10)
fetch next from delcur into @num,@name
while(@@fetch_status=0)
begin
declare second cursor for select * from userinfo where username=@name
open second
fetch next from second
while(@num >1)
begin
delete from userinfo where current of second
set @num=@num-1
fetch next from second
end
close second
deallocate second
fetch next from delcur into @num,@name
end
close delcur
deallocate delcur
<2>
Q:如何在一表中用一SQL语句查询出两条完全相同的记录:
A:
select col1,col2,col3...
from t
group by col1,col2,col3...
having count(*)>1
<3>
Q:删除一个表中所有与另一个表完全相同的记录
A:
delete from t1 where exists(select 1 from t2 b where t1.f1=b.f1 and t1.f2=b.f2)
delete 表1
from 表1 a,表2 b where a.col1=b.col1 and a.col2=b.col2 and ...--所有关连字段
delete from 表1 where checksum(*) in(select checksum(*) from 表2)
checksum生成一行数据的hash值
分组算法1:
create table #table1
(hzh char(2),
kwh char(2),
zl datetime,
spmc char (4))
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb','2001-5-6 00:01:001','1111')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb','2001-5-6 00:02:002','2222')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc','2001-5-6 00:03:003','3333')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc','2001-5-6 00:04:004','4444')
insert into #table1 (hzh,kwh,zl,spmc) values('ab','bb','2001-5-6 00:05:005','5555')
select #table1.hzh, #table1.kwh, #table1.zl, #table1.spmc
from #table1,
(
select hzh,kwh,max(zl) as zl
from #table1
group by hzh,kwh) t
where #table1.zl = t.zl
select * from #table1
drop table #table1
分组算法2:
create table #table1
(hzh char(2),
kwh char(2),
zl int,
spmc char (4))
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb',3,'1111')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bb',4,'2222')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc',5,'3333')
insert into #table1 (hzh,kwh,zl,spmc) values('aa','bc',6,'4444')
insert into #table1 (hzh,kwh,zl,spmc) values('ab','bb',7,'5555')
select #table1.hzh, #table1.kwh, #table1.zl, #table1.spmc
from #table1,
(
select hzh,kwh,max(zl) as zl
from #table1
group by hzh,kwh) t
where #table1.zl = t.zl and #table1.hzh=t.hzh and #table1.kwh=t.kwh
select * from #table1
drop table #table1
解决了代码和名称对应的问题,还有case when 语句得使用和别名得好处
=======================================
SELECT (
case (select distinct T_TRAIN.Fld_Depart_Train_Code from T_TRAIN where T_TRAIN.Fld_Train_Code=T_TRAIN_SEGMENT_RELATION.Fld_Train_Code)
when null then T_TRAIN_SEGMENT_RELATION.Fld_Train_Code
else (select distinct T_TRAIN.Fld_Depart_Train_Code from T_TRAIN where T_TRAIN.Fld_Train_Code=T_TRAIN_SEGMENT_RELATION.Fld_Train_Code)
end ) as Train_Code,
T_TRAIN_SEGMENT_RELATION.Fld_Segment_Code,
d1.Fld_Station_Name,
d2.Fld_Station_Name,
T_TRAIN_SEGMENT_RELATION.Fld_Segment_Distance,
T_TRAIN_SEGMENT_RELATION.Fld_Engine_Dep_Name,
T_TRAIN_SEGMENT_RELATION.Fld_Owner_Dep_Name,
(select distinct Fld_Engine_Type_Name from T_ENGINE_DEP where T_TRAIN_SEGMENT_RELATION.Fld_Engine_Type=T_ENGINE_DEP.Fld_Engine_Type) as Fld_Engine_Type
FROM T_TRAIN_SEGMENT_RELATION , T_STATION d1 , T_STATION d2
where T_TRAIN_SEGMENT_RELATION.Fld_Segment_Start_Station_Code=d1.Fld_Station_Code and
T_TRAIN_SEGMENT_RELATION.Fld_Segment_End_Station_Code=d2.Fld_Station_Code ;
============================================
对游标进行更新得操作:
create procedure collection_train as
declare @Fld_Segment_Code Char(10)
declare @Fld_Segment_Start_Station Char(20),@Fld_Segment_End_Station Char(20)
declare @Start_Fld_Station_Code Char(4),@End_Fld_Station_Code Char(4)
declare @i int
select @i=0
declare cur_result cursor for
Select Fld_Segment_Code,Fld_Segment_Start_Station,Fld_Segment_End_Station from T_TRAIN_SEGMENT_RELATION
for update of Fld_No
open cur_result
fetch cur_result into @Fld_Segment_Code,@Fld_Segment_Start_Station,@Fld_Segment_End_Station
while @@sqlstatus = 0
begin
select @i=@i+1
update T_TRAIN_SEGMENT_RELATION set Fld_No=@i
where current of cur_result
fetch cur_result into @Fld_Segment_Code,@Fld_Segment_Start_Station,@Fld_Segment_End_Station
end
close cur_result
deallocate cursor cur_result ;