四個比較經典的sql程式設計問題-mysql教程-PHP中文網

四個比較經典的sql程式設計問題

伊谢尔伦

發布： 2016-12-03 10:30:51

原創

1111 人瀏覽過

一、數字輔助表

數字輔助表是一個只包含從1到N的N個整數的簡單表，N通常很大。數位輔助表是一個非常強大的工具，所以我們創建一個持久的數位輔助表：

CREATETABLENums(     a INTUNSIGNED NOTNULLPRIMARYKRY
    )ENGINE=InnoDB;  
CREATEPRODURE CreateNums (t INTUNSIGNED ) BEGIN DECLAREs INTUNSIGNED DEFAULT1; TRUNCATETABLENums; INSERTINTONums SELECTs; WHILE s*2 <= t DO                       
BEGIN INSERTINTONums SELECTa+s FROMNums; SETs = s*2 END; ENDWHILE; END;

登入後複製

二、連續範圍

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,&#39;a&#39;; INSERTINTOx SELECT4,&#39;b&#39;; INSERTINTOx SELECT6,&#39;c&#39;; INSERTINTOx SELECT7,&#39;d&#39;;

登入後複製

注意a欄必須是正值一個正值，所以這裡的類型為整數。最小缺失值的問題是，假設列a從1開始，對於目前表中的資料3、4、6、7，查詢應傳回1。如果目前表的資料為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, &#39;p&#39;;

登入後複製

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

四、取得行號

行號是指依序為查詢結果集的行所分配的連續整數。

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

登入後複製

現在我們根據empid進行行號統計

SELECTempid,  (SELECTCOUNT(*) FROMsales AST2
WHERET2.empid <= T1.empid) ASrownum FROMsales AST1;

登入後複製