四个经典的SQL编程问题 -控件新闻-慧都网

四个经典的SQL编程问题

原创|使用教程|编辑：龚雪|2014-12-09 09:55:47.000|阅读 701 次

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

一、数字辅助表

数字辅助表是一个只包含从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。

狂欢继续！【年终大促巅峰盛"慧" 】促销火热进行中 iPhone 6 Plus、 iPhone 6、iPad Air满就送，还不赶快买买买！

标签：SQL Sql Server Compact mysql

本站文章除注明转载外，均为本站原创或翻译。欢迎任何形式的转载，但请务必注明出处、不得修改原文相关链接，如果存在内容上的异议请邮件反馈至chenjj@pclwef.cn

文章转载自：慧都控件网

上一篇：dtSearch使用教程：全文数据库索引下一篇：dtSearch使用教程：文件缓存

彩票走势图

在线采购

中文文档库

试用/Demo下载

开发社群