一些经典的SQL编程问题

浏览数：18 / 时间：2015年06月12日
一 数字辅助表
    数字辅助表是一个只包含从1到N的N个整数的简单表，N通常很大。数字辅助表是一个非常强大的工具，所以我们创建一个持久的数字辅助表：
  1. CREATE TABLE Nums(
  2.     a INT UNSIGNED NOT NULL PRIMARY KRY
  3.     )ENGINE=InnoDB;

  5. CREATE PRODURE CreateNums (t INT UNSIGNED )
  6. BEGIN
  7. DECLARE s INT UNSIGNED DEFAULT 1;
  8. TRUNCATE TABLE Nums;
  9. INSERT INTO Nums SELECT s;
  10. WHILE s*2 <= t DO                       
  11. BEGIN
  12. INSERT INTO Nums SELECT a+s FROM Nums;
  13. SET s = s*2
  14. END;
  15. END WHILE;
  16. END;

二 连续范围
  1. CREATE TABLE t (a INT UNSIGNED NOT NULL PRIMARY KEY);
  2. INSERT INTO t VALUES(1);
  3. INSERT INTO t VALUES(2);
  4. INSERT INTO t VALUES(3);
  5. INSERT INTO t VALUES(100);
  6. INSERT INTO t VALUES(101);
  7. INSERT INTO t VALUES(103);
  8. INSERT INTO t VALUES(104);
  9. INSERT INTO t VALUES(105);
    如何得到下面的输出结果呢？
    
  1. SELECT MIN(a) start,MAX(a) end FROM (
  2.    SELECT a,rn,a-rn AS diff
  3.    FROM (SELECT a,@a:=@a+1 rn FROM t,(SELECT @a:=0) AS a)
  4.    AS b
  5. )AS c
  6. GROUP BY diff;

三  最小缺失值

点击(此处)折叠或打开

  1. CREATE TABLE x(
  2.   a INT UNSIGNED PRIMARY KEY,
  3.   b CHAR(1) NOT NULL
  4. )ENGINE = InnoDB;

  6. INSERT INTO x SELECT 3,‘a‘;
  7. INSERT INTO x SELECT 4,‘b‘;
  8. INSERT INTO x SELECT 6,‘c‘;
  9. INSERT INTO x SELECT 7,‘d‘;
    注意a列必须是一个正整数，所以这里的类型为INT UNSGINED。最小缺失值的问题是，假设列a从1开始，对于当前表中的数据3、4、6、7，查询应返回1。
    如果当前表的数据为1、2、3、4、6、7，则返回5。
    解决方案如下：
  1. SELECT
  2. CASE
  3. WHEN NOT EXISTS (SELECT a FROM x WHERE a=1)THEN 1
  4. ELSE
  5. (SELECT MIN(a)+1 AS missing
  6. FROM x AS A
  7. WHERE NOT EXISTS
  8. (SELECT * FROM x AS B
  9. WHERE A.a+1=B.a))
  10. END AS missing;
    运行上面的SQL，得到结果为1，若向a列插入1，2后得到的结果为5。
    若要对最小缺失值进行补缺操作，解决方案如下:
  1. INSERT INTO x
  2. SELECT
  3. CASE
  4. WHEN NOT EXISTS (SELECT a FROM x WHERE a=1)THEN 1
  5. ELSE
  6. (SELECT MIN(a)+1 AS missing
  7. FROM x AS A
  8. WHERE NOT EXISTS
  9. (SELECT * FROM x AS B
  10. WHERE A.a+1=B.a)) END AS missing, ‘p‘;
    运行上面的SQL，我们将会在a列插入5，b列插入‘p‘。

四 获取行号
    行号是指按顺序为查询结果集的行分配的连续整数。
  1. CREATE TABLE sales (
  2.   empid varchar(10) NOT NULL,
  3.   mgrid varchar(10) NOT NULL,
  4.   qty` int(11) NOT NULL,
  5.   PRIMARY KEY (empid)
  6. );

  8. INSER INTO salses VALUES(‘A‘,Z‘,300);
  9. INSER INTO salses VALUES(‘B‘,X‘,100);
  10. INSER INTO salses VALUES(‘C‘,Y‘,100);
  11. INSER INTO salses VALUES(‘D‘,Z‘,300);
  12. INSER INTO salses VALUES(‘E‘,X‘,200);
  13. INSER INTO salses VALUES(‘F‘,Z‘,100);
    现在我们根据empid进行行号统计
  1. SELECT empid,
  2.  (SELECT COUNT(*) FROM sales AS T2
  3. WHERE T2.empid <= T1.empid) AS rownum
  4. FROM sales AS T1;
    但是上面这句SQL效率不是最理想的，在Mysql数据库中得到行号最快的解决方案是采用CROSS  JOIN。

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： class style src com 问题 数据 si ha la class style src com 问题 数据 si ha la

相关文章

随机文章

您可能还喜欢

您可能还喜欢