一 数字辅助表
数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。数字辅助表是一个非常强大的工具，所以我们创建一个持久的数字辅助表：
-
CREATE TABLE Nums(
-
a INT UNSIGNED NOT NULL PRIMARY KRY
-
)ENGINE=InnoDB;
-
-
CREATE PRODURE CreateNums (t INT UNSIGNED )
-
BEGIN
-
DECLARE s INT UNSIGNED DEFAULT 1;
-
TRUNCATE TABLE Nums;
-
INSERT INTO Nums SELECT s;
-
WHILE s*2 <= t DO
-
BEGIN
-
INSERT INTO Nums SELECT a+s FROM Nums;
-
SET s = s*2
-
END;
-
END WHILE;
-
END;
二 连续范围
-
CREATE TABLE t (a INT UNSIGNED NOT NULL PRIMARY KEY);
-
INSERT INTO t VALUES(1);
-
INSERT INTO t VALUES(2);
-
INSERT INTO t VALUES(3);
-
INSERT INTO t VALUES(100);
-
INSERT INTO t VALUES(101);
-
INSERT INTO t VALUES(103);
-
INSERT INTO t VALUES(104);
-
INSERT INTO t VALUES(105);
如何得到下面的输出结果呢？
-
SELECT MIN(a) start,MAX(a) end FROM (
-
SELECT a,rn,a-rn AS diff
-
FROM (SELECT a,@a:=@a+1 rn FROM t,(SELECT @a:=0) AS a)
-
AS b
-
)AS c
-
GROUP BY diff;
三 最小缺失值
-
CREATE TABLE x(
-
a INT UNSIGNED PRIMARY KEY,
-
b CHAR(1) NOT NULL
-
)ENGINE = InnoDB;
-
-
INSERT INTO x SELECT 3,‘a‘;
-
INSERT INTO x SELECT 4,‘b‘;
-
INSERT INTO x SELECT 6,‘c‘;
-
INSERT INTO x SELECT 7,‘d‘;
注意a列必须是一个正整数，所以这里的类型为INT UNSGINED。最小缺失值的问题是，假设列a从1开始，对于当前表中的数据3、4、6、7，查询应返回1。
如果当前表的数据为1、2、3、4、6、7，则返回5。
解决方案如下：
-
SELECT
-
CASE
-
WHEN NOT EXISTS (SELECT a FROM x WHERE a=1)THEN 1
-
ELSE
-
(SELECT MIN(a)+1 AS missing
-
FROM x AS A
-
WHERE NOT EXISTS
-
(SELECT * FROM x AS B
-
WHERE A.a+1=B.a))
-
END AS missing;
运行上面的SQL，得到结果为1，若向a列插入1，2后得到的结果为5。
若要对最小缺失值进行补缺操作，解决方案如下:
-
INSERT INTO x
-
SELECT
-
CASE
-
WHEN NOT EXISTS (SELECT a FROM x WHERE a=1)THEN 1
-
ELSE
-
(SELECT MIN(a)+1 AS missing
-
FROM x AS A
-
WHERE NOT EXISTS
-
(SELECT * FROM x AS B
-
WHERE A.a+1=B.a)) END AS missing, ‘p‘;
运行上面的SQL，我们将会在a列插入5，b列插入‘p‘。
四 获取行号
行号是指按顺序为查询结果集的行分配的连续整数。
-
CREATE TABLE sales (
-
empid varchar(10) NOT NULL,
-
mgrid varchar(10) NOT NULL,
-
qty` int(11) NOT NULL,
-
PRIMARY KEY (empid)
-
);
-
-
INSER INTO salses VALUES(‘A‘,Z‘,300);
-
INSER INTO salses VALUES(‘B‘,X‘,100);
-
INSER INTO salses VALUES(‘C‘,Y‘,100);
-
INSER INTO salses VALUES(‘D‘,Z‘,300);
-
INSER INTO salses VALUES(‘E‘,X‘,200);
-
INSER INTO salses VALUES(‘F‘,Z‘,100);
现在我们根据empid进行行号统计
-
SELECT empid,
-
(SELECT COUNT(*) FROM sales AS T2
-
WHERE T2.empid <= T1.empid) AS rownum
-
FROM sales AS T1;
但是上面这句SQL效率不是最理想的，在Mysql数据库中得到行号最快的解决方案是采用CROSS JOIN。
一些经典的SQL编程问题
原文：http://blog.chinaunix.net/uid-28841896-id-4657262.html
