四個經典的SQL程式設計問題

一、 數字輔助表

數字輔助表是一個只包含從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);

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

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列必須是一個正整數，所以這裡的型別為INT UNSGINED。最小缺失值的問題是，假設列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, 'p';

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

四、 獲取行號

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

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);

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

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

但是上面這句SQL效率不是最理想的，在Mysql資料庫中得到行號最快的解決方案是採用CROSS  JOIN。