T表:(字段:ID,NAME,ADDRESS,PHONE,LOGDATE)
E表:(字段:NAME,ADDRESS,PHONE) 请写出将表T中NAME存在重复的记录都列出来的SQL语句(按NAME排序) 请写出题目2中,只保留重复记录的第一条,删除其余记录的SQL语句(即使该表不存在重复记录)
我这么做的:将emp表中的rownum 为1,2的两行数据ename改为SMITHdelete from t where name in (select name from t group by name having count(*)>1) and rowid not in (select min(rowid) from t group by name having count(*)>1 );
可以删除多余的ename='SMITH'的数据,而
delete from emp where ename in (select ename from emp group by ename having count(*) > 1)
and
rownum not in (select min(rownum) from emp group by ename having count(*)>1);
则将所有ename='SMITH'的数据都删除了请问这两者有什么区别
E表:(字段:NAME,ADDRESS,PHONE) 请写出将表T中NAME存在重复的记录都列出来的SQL语句(按NAME排序) 请写出题目2中,只保留重复记录的第一条,删除其余记录的SQL语句(即使该表不存在重复记录)
我这么做的:将emp表中的rownum 为1,2的两行数据ename改为SMITHdelete from t where name in (select name from t group by name having count(*)>1) and rowid not in (select min(rowid) from t group by name having count(*)>1 );
可以删除多余的ename='SMITH'的数据,而
delete from emp where ename in (select ename from emp group by ename having count(*) > 1)
and
rownum not in (select min(rownum) from emp group by ename having count(*)>1);
则将所有ename='SMITH'的数据都删除了请问这两者有什么区别
rownum是一个虚拟序号值 而且只支持< 、<= 、 !=
rowid是对于每一条数据的地址,你可以把它看作是固定的(频繁更新,删除操作除外)
*/
SQL> select rowid,deptno from dept order by deptno;
/*
ROWID DEPTNO
------------------ ----------
AAABe/AAFAAABLqAAA 10
AAABe/AAFAAABLqAAB 20
AAABe/AAFAAABLqAAC 30
AAABe/AAFAAABLqAAD 40
*/
SQL> edit
Wrote file afiedt.buf1* select rowid,deptno from dept order by deptno desc
SQL> /
/*
ROWID DEPTNO
------------------ ----------
AAABe/AAFAAABLqAAD 40
AAABe/AAFAAABLqAAC 30
AAABe/AAFAAABLqAAB 20
AAABe/AAFAAABLqAAA 10
*/
SQL> edit
Wrote file afiedt.buf1* select rownum,deptno from dept order by deptno
SQL> /
/*
ROWNUM DEPTNO
---------- ----------
1 10
2 20
3 30
4 40
*/
SQL> edit
Wrote file afiedt.buf1* select rownum,deptno from dept order by deptno desc
SQL> /
/*
ROWNUM DEPTNO
---------- ----------
1 40
2 30
3 20
4 10just think about ROWNUM pseudo column as a 'magic'. You can do
where rownum < n
or
where rownum = 1
but NOT
where rownum > n
or
where rownum = some number > 1
If you have to you can do something like:
select * from(
select t.*,rownum rn from t
)
where rn = 3
*/
and
rownum not in (select min(rownum) from emp group by ename having count(*)>1);
则将所有ename='SMITH'的数据都删除了
在这里and后面的语句应该是没有作用的,最后的select返回的值应该是一个字符串型的,所以rownum 一直没有在里面,也就删掉了所有的 smith。
rowid 和 rownum五楼解释的相当好。。
rowid 每一行数据在表中存在的时候给定的一个序号.
rownum可以理解为你通过sql查出的结果集里面的一个排列顺序;