SQL范围运算符

很有趣的问题。这是一个丑陋的黑客,除非你的范围真的很小,否则可能是无用的…

select * from (
  select 1 as num  UNION 
  select 2 as num  UNION 
  select 3 as num  UNION 
  select 4 as num  UNION 
  select 5 as num  UNION 
  select 6 as num  UNION 
  select 7 as num  UNION 
  select 8 as num  UNION 
  select 9 as num  UNION 
  select 10 as num
  ) t ;

+-----+
| num |
+-----+
|   1 | 
|   2 | 
  .... 
|   9 | 
|  10 | 
+-----+
10 rows in set (0.00 sec)

编辑:好的,所以我想为什么不使用交叉连接。所以,这里有另一个黑客,这个黑客很快就会在内存表中为您提供相当大的空间,而且可能相当不错。

select POW(2,0)*t0.num + POW(2,1)*t1.num + POW(2,2)*t2.num + POW(2,3)*t3.num  
as num 
from (
  select 0 as num  UNION 
  select 1 as num  
  ) t0, (
  select 0 as num  UNION 
  select 1 as num  
  ) t1, (
  select 0 as num  UNION 
  select 1 as num  
  ) t2, (
  select 0 as num  UNION 
  select 1 as num  
  ) t3 
order by num ;
+------+
| num  |
+------+
|    0 | 
|    1 | 
  ....
|   14 | 
|   15 | 
+------+
16 rows in set (0.00 sec)

很容易达到2的任何功率,而且速度应该足够快。

你在用什么数据库?between运算符通常是SQL的合法语法。我们一直在使用它作为日期范围。

从 http://www.w3schools.com/Sql/sql_between.asp

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

具有 SQL语言 你可以使用 热膨胀系数 :

;with numbs as
(
    select 1 as col 
    union all
    select col + 1 from numbs
    where col < 10
)

select * from numbs

倍增记录的SQL语法是 CROSS JOIN 我不确定你的要求和评论,但我想你是想这样做。

我做了一个类似的事情来填充一个日期戳为字符串“YYYYMM”的表,时间为20年。

您没有指定您使用的DBMS,但是使用 Oracle 你可以使用 CONNECT BY :

SELECT ROWNUM-1 i
FROM dual
CONNECT BY ROWNUM <= 10 + 1