一些经典的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。