四个经典的SQL编程问题 (希望对你有帮助)



四个经典的SQL编程问题

使用教程 | 作者：如影随形 | 2014-12-09 09:53:33| 阅读 244次 有用 (4) 评论 (0) 收藏

---

标签： SQL mysql Sql Server Compact

概述：一些经典的SQL编程问题。

一、 数字辅助表

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 ;

二 、连续范围

?

1 2 3 4 5 6 7 8 9

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

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

?

1 2 3 4 5 6

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;

三 、 最小缺失值

点击(此处)折叠或打开

?

1 2 3 4 5 6 7 8 9

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。

解决方案如下：

?

1 2 3 4 5 6 7 8 9 10

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。

若要对最小缺失值进行补缺操作，解决方案如下:

?

1 2 3 4 5 6 7 8 9 10

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

四、 获取行号

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13

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进行行号统计

?

1 2 3 4

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

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

From: silence0607