Four classic SQL programming problems-Mysql Tutorial-php.cn

Four classic SQL programming problems

伊谢尔伦

Release： 2016-11-24 15:35:56

Original

1276 people have browsed it

1. Numerical auxiliary table

Numerical auxiliary table is a simple table containing only N integers from 1 to N. N is usually very large. The numeric auxiliary table is a very powerful tool, so we create a persistent numeric auxiliary table:

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;

Copy after login

2. Continuous range

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

Copy after login

How to get the following output result?

Four classic SQL programming problems

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;

Copy after login

3. Minimum missing value

Click (here) to collapse or open

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

Copy after login

Note that column a must be a positive integer, so the type here is INT UNSGINED. The problem with minimum missing values is that assuming column a starts at 1, for data 3, 4, 6, 7 in the current table, the query should return 1. If the data in the current table is 1, 2, 3, 4, 6, 7, then 5 is returned.
The solution is as follows:

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;

Copy after login

Run the above SQL and get the result 1. If you insert 1 and 2 into column a, the result will be 5.

To fill in the minimum missing value, the solution is as follows:

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

Copy after login

Run the above SQL, we will insert 5 in column a and 'p' in column b.

4. Get the row number

The row number refers to the consecutive integer assigned to the rows of the query result set in order.

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

Copy after login

Now we perform line number statistics based on empid

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

Copy after login