有一个表t_customer,列code 为varchar(20),name为varchar(40);code为主键列且不同,写一条sql语句删除name相同且想同name中codec 对应值最大的记录(仅一条SQL语句)。
解决方案 »
- 笔试题
- 请问创建表空间语句中的logging是如何体现作用的?
- 数据库中截取字符串很影响速度吗?
- 在线等oracle9i中建立函数是否可以用数组作为参数,如果可以,如何处理……在线等
- select z1,z2,z3,z4 from t order by decode(z3,null,0,1)
- 数据库中碰到奇怪的事情???
- 从一个用户接受表数据
- Net Configuration Assistant建好服务存储问题
- 第一天学oracle,scott!解释得越多越好,可以再开一贴送分!
- 请帮忙解决下面几个问题
- 查看数据库中是否存在某字段以xxx开头的记录,sql语句怎么写。
- RAC双机修改系统时间问题?
where exists (select 1 from t_customer t1 where t1.name = t.name and t.code > t1.code);
delete from t_customer t
where not exists (select 1 from t_customer t1 where t1.name = t.name and t.code < t1.code);
select max(code ) from t_customer group by name
)
select max(code ) from t_customer group by name
)
where exists (select 1 from t_customer t1 where t1.name = t.name and t.code < t1.code);删除最大的记录 应该是这样 呵呵
---------- --------------------
1 wh
2 wh
3 wh
4 wp
5 wr
6 wq
7 wy7 rows selected.SQL> delete from t1
2 where not exists(select 1 from t1 t where t.name=t1.name and t1.id<t.id);5 rows deleted.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 whSQL>
我试了下你这个
好像不太对哦?
2 where (t1.id, t1.name) in (select max(id),name from t t2 group by name having count(1) >= 2) ;1 row deletedSQL> select * from t; ID NAME
---------- --------------------
1 wh
2 wh
7 wy
6 wq
5 wx
4 wr6 rows selected
delete from t_customer a
where exists (select code,name
from (select name,min(code) code
from t_customer
group by name
) b
where a.name = b.name
and a.code <> b.code);
where code in (select max(code) code
from t_customer a where a.name1=b.name1)delete from t_customer b
where code in (select max(code) over(partition by name1 order by name1) code
from t_customer)
如果按照1#的那种写法不管用大于号还是小于号都不对
SQL> delete from t1
2 where exists(select 1 from t1 t where t.name=t1.name and t.id>t1.id);2 rows deleted.SQL> select * from t1; ID NAME
---------- --------------------
3 wh
4 wp
5 wr
6 wq
7 wySQL> rollback;Rollback complete.SQL> delete from t1
2 where exists(select 1 from t1 t where t.name=t1.name and t.id<t1.id);2 rows deleted.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
4 wp
5 wr
6 wq
7 wy
SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 wh
3 wh
4 wp
5 wr
6 wq
7 wy7 rows selected.
SQL> delete from t1
2 where (t1.id,t1.name) in (select max(t.id),t.name from t1 t group by t.name
having count(1)>=2);1 row deleted.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 wh
4 wp
5 wr
6 wq
7 wy6 rows selected.
SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 wh
3 wh
4 wp
5 wr
6 wq
7 wy7 rows selected.SQL> delete from t1
2 where t1.id in
3 (select max(id) from t1 t where t.name=t1.name);5 rows deleted.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 whSQL> rollback;Rollback complete.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 wh
3 wh
4 wp
5 wr
6 wq
7 wy7 rows selected.SQL> delete from t1
2 where t1.id in
3 (select max(id) over(partition by name order by name) id from t1);5 rows deleted.SQL> select * from t1; ID NAME
---------- --------------------
1 wh
2 whSQL>