SQL プログラミングの 4 つの古典的な問題

1. 数値補助表

数値補助表は、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;

2. 連続範囲

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;

3. 最小欠損値

(ここ) をクリックして折りたたむか開きます

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' を挿入します。

4. 行番号を取得します

行番号は、クエリ結果セットの行に順番に割り当てられる連続する整数を指します。

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;