一道面试题,请高手指教,万分感谢

[hxiquan80]

有表test
记录为
索引 课程 分数
1 a 80
2 a 80
3 b 90
4 c 70
5 c 70
要求删掉表test中课程和分数相同,但索引有多条的记录只保留索引号最小的那条,即需要删除后表记录为
1 a 80
3 b 90
4 c 70
谢谢!

[gaojie001]

delete tb
from tb t
where exists(select 1 from tb where 课程=t.课程 and 分数=t.分数 and 索引<t.索引)

[sanny_txx]

支持8楼的

[skypaul888]

[Quote=引用 2 楼 pt_caddy 的回复:]
SQL code

delete tb
where 索引 not in (select min(索引)
from tb
group by 课程,分数)
[/Quote]
正解

[Mr_Nice]

/*
 [Author]: OrchidCat[OC]_轻骑兵(向高手学习...)
   [Time]: 2010-08-11 07:52:18
  [Place]: From Beijing
[Version]: 
    Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
	Feb  9 2007 22:47:07 
	Copyright (c) 1988-2005 Microsoft Corporation
	Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

*/
--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([索引] int,[课程] varchar(1),[分数] int)
insert [test]
select 1,'a',80 union all
select 2,'a',80 union all
select 3,'b',90 union all
select 4,'c',70 union all
select 5,'c',70

select * from [test]

SELECT  A.*
FROM    test A
WHERE   [索引] = ( SELECT MIN([索引])
                 FROM   test
                 WHERE  A.[课程] = [课程]
                        AND A.[分数] = [分数]
               )
/*索引          课程   分数
----------- ---- -----------
1           a    80
3           b    90
4           c    70

(3 行受影响)*/

[pt_caddy]

 delete tb 
 where 索引 not in (select min(索引) 
                    from tb 
                    group by 课程,分数)

[华夏小卒]

delete tb
from tb t
where exists(select 1 from tb where  课程=t.课程 and 分数=t.分数 and 索引<t.索引
)

[情殇无限]

delete from test t
where exists(select 1 from test where  课程=t.课程 and 分数=t.分数 and 索引<t.索引)

[Lakesy]

vesion为2008的话,用with&row_number()吧,几天前好像有张相似的帖子

[crszf]

Select A.* From Test A Where 索引 =( Select U_Index From (Select Count(1) Cnt,Min(索引) U_Index From Test Where A.[课程] = [课程] AND A.[分数] = [分数]) T Where Cnt > 1)

[ai593423625]

学习···

[shadowdudu]

delete tb where id in (select min(id) from tb group by kc,fs having count(*)>1) 手机发帖不容易阿

[幸运的意外]

delete tb
from tb t
where exists(select * from tb where 课程=t.课程 and 分数=t.分数 and 索引<t.索引
)

[taotony]

delete test
from test t
where exists(select 1 from test where kc=t.kc and score=t.score and sy<t.sy)

[ask_chang]

DELETE FROM test WHERE id NOT IN (SELECT MIN(id) FROM test GROUP BY course,score)

[suibiantha]

支持8楼

[「已注销」]

if object_id('[test]') is not null drop table [test]
create table [test]([rowID] int,[course] varchar(1),[Score] int)
insert [test]
select 1,'a',80 union all
select 2,'a',80 union all
select 3,'b',90 union all
select 4,'c',70 union all
select 5,'c',70


SELECT MIN(rowid) rownum ,course ,score FROM test
GROUP BY course,score
ORDER BY rownum 

[「已注销」]

如果需要删除重复的行，参考：

DELETE FROM test
WHERE rowID NOT IN (SELECT MIN(rowID) from test
                    GROUP BY test.course,test.score)

[Mr.Huang……]

学习了！
8楼正解

[Rotel-刘志东]

--创建测试表
 if object_id ('[test]')is not null 
drop table [test]
create table test(索引 int, 课程 varchar(10),分数 int)
insert [test]
select 1,'a',80 union all
select 2,'a',80 union all
select 3,'b',90 union all
select 4,'c',70 union all
select 5,'c',70
--查询语句
select min(索引) as '索引',课程,分数 from [test] group by 课程,分数 order by min(索引)
--查询结果
/**
索引 课程  分数
1	  a	    80
3	  b	    90
4	  c	    70
**/

[windxfxx8]

delete [test]
where index not in (select min(index)
from test
group by 课程)

delete from
test from test a where exists(select * from test b where a.课程=b.课程 and a.index<>b.index)