给记录加编号

Question

现在想把表中一列修改为编号，编号为00001,00002，自动编就可以
应该怎么写

Answer 1

建议使用序列DROP SEQUENCE_ID_S;
CREATE SEQUENCE SEQUENCE_ID_SINCREMENT BY 1 START WITH 1;在使用的时候直接取就可以select SEQUENCE_ID_S.next into v_count from dual;
insert into 表（字段1）values（v_count ）

Answer 2

update tb t1
set t1.id=(select t2.rn 
             from (select t3.id, rowid, 
                          row_number() over(order by rowid) as rn
                     from tb t3 ) t2
           where t2.rowid=t1.rowid )
where exists (SELECT rowid from tb t4 where t4.rowid=t1.rowid );

Answer 3

scott@TBWORA> create table tb as select * from emp;表已创建。scott@TBWORA> alter table tb add id number(18,0);表已更改。scott@TBWORA> select * from tb;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO         ID
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                 20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
      7839 KING                 PRESIDENT                     17-11月-81           5000                 10
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
      7900 JAMES                CLERK                    7698 03-12月-81            950                 30
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。scott@TBWORA> update tb t1
  2  set t1.id=(select t2.rn
  3               from (select t3.id, rowid,
  4                            row_number() over(order by rowid) as rn
  5                       from tb t3 ) t2
  6             where t2.rowid=t1.rowid )
  7  where exists (SELECT rowid from tb t4 where t4.rowid=t1.rowid );已更新14行。scott@TBWORA> select tb.*, rowid from tb order by rowid;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO         ID ROWID
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ------------------
      7369 SMITH                CLERK                    7902 17-12月-80            800                 20             1 AAAMyMAAEAAAAwsAAA
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30             2 AAAMyMAAEAAAAwsAAB
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30             3 AAAMyMAAEAAAAwsAAC
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20             4 AAAMyMAAEAAAAwsAAD
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30             5 AAAMyMAAEAAAAwsAAE
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30             6 AAAMyMAAEAAAAwsAAF
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10             7 AAAMyMAAEAAAAwsAAG
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20             8 AAAMyMAAEAAAAwsAAH
      7839 KING                 PRESIDENT                     17-11月-81           5000                 10             9 AAAMyMAAEAAAAwsAAI
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30            10 AAAMyMAAEAAAAwsAAJ
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20            11 AAAMyMAAEAAAAwsAAK
      7900 JAMES                CLERK                    7698 03-12月-81            950                 30            12 AAAMyMAAEAAAAwsAAL
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20            13 AAAMyMAAEAAAAwsAAM
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10            14 AAAMyMAAEAAAAwsAAN已选择14行。

Answer 4

我也觉得使用序列好多了。sequence...

Answer 5

-- 固守宽度前面要补零的话，加个 lpad()函数就OK啦！scott@TBWORA> create table tb as select * from emp;表已创建。scott@TBWORA> select * from tb;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
      7369 SMITH                CLERK                    7902 17-12月-80            800                 20
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20
      7839 KING                 PRESIDENT                     17-11月-81           5000                 10
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20
      7900 JAMES                CLERK                    7698 03-12月-81            950                 30
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10已选择14行。scott@TBWORA> alter table tb add id varchar2(10);表已更改。scott@TBWORA> update tb t1
  2  set t1.id=(select lpad(t2.rn,5,'0')
  3               from (select rowid,
  4                            row_number() over(order by rowid) as rn
  5                       from tb t3 ) t2
  6             where t2.rowid=t1.rowid )
  7  where exists (SELECT rowid from tb t4 where t4.rowid=t1.rowid );已更新14行。scott@TBWORA> set line 160
scott@TBWORA> select * from tb;     EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO ID
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- --------------------
      7369 SMITH                CLERK                    7902 17-12月-80            800                 20 00001
      7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300      30 00002
      7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500      30 00003
      7566 JONES                MANAGER                  7839 02-4月 -81           2975                 20 00004
      7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400      30 00005
      7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                 30 00006
      7782 CLARK                MANAGER                  7839 09-6月 -81           2450                 10 00007
      7788 SCOTT                ANALYST                  7566 19-4月 -87           3000                 20 00008
      7839 KING                 PRESIDENT                     17-11月-81           5000                 10 00009
      7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0      30 00010
      7876 ADAMS                CLERK                    7788 23-5月 -87           1100                 20 00011
      7900 JAMES                CLERK                    7698 03-12月-81            950                 30 00012
      7902 FORD                 ANALYST                  7566 03-12月-81           3000                 20 00013
      7934 MILLER               CLERK                    7782 23-1月 -82           1300                 10 00014已选择14行。

Answer 6

-- 上面是更新表中原数据，更改原数据后，再按表原数据中最大的id+1 去定义一个序列的起始值，
-- 创建相应的触发器去填充id字段。-- 详性请参考：http://topic.csdn.net/u/20110810/11/a8b0f69c-b11f-40ad-a291-2ffc02158ad8.html

Answer 7

create sequence sequence_id_sincrement by 1 start with 1