一些经典的SQL编程问题

一 数字辅助表
数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。数字辅助表是一个非常强大的工具，所以我们创建一个持久的数字辅助表：

CREATE TABLE Nums(
a INT UNSIGNED NOT NULL PRIMARY KRY
)ENGINE=InnoDB;

CREATEPRODURE  CreateNums(t  INT  UNSIGNED)
BEGIN
DECLARE  s  INT  UNSIGNEDDEFAULT1;
TRUNCATETABLENums;
INSERTINTONumsSELECTs;
WHILE  s*2<=t  DO
BEGIN
INSERTINTONumsSELECTa+sFROMNums;
SETs=s*2
END;
ENDWHILE;
END;

二 连续范围

CREATETABLEt(a  INT UNSIGNEDNOTNULLPRIMARYKEY);
INSERTINTOtVALUES(1);
INSERTINTOtVALUES(2);
INSERTINTOtVALUES(3);
INSERTINTOtVALUES(100);
INSERTINTOtVALUES(101);
INSERTINTOtVALUES(103);
INSERTINTOtVALUES(104);
INSERTINTOtVALUES(105);

如何得到下面的输出结果呢？

SELECTMIN(a)start,MAX(a)endFROM(
SELECTa,rn,a-rnASdiff
FROM(SELECTa,@a:=@a+1  rnFROMt,(SELECT@a:=0)ASa)
ASb
)ASc
GROUPBYdiff;

三 最小缺失值

点击(此处)折叠或打开

CREATE  TABLE x(
aINTUNSIGNED  PRIMARY KEY,
b  CHAR(1)NOTNULL
)ENGINE=InnoDB;

INSERTINTOx SELECT 3,'a';
INSERTINTOx SELECT 4,'b';
INSERTINTOx SELECT 6,'c';
INSERTINTOx SELECT 7,'d';

注意a列必须是一个正整数，所以这里的类型为INT UNSGINED。最小缺失值的问题是，假设列a从1开始，对于当前表中的数据3、4、6、7，查询应返回1。
如果当前表的数据为1、2、3、4、6、7，则返回5。
解决方案如下：

SELECT
CASE
WHENNOTEXISTS(SELECTaFROMxWHEREa=1)THEN1
ELSE
(SELECTMIN(a)+1ASmissing
FROMxASA
WHERENOTEXISTS
(SELECT*FROMxASB
WHEREA.a+1=B.a))
ENDASmissing;

运行上面的SQL，得到结果为1，若向a列插入1，2后得到的结果为5。
若要对最小缺失值进行补缺操作，解决方案如下:

INSERTINTOx
SELECT
CASE
WHENNOTEXISTS(SELECTaFROMxWHEREa=1)THEN1
ELSE
(SELECTMIN(a)+1ASmissing
FROMxASA
WHERENOTEXISTS
(SELECT*FROMxASB
WHEREA.a+1=B.a))ENDASmissing,'p';

运行上面的SQL，我们将会在a列插入5，b列插入'p'。

四 获取行号
行号是指按顺序为查询结果集的行分配的连续整数。

CREATETABLEsales(
empidvarchar(10)NOTNULL,
mgridvarchar(10)NOTNULL,
qty` int(11)NOTNULL,
PRIMARYKEY(empid)
);

INSERINTOsalsesVALUES('A',Z',300);
INSER INTO salses VALUES('B',X',100);
INSERINTOsalsesVALUES('C',Y',100);
INSER INTO salses VALUES('D',Z',300);
INSERINTOsalsesVALUES('E',X',200);
INSER INTO salses VALUES('F',Z',100);

现在我们根据empid进行行号统计

SELECTempid,
(SELECTCOUNT(*)FROMsalesAST2
WHERET2.empid<=T1.empid)ASrownum
FROMsalesAST1;

但是上面这句SQL效率不是最理想的，在Mysql数据库中得到行号最快的解决方案是采用CROSS JOIN。