查询及删除重复记录的SQL语句
查询及删除重复记录的SQL语句
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

 

（1）通过建立临时表来实现

SQL>create table temp_emp as (select distinct * from employee)

SQL> truncate table employee; (清空employee表的数据）

SQL> insert into employee select * from temp_emp; (再将临时表里的内容插回来）

 

( 2）通过唯一rowid实现删除重复记录.在Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记 录是在Oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最 大或最小rowid的就可以了，其余全部删除。

SQL>delete from employee e2 where rowid not in (

        select max(e1.rowid) from employee e1 where

        e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这里用min(rowid)也可以。

SQL>delete from employee e2 where rowid <(

        select max(e1.rowid) from employee e1 where
        e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and

                  e1.salary=e2.salary);

（3）也是通过rowid，但效率更高。

SQL>delete from employee where rowid not in (

        select max(t1.rowid) from employee t1 group by

         t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。

【转】以下帖子总结了4种方法：
http://bbs.51testing.com/viewthread.php?tid=104648&extra=&page=1

第一种：利用 Description 对象
For intLoop = 1 to N
    strText=DataTable.Value(...)
    Set LinkDesc = Description.Create()
    LinkDesc ("Text").Value = strText
    Browser("").Page("").Link(LinkDesc).Click
    DataTable.GetSheet("").SetNextRow
Next
第二种：描述性编程
For intLoop = 1 to N
    strText=DataTable.Value(...)
    Browser("").Page("").Link("text:=" & strText).Click
    DataTable.GetSheet("").SetNextRow
Next
第三种：利用SetToproperty方法（以sina为例）
Step1:录制
Browser("新浪首页").Page("新浪首页").Link("墨尔本北航热招营销硕士").click
此时对象库如图1

Step2：欲点击其他新闻（等于是用同一个录制好的link对象，只是改变其text属性，然后click的时候就会等于click不同的link，因为link（“”）括号里的只是对象名，可以在object repository里改，并不是任何属性）
Browser("新浪首页").Page("新浪首页").Link("北大私募基金/企业上市").Click
'点击北大...新闻
Browser("新浪首页").Page("新浪首页").Link("北大私募基金/企业上市").SetTOProperty"text","清华深圳创业板/私募班"
Browser("新浪首页").Page("新浪首页").Link("北大私募基金/企业上市").Click
'点击清华...新闻
Setp3：写循环语句
For intLoop = 1 to N
    strText=DataTable.Value(...)
    Browser("新浪首页").Page("新浪首页").Link("北大私募基金/企业上市").SetTOProperty"text","strText"
    DataTable.GetSheet("").SetNextRow
Next
第四种：利用参数化对象的Text属性实现（这个也是新知道的，object repository里的属性居然也能参数化）
Step1:录制
Browser("新浪首页").Page("新浪首页").Link("墨尔本北航热招营销硕士").click
此时对象库如图1


Step2：参数化Text属性，见下图2，步骤省略

Step3：写循环或者设置Action run call properties为run on all rows即可

 

 

其中，第三种方法在另一个帖子里我找到了实例，并且实践了一下（下面的例子和上面4个方法还有一个不同是，上面4个方法link的text属性从datatable中获取，这里是自动从页面获取）：
http://bbs.51testing.com/thread-20178-1-4.html

Browser("Browser").Page("Page").Sync （这个是blank页面的对象，可以用添加到对象库来添加）
Browser("Browser").Navigate("http://bbs.51testing.com/default.php")
Browser("51Testing软件测试论坛 软件测试 |").Page("51Testing软件测试论坛 软件测试 |").Sync

Set tags=Browser("51Testing软件测试论坛 软件测试 |").Page("51Testing软件测试论坛 软件测试 |").Object.links
Dim i,j, arr()
i=0
For Each element in tags
    If Ucase(element.tagName)="A" and left(element.InnerText,1)="[" Then
ReDim Preserve arr(i+1)
     arr(i)=element.InnerText
    i=i+1
end if
Next

For j=0 to i
Browser("51Testing软件测试论坛 软件测试 |").Page("51Testing软件测试论坛 软件测试 |").Link("[软件测试新手上路]").SetTOProperty "Text",arr(j)
            Browser("51Testing软件测试论坛 软件测试 |").Page("51Testing软件测试论坛 软件测试 |").Link("[软件测试新手上路]").Click
Browser("51Testing软件测试论坛 软件测试 |").Back
Next

练习代码路径：Test\ClickURL

查询及删除重复记录的SQL语句
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people 
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)

注:rowid为oracle自带不用该.....

3、查找表中多余的重复记录（多个字段） 
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1

(三)
方法一
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0 方法二
＂重复记录＂有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
　　1、对于第一种重复，比较容易解决，使用
select distinct * from tableName
　　就可以得到无重复记录的结果集。
　　如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
　　发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
　　2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
　　假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
　　最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
(四)
查询重复
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1
) 

呵呵，本人用中文写一篇日记。每天都好累，上班真是辛苦呀，今天又被我发现了几个bug，挺有成就感的，因为这个项目已经测了很久，bug相对比较少，不过一直去找开发说修改的事，搞得我都不好意思了，才进公司应该低调点，不过leader和bob都让我找，我又不能不找，只好硬着头皮去，还好开发态度一直还算好，不过今天他说了这样一句话让我有些不爽，他说“这个东西给你说也不明白……”，哼，有什么不明白的，明明是可以做好的东西，就是懒，我就不相信了能有多高深。不过这也验证了老师说的一句话，要想让开发乖乖地修改bug，就得水平比他们高，呵呵。我要努力，不能让他们小瞧了测试

I will set to work tomorrow. I am tingling with excitement. I will graduate from 51testing, and I am smarting because of my separation from my classmates. I want to put my knowledge and experience to use in this position. In order to achieve this goal, I just want to work step by step.

This is my first working day. As a whole it’s a cheerful day. I read the documents of product all day. The documents are English , so I feel a little difficulty. Between my colleagues send the mail in English, and the entire English learning environment enriched. I hope the level of my English will improve.  

I will take an examination tomorrow, so call it a night. Good night!

I suddenly know nothing regarding the future. I have no idea whether I have to work in SARLRY or not. I want to learn much from this job. But I feel I can’t gain the most from working in this kind of environment. I hope that is only my prejudice against SARLRY. However, I don’t make a decision if I am working in Shanghai until now, and it’s a great distress to me. I wish I could find a solution at last. Good luck with me.

Today, I saw a movie whose name is Mad Detective. That tell a very lively story about toyshop. There are many interesting toys in the story. This film’s really clicked with me, I like it. I wish I could have a toyshop likes that.

 

This is my first journal. Yesterday, I joined in the interviewing of INFOSYS.

I think I was not good in the interview. They really put me through it at the interview, because the whole questions that they asked are English .My English is poor, especially oral English. I can answer the half questions.

The interview was far worse than I had expected. It is a great blow to me. So I am working hard at my English.