SQL> select rowid, rownum from eric_tmp
2 /ROWID ROWNUM
------------------ ---------
AABTPtAGjAAAH3NAAA 1
AABTPtAGjAAAH3NAAB 2
AABTPtAGjAAAH3NAAC 3
AABTPtAGjAAAH3NAAD 4
AABTPtAGjAAAH3NAAE 5
AABTPtAGjAAAH3NAAF 6
AABTPtAGjAAAH3NAAG 77 rows selected.SQL>
2 /ROWID ROWNUM
------------------ ---------
AABTPtAGjAAAH3NAAA 1
AABTPtAGjAAAH3NAAB 2
AABTPtAGjAAAH3NAAC 3
AABTPtAGjAAAH3NAAD 4
AABTPtAGjAAAH3NAAE 5
AABTPtAGjAAAH3NAAF 6
AABTPtAGjAAAH3NAAG 77 rows selected.SQL>
ROWID:Before the release of Oracle8, ROWID datatype was used to store the physical
address of each row of each table, as a hexadecimal number. ROWNUM:ROWNUM returns a number indicating the order in which a row was selected from a table.
The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on.
If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows
before the sort is done.
ROWID: 数据库内部存放数据地址
ROWNUM: 选取的行数,即数据的行号。
那么 rownum 是否会发生变化呢?
SQL> select * from eric_tmp
2 / ID NAME
--------- --------------------
1 AAA
2 BBB
1 ABC
3 CCC
2 DDD
4
7 rows selected.SQL> select rowid, rownum, t.* from eric_tmp t
2 /ROWID ROWNUM ID NAME
------------------ --------- --------- --------------------
AABTPtAGjAAAH3NAAA 1 1 AAA
AABTPtAGjAAAH3NAAB 2 2 BBB
AABTPtAGjAAAH3NAAC 3 1 ABC
AABTPtAGjAAAH3NAAD 4 3 CCC
AABTPtAGjAAAH3NAAE 5 2 DDD
AABTPtAGjAAAH3NAAF 6 4
AABTPtAGjAAAH3NAAG 77 rows selected.SQL> select rowid, rownum, t.* from eric_tmp t order by name
2 /ROWID ROWNUM ID NAME
------------------ --------- --------- --------------------
AABTPtAGjAAAH3NAAA 1 1 AAA
AABTPtAGjAAAH3NAAC 3 1 ABC
AABTPtAGjAAAH3NAAB 2 2 BBB
AABTPtAGjAAAH3NAAD 4 3 CCC
AABTPtAGjAAAH3NAAE 5 2 DDD
AABTPtAGjAAAH3NAAF 6 4
AABTPtAGjAAAH3NAAG 77 rows selected.SQL>
select group_name,count(group_name) from sfism4.r117 a
where mo_number='P-G4080020-1-1-5'
and a.rowid!=(select min(rowid) from sfism4.r117 b
where a.serial_number=b.serial_number
and a.group_name=b.group_name
and a.in_station_time=b.in_station_time
and a.error_flag=b.error_flag)
group by group_name
这是一段查询重复值的语句
为什么其中的 rowid 不能用 rownum 代替呢?