求SQL语句,越简单越好

有一个张员工工资表(SALARY),表有三列：员工编号(ID),姓名(NAME),工资(SALARY)
1.查询id重复记录
2.删除id重复记录，只保留第一条(说明，不需要考虑表中存在完全相同纪录的情况)

家庭作业？

回复 #1 congruilan 的帖子

知道第一个:
1.
select *
from salary
where ID in
(
select ID
from salary
group by ID
having count(*)>1
)

第二个还没想出来,学习SQL中,
多谢大家指教.

[quote]原帖由 [i]zhangfengh[/i] 于 2008-7-5 15:18 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10865530&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]
家庭作业？ [/quote]
:sweat2:
学习SQL中,惭愧^

ROW_NUMBER(),MAX()......

类似的帖子好像还不少：
给一个方案：
delete from salary a where a.rowid >(select min(b.rowid) from salary where a.id=b.id);

[quote]原帖由 [i]wuhuaT[/i] 于 2008-7-5 15:32 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10865644&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]
ROW_NUMBER(),MAX()...... [/quote]
[size=4][b]老大能不能说明白一点啊[/b][/size]

[quote]原帖由 [i]congruilan[/i] 于 2008-7-5 16:16 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10865982&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]

老大能不能说明白一点啊 [/quote]

我是最差的,来回答一下简单的问题:
with tab as (select *,row_number() over (partiton by id order by salary) rn from salary)
select id,name,salary from tab where rn=1

[quote]原帖由 [i]zhangweicai74[/i] 于 2008-7-5 17:28 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10866440&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]


我是最差的,来回答一下简单的问题:
with tab as (select *,row_number() over (partiton by id order by salary) rn from salary)
select id,name,salary from tab where rn=1 [/quote]

这个只是查询出数据，达不到LZ要求的啊，呵呵，必须要DELETE的

同6楼。。。。。。。

[quote]原帖由 [i]andytianyi[/i] 于 2008-7-5 17:42 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10866508&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]


这个只是查询出数据，达不到LZ要求的啊，呵呵，必须要DELETE的 [/quote]
能查出来,修改一下难道还不能DELETE:o

[quote]原帖由 [i]zhangweicai74[/i] 于 2008-7-5 17:28 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10866440&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]


我是最差的,来回答一下简单的问题:
with tab as (select *,row_number() over (partiton by id order by salary) rn from salary)
select id,name,salary from tab where rn=1 [/quote]


汗！本来很简单的语句写这么复杂干啥子？

1.select * from tablea a where rowid = (select max(rowid) from tablea b where a.pk = b.pk)
2.delete from tablea a where rowid < (select max(rowid) from tablea b where a.pk = b.pk)

用rowid好

[quote]原帖由 [i]congruilan[/i] 于 2008-7-5 15:12 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10865483&ptid=1017241][img]http://www.itpub.net/images/common/back.gif[/img][/url]
有一个张员工工资表(SALARY),表有三列：员工编号(ID),姓名(NAME),工资(SALARY)
1.查询id重复记录
2.删除id重复记录，只保留第一条(说明，不需要考虑表中存在完全相同纪录的情况) [/quote]
1 select s.* from salary s,salary b where s.id=s1.id and not( s.id=s1.id and s.name=s1.name s.sal=s1.sal)
2 delete from salary where id in (select s.id from salary s,salary b where s.id=s1.id and not( s.id=s1.id and s.name=s1.name s.sal=s1.sal))