四个必须知道的SQL编程问题

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';

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;

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';

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);

SELECT empid,
(SELECT COUNT(*) FROM sales AS T2
WHERE T2.empid <= T1.empid) AS rownum
FROM sales AS T1;