四個經典的sql編程問題

一、?數(shù)字輔助表

數(shù)字輔助表是一個只包含從1到n的n個整數(shù)的簡單表,n通常很大。數(shù)字輔助表是一個非常強大的工具,所以我們創(chuàng)建一個持久的數(shù)字輔助表:

CREATETABLENums(?????a?INTUNSIGNED?NOTNULLPRIMARYKRY  ????)ENGINE=InnoDB;??  CREATEPRODURE?CreateNums?(t?INTUNSIGNED?)?BEGIN?DECLAREs?INTUNSIGNED?default1;?TRUNCATETABLENums;?INSERTINTONums?SELECTs;?WHILE?s*2?<p>二?、連續(xù)范圍<br></p><pre class="brush:php;toolbar:false">CREATETABLEt?(a?INTUNSIGNED?NOTNULLPRIMARYKEY);?INSERTINTOt?VALUES(1);?INSERTINTOt?VALUES(2);?INSERTINTOt?VALUES(3);?  INSERTINTOt?VALUES(100);?INSERTINTOt?VALUES(101);?INSERTINTOt?VALUES(103);?INSERTINTOt?VALUES(104);?INSERTINTOt?VALUES(105);

如何得到下面的輸出結果呢?

四個經典的sql編程問題

SELECTMIN(a)?start,MAX(a)?endFROM(????SELECTa,rn,a-rn?ASdiff????FROM(SELECTa,@a:=@a+1?rn?FROMt,(SELECT@a:=0)?ASa)????ASb?)ASc?GROUPBYdiff;

三?、?最小缺失值

點擊(此處)折疊或打開

CREATETABLEx(???a?INTUNSIGNED?PRIMARYKEY,???b?CHAR(1)?NOTNULL?)ENGINE?=?InnoDB;???  INSERTINTOx?SELECT3,'a';?INSERTINTOx?SELECT4,'b';?INSERTINTOx?SELECT6,'c';?INSERTINTOx?SELECT7,'d';

注意a列必須是一個正整數(shù),所以這里的類型為INT?UNSGINED。最小缺失值的問題是,假設列a從1開始,對于當前表中的數(shù)據3、4、6、7,查詢應返回1。如果當前表的數(shù)據為1、2、3、4、6、7,則返回5。
解決方案如下:

SELECT  CASE  WHENNOTEXISTS?(SELECTa?FROMx?WHEREa=1)THEN1  ELSE?(SELECTMIN(a)+1?ASmissing?FROMx?ASA  WHERENOTEXISTS?(SELECT*?FROMx?ASB  WHEREA.a+1=B.a))?ENDASmissing;

運行上面的SQL,得到結果為1,若向a列插入1,2后得到的結果為5。

若要對最小缺失值進行補缺操作,解決方案如下:

INSERTINTOx?SELECT  CASE  WHENNOTEXISTS?(SELECTa?FROMx?WHEREa=1)THEN1  ELSE?(SELECTMIN(a)+1?ASmissing?FROMx?ASA  WHERENOTEXISTS?(SELECT*?FROMx?ASB  WHEREA.a+1=B.a))?ENDASmissing,?'p';

運行上面的SQL,我們將會在a列插入5,b列插入’p’。

四、?獲取行號

行號是指按順序為查詢結果集的行分配的連續(xù)整數(shù)。

CREATETABLEsales?(???empid?varchar(10)?NOTNULL,???mgrid?varchar(10)?NOTNULL,???qty`?int(11)?NOTNULL,  ??PRIMARYKEY(empid)  );  ?  INSER?INTOsalses?VALUES('A',Z',300);  INSER?INTO?salses?VALUES('B',X',100);  INSER?INTOsalses?VALUES('C',Y',100);  INSER?INTO?salses?VALUES('D',Z',300);  INSER?INTOsalses?VALUES('E',X',200);  INSER?INTO?salses?VALUES('F',Z',100);

現(xiàn)在我們根據empid進行行號統(tǒng)計

SELECTempid,??(SELECTCOUNT(*)?FROMsales?AST2  WHERET2.empid?
? 版權聲明
THE END
喜歡就支持一下吧
點贊7 分享