很有挑战性的sql语句，大家来做做看

[lwtwrm]

下面我给出实际数据供大家使用。
表tab
col1 col2 col3
111 aaa ok
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok

第一个问题：查找col1,col2相同的重复记录,请写出sql查询语句
查询结果应该如下
col1 col2 col3
111 aaa ok
111 aaa no
111 aaa ok
222 aaa ok
222 aaa no

第二个问题：如何删除col1,col2,col3完全相同的字段，而只保留一条数据
删除后，tab表中数据应为
col1 col2 col3
222 aaa ok
333 bbb ok
111 bbb no
111 aaa no
222 aaa no
111 aaa ok

[silentwins]

select * from tab inner join (select count(*) as c, col1 from tab) tmp
on tab.col1 = tmp.col1 and tmp.c >1

select distinct * from tab

[Lovely_baby]

1:

select col1 ,col2 from tablename
　　group by col1 ,col2 
　　having(count(*))>1

[波导终结者]

declare @t table (col1 varchar(50),col2 varchar(50),col3 varchar(50))
insert into @t
select '111','aaa','ok' union all
select '222','aaa','ok' union all
select '333','bbb','ok' union all
select '111','bbb','no' union all
select '111','aaa','no' union all
select '222','aaa','no' union all
select '111','aaa','ok'


select * from @t where col1 in
	(select col1 from @t group by col1,col2 having count(*)>1)
and col2 in
	(select col2 from @t group by col1,col2 having count(*)>1)

select distinct col1,col2,col3 from @t

[Dream_Hunter_]

[Quote=引用 3 楼 alifriend 的回复:]
SQL codedeclare@ttable (col1varchar(50),col2varchar(50),col3varchar(50))insertinto@tselect'111','aaa','ok'unionallselect'222','aaa','ok'unionallselect'333','bbb','ok'unionallselect'111','bbb','no'unio?-
[/Quote]
up正确

[ZKM_ZHANG]

第2个问题要有个唯的一id才行

[tkscascor]

--测试数据, ascor,09-12-24
declare @t table (col1 varchar(50),col2 varchar(50),col3 varchar(50))
insert into @t
select '111','aaa','ok' union all
select '222','aaa','ok' union all
select '333','bbb','ok' union all
select '111','bbb','no' union all
select '111','aaa','no' union all
select '222','aaa','no' union all
select '111','aaa','ok'

---- 1
select a.col1,a.col2,a.col3
from @t a inner join
	(
		select col1 ,col2 
		from @t
		group by col1 ,col2 
		having(count(1))>1
	) as b
	on a.col1=b.col1 and a.col2=b.col2
---- 2
select distinct col1,col2,col3 from @t

[tkscascor]

----测试结果


（所影响的行数为 7 行）

col1                                               col2                                               col3                                               
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
111                                                aaa                                                ok
111                                                aaa                                                no
111                                                aaa                                                ok
222                                                aaa                                                ok
222                                                aaa                                                no

（所影响的行数为 5 行）

col1                                               col2                                               col3                                               
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
111                                                aaa                                                no
111                                                aaa                                                ok
111                                                bbb                                                no
222                                                aaa                                                no
222                                                aaa                                                ok
333                                                bbb                                                ok

（所影响的行数为 6 行）

[游北亮]

这有什么很有挑战性？
第一个很简单，有人做出来了

第二个SQL如果是Oracle也简单
如果是SqlServer，估计要用到存储过程了，不知道SqlServer有没有类似Rowid的字段属性

Oracle的第2个Sql

DELETE FROM tab
 WHERE tab.ROWID IN (SELECT B.RID
                     FROM (SELECT tab.*,
                                  tab.ROWID RID,
                                  ROW_NUMBER() OVER(PARTITION BY col1, col2, col3 ORDER BY col1, col2, col3) RN
                             FROM tab) B
                    WHERE RN <> 1)