SQL SERVER中SQL语句的一个问题——得到连续数字数据集的SQL语句

问题：

在数据库中没有任何可知数据时，通过一条SQL语句怎么得到一个连续数字的数据集，如从1到10000的数据集？

问题解决的思路：

得到连续连续数字的数据集，方法非常多，可以新建一个表（临时表），循环插入数据就可以实现，也可以用UNION并举所有的数字，也可以通过数据集的笛卡儿集得到等等。

但题目要求“在数据库中没有任何可知数据时，通过一条SQL语句”，通过临时表得到不可起；完全通过select 1 union all select 2…并举如数据量比较大SQL语句就非常的长，也不太好；完全通过数据集的笛卡儿集得到，数据集如果没有，就不可能通过笛卡儿集来得到。可以通过UNION并举一部分数据得到数据集，在笛卡儿集数字相加就可以得到连续数字的数据集，这样做也可以同时放到一条SQL语句中来实现，和数据库中有没有数据也没有关系，满足题目的要求。

实现：

从1到10000的数据集

select t1.a + t2.a + t3.a + t4.a + 1 as a

from  (select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9

) as t1,

(select 0 as a union all select 10 union all select 20 union all

select 30 union all select 40 union all select 50 union all

select 60 union all select 70 union all select 80 union all

select 90

) as t2,

(select 0 as a union all select 100 union all select 200 union all

select 300 union all select 400 union all select 500 union all

select 600 union all select 700 union all select 800 union all

select 900

) as t3,

(select 0 as a union all select 1000 union all select 2000 union all

select 300 union all select 4000 union all select 5000 union all

select 6000 union all select 7000 union all select 8000 union all

select 9000

) as t4

order by t1.a + t2.a + t3.a + t4.a

也可以这样写

select t1.a + t2.a * 10 + t3.a * 100 + t4.a * 1000 + 1 as a

from  (select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9

) as t1,

(select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9

)  as t2,

(select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9

)  as t3,

(select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5 union all

select 6 union all select 7 union all select 8 union all

select 9

) as t4

order by t1.a + t2.a * 10 + t3.a * 100 + t4.a * 1000

也可以这样写

select t1.a + t2.a * 3 + t3.a * 9 + t4.a * 27 + t5.a * 81 + t6.a * 243 + t7.a * 729 + t8.a * 2187 + t9.a * 6561 + 1 as a

from (select 0 as a union all select 1 union all select 2) as t1,

    (select 0 as a union all select 1 union all select 2) as t2,

    (select 0 as a union all select 1 union all select 2) as t3,

    (select 0 as a union all select 1 union all select 2) as t4,

    (select 0 as a union all select 1 union all select 2) as t5,

    (select 0 as a union all select 1 union all select 2) as t6,

    (select 0 as a union all select 1 union all select 2) as t7,

    (select 0 as a union all select 1 union all select 2) as t8,

    (select 0 as a union all select 1 union all select 2) as t9

where t1.a + t2.a * 3 + t3.a * 9 + t4.a * 27 + t5.a * 81 + t6.a * 243 + t7.a * 729 + t8.a * 2187 + t9.a * 6561 <= 10000

order by t1.a + t2.a * 3 + t3.a * 9 + t4.a * 27 + t5.a * 81 + t6.a * 243 + t7.a * 729 + t8.a * 2187 + t9.a * 6561

也可以这样写

select t.a

from (select t1.a + t2.a * 3 + t3.a * 9 + t4.a * 27 + t5.a * 81 + t6.a * 243 + t7.a * 729 + t8.a * 2187 + t9.a * 6561 + 1 as a

from (select 0 as a union all select 1 union all select 2) as t1,

(select 0 as a union all select 1 union all select 2) as t2,

(select 0 as a union all select 1 union all select 2) as t3,

(select 0 as a union all select 1 union all select 2) as t4,

(select 0 as a union all select 1 union all select 2) as t5,

(select 0 as a union all select 1 union all select 2) as t6,

 (select 0 as a union all select 1 union all select 2) as t7,

 (select 0 as a union all select 1 union all select 2) as t8,

(select 0 as a union all select 1 union all select 2) as t9

) t

where t.a <= 10000

order by t.a

得到数据的应用：

1、  得到一个月的所有的天数的数据库集（得到2005年3月的所有天的的数据库集）和上班天数的数据库集（得到2005年3月的上班天数的的数据库集）

2005年3月的所有天的的数据库集：

select Cast(’2005-03-1’ as datetime) + t1.a + t2.a * 6

from (select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5

)t1,

(select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5

)t2

where t1.a + t2.a * 6 < 31

order by t1.a + t2.a * 6

得到2005年3月的上班天数的的数据库集(星期天和星期六为休息, 星期天为星期的第一天)：

select Cast(’2005-03-1’ as datetime) + t1.a + t2.a * 6

from (select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5

)t1,

(select 0 as a union all select 1 union all select 2 union all

select 3 union all select 4 union all select 5

)t2

where t1.a + t2.a * 6 < 31 and

DATEPART(weekday,Cast('2005-03-1' as datetime) + t1.a + t2.a * 6) in(1, 7)

order by t1.a + t2.a * 6

2、  得到一个表[CREATE TABLE mytable(F_c char(1), F_Name varchar(31) NULL);

insert into mytable(F_c)values('a');

insert into mytable(F_c)values('b');

insert into mytable(F_c)values('d');

insert into mytable(F_c)values('e');

insert into mytable(F_c)values('i');

insert into mytable(F_c)values('N');]

求在表中没有的数据连续，及

F

g

h

g

k

l

m

ASCII(‘a’)为97

select t.a

  from

       (select char(97 + t1.a + t2.a * 6) a

          from (select 0 as a union all select 1 union all select 2 union all

                 select 3 union all select 4 union all select 5

               )t1,

               (select 0 as a union all select 1 union all select 2 union all

                select 3 union all select 4

               )t2

         where t1.a + t2.a * 6 < 26

       )t

 where t.a not in (select F_c from mytable) and t.a < (select Max(F_c) from mytable)

order by a

以上SQL语句都在SQL SERVER 中执行过，但没有完全优化，在ORACLE上思路也是一样的；对文章的某一部分和几部分欢迎提出来讨论，这些SQL语句优化可能做的也不够，也可以讨论；如发现错误，欢迎指教，谢谢。

最后看看这段代码：

DECLARE @MaxI int set @MaxI = 1000000--需要的最大的数

DECLARE @forI int;

  select @fori = ROUND(LOG10(@MaxI) + 0.499999999999999, 0)

DECLARE @i int set @i = 1;

DECLARE @Temp int

IF OBject_Id('Tempdb..#t') IS NOT NULL DROP TABLE #t

create table #t (a int)

insert into #t(a)

 select 0 union all select 1 union all select 2 union all select 3 union all select 4

 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9

while @i < @fori

begin

  set @Temp = POWER(10, @i);

  set @i = @i + 1;

  insert into #t(a)

     select f1.a + 10 * f2.a

      from 

           (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4

            union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f1,

           #t f2

     where f1.a + 10 * f2.a >= @Temp and f1.a + 10 * f2.a < @MaxI

    --order by f1.a + 10 * f2.a

end;

select a + 1 from #t order by a