瘋狂自增記錄 truncate table，case實現橫排， 複製表結構

瘋狂自增記錄~

delete 和 truncate table對比

SQL> create table tb(i number);--<br />Table created.<br />Elapsed: 00:00:00.09<br />SQL> insert into tb values(1);<br />1 row created.<br />Elapsed: 00:00:00.01<br />SQL> insert into tb<br /> 2 select * from tb;<br />1 row created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />2 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />4 rows created.<br />Elapsed: 00:00:00.00<br />SQL> /<br />8 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />16 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />32 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />64 rows created.<br />Elapsed: 00:00:00.00<br />SQL> /<br />128 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />256 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />512 rows created.<br />Elapsed: 00:00:00.00<br />SQL> /<br />1024 rows created.<br />Elapsed: 00:00:00.01<br />SQL> /<br />2048 rows created.<br />Elapsed: 00:00:00.03<br />SQL> select count(*) from tb;<br /> COUNT(*)<br />----------<br /> 4096<br />Elapsed: 00:00:00.01<br />SQL> savepoint sp1;<br />Savepoint created.<br />Elapsed: 00:00:00.00<br />SQL> delete tb;<br />4096 rows deleted.<br />Elapsed: 00:00:00.06<br />SQL> select * from tb;<br />no rows selected<br />Elapsed: 00:00:00.01<br />SQL> rollback to sp1;<br />Rollback complete.<br />Elapsed: 00:00:00.04<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1* select count(*) from tb<br />SQL> /<br /> COUNT(*)<br />----------<br /> 4096<br />Elapsed: 00:00:00.01<br />SQL> savepoint sp1;<br />Savepoint created.<br />Elapsed: 00:00:00.01<br />SQL> truncate table tb;<br />Table truncated.<br />Elapsed: 00:00:00.03<br />Elapsed: 00:00:00.01<br />SQL> rollback to sp1;<br />rollback to sp1<br />*<br />ERROR at line 1:<br />ORA-01086: savepoint 'SP1' never established<br />Elapsed: 00:00:00.01<br />SQL> spool off<br />

 

 

用Case實現橫排

因為分組查詢中，前面查詢的欄位除了用來分組的欄位外，都必須用彙總函式，否則系統不知道該取同一組資料中的哪一個

SQL> edit<br />Wrote file afiedt.buf<br /> 1 create table testCase(<br /> 2 name varchar2(20),<br /> 3 subject varchar(20),<br /> 4* mark number(3,1))<br />SQL> /<br />Table created.<br />Elapsed: 00:00:00.01<br />SQL> insert into testCase<br /> 2 values('ymc','OS',61);<br />1 row created.<br />Elapsed: 00:00:00.00<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 insert into testCase<br /> 2* values('ymc','DS',62)<br />SQL> /<br />1 row created.<br />Elapsed: 00:00:00.01<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 insert into testCase<br /> 2* values('ymc','DB',63)<br />SQL> /<br />1 row created.<br />Elapsed: 00:00:00.01<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 insert into testCase<br /> 2* values('tingting','OS',91)<br />SQL> /<br />1 row created.<br />Elapsed: 00:00:00.00<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 insert into testCase<br /> 2* values('tingting','DS',92)<br />SQL> /<br />1 row created.<br />Elapsed: 00:00:00.01<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 insert into testCase<br /> 2* values('tingting','DB',99)<br />SQL> /<br />1 row created.<br />Elapsed: 00:00:00.00<br />SQL> select * from testCase;<br />NAME SUBJECT MARK<br />-------------------- -------------------- ----------<br />ymc OS 61<br />ymc DS 62<br />ymc DB 63<br />tingting OS 91<br />tingting DS 92<br />tingting DB 99<br />6 rows selected.<br />Elapsed: 00:00:00.06<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 select Name,<br /> 2 sum(case when Subject='OS' then mark end) as OS,<br /> 3 sum(case when Subject='DS' then mark end) as DS,<br /> 4 sum(case when Subject='DB' then mark end) as DB<br /> 5 from testCase<br /> 6* group by Name<br />SQL> /<br />NAME OS DS DB<br />-------------------- ---------- ---------- ----------<br />tingting 91 92 99<br />ymc 61 62 63<br />Elapsed: 00:00:00.03<br />SQL> spool off<br />

 

複製表結構 where 1=2

SQL> select count(*) from testCase;<br /> COUNT(*)<br />----------<br /> 7<br />Elapsed: 00:00:00.01<br />SQL> desc testCase;<br /> Name Null? Type<br /> ----------------------------------------- -------- ----------------------------<br /> NAME VARCHAR2(20)<br /> SUBJECT VARCHAR2(20)<br /> MARK NUMBER(3,1)<br />SQL> edit<br />Wrote file afiedt.buf<br /> 1 create table dupTestCase<br /> 2 as<br /> 3 select *<br /> 4 from testCase<br /> 5* where 1=2<br />SQL> /<br />Table created.<br />Elapsed: 00:00:00.07<br />SQL> select * from dupTestCase;<br />no rows selected<br />Elapsed: 00:00:00.00<br />SQL> desc dupTestCase;<br /> Name Null? Type<br /> ----------------------------------------- -------- ----------------------------<br /> NAME VARCHAR2(20)<br /> SUBJECT VARCHAR2(20)<br /> MARK NUMBER(3,1)<br />SQL> spool off<br />