四个比较经典的sql编程问题
程序员文章站
2022-05-02 17:25:17
...
一、 数字辅助表
数字辅助表是一个只包含从1到N的N个整数的简单表,N通常很大。数字辅助表是一个非常强大的工具,所以我们创建一个持久的数字辅助表:
数字辅助表是一个只包含从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;
二 、连续范围
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;
三 、 最小缺失值
点击(此处)折叠或打开
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'。
四、 获取行号
行号是指按顺序为查询结果集的行分配的连续整数。
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;