네 가지 고전적인 SQL 프로그래밍 문제

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;