Vier klassische SQL-Programmierprobleme

1. Numerische Hilfstabelle

Die numerische Hilfstabelle ist eine einfache Tabelle, die nur N ganze Zahlen von 1 bis N enthält. N ist normalerweise sehr groß. Die numerische Hilfstabelle ist ein sehr leistungsfähiges Werkzeug, daher erstellen wir eine persistente numerische Hilfstabelle:

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. So erhalten Sie den kontinuierlichen Bereich

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

Was ist mit der Ausgabe unten?

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. Minimaler fehlender Wert

Klicken Sie (hier), um es zu reduzieren oder zu öffnen

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

Beachten Sie, dass Spalte a eine positive Ganzzahl sein muss, daher ist der Typ hier INT UNSGINED. Das Problem mit minimalen fehlenden Werten besteht darin, dass die Abfrage unter der Annahme, dass Spalte a bei 1 beginnt, für die Daten 3, 4, 6, 7 in der aktuellen Tabelle 1 zurückgeben sollte. Wenn die Daten in der aktuellen Tabelle 1, 2, 3, 4, 6, 7 sind, wird 5 zurückgegeben.
Die Lösung lautet wie folgt:

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;

Führen Sie die obige SQL aus und das Ergebnis ist 1. Wenn Sie 1 und 2 in Spalte a einfügen, ist das Ergebnis 5.

Um den minimalen fehlenden Wert auszufüllen, lautet die Lösung wie folgt:

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

Führen Sie das obige SQL aus, wir werden 5 in Spalte a und „p“ in Spalte b einfügen.

4. Ermitteln Sie die Zeilennummer

Die Zeilennummer bezieht sich auf die fortlaufende Ganzzahl, die den Zeilen der Abfrageergebnismenge der Reihe nach zugewiesen wird.

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

Jetzt führen wir Zeilennummernstatistiken basierend auf empid durch

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