如何使用"select"子句中的参数动态生成数据集"from"

Question

select * from 
(select 0 as hour 
union select 2  union select 4  union select 6  union select 8 
union select 10 union select 12 union select 14 union select 16 
union select 18 union select 20 union select 22 union select 24) hours
go

在上面的代码中，"小时"包含 2 的倍数小时。我需要写一些sql语句，如上面的例子，这些语句之间的区别在于它们是其他数字的倍数，例如3或4的倍数。所以我想用参数优化上面的sql语句，就像下面的伪代码一样：

#Below code creates "hours" with multiple of 3 from 0 to 24.
parameter = 3
select * from 
(select 0 as hour 
union select parameter  union select 2*parameter  union select 3*parameter
... ... 
union select 24) hours
go

但是我不知道如何用sql编写它，你能帮我吗？谢谢。

Answer 1

您可以使用递归 CTE：

with hours as (
      select 0 as hh
      union all
      select hh + @param
      from cte
      where hh + @param < 24
     )
select *
from hours;

如果您愿意，可以将其放入用户定义的表值函数中。 然后你可以称之为：

select . . .
from dbo.Hours(2) h(hh);

注意：hour是SQL Server中的保留字，因此我更喜欢使用hh作为列的名称。

Answer 2

我认为这应该是一个简单的表格和模数(%(的教科书用例。没有必要使用递归 CTE。我希望我的解决方案表现良好，而且很简单。

DECLARE @InputNum INT = 2 /*Sample parameter*/
SELECT hr
FROM (
        /*Values should probably be put into a physical table*/
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)
    ) AS A(hr)
WHERE hr % @InputNum = 0

返回：

hr
0
2
4
6
8
10
12
14
16
18
20
22
24

Answer 3

如果我正确阅读了您的问题，这将起作用：

对数字使用堆叠 cte(公用表表达式(：

declare @everyN int = 3;
declare @maxN   int = 24;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, numbers as (
  select n = row_number() over(order by (select 1))-1
  from n as deka cross join n as hecto cross join n as kilo 
                --cross join n as tenK cross join n as hundredK
)
select n
from numbers
where n <= @maxN
  and n % @everyN = 0;

Rextester 演示：http://rextester.com/PHZDZA97504

返回：

+----+
| n  |
+----+
|  0 |
|  3 |
|  6 |
|  9 |
| 12 |
| 15 |
| 18 |
| 21 |
| 24 |
+----+

Answer 4

对于任何类型的序列，在数据库中有一个所谓的"Tally"表都是有意义的。计数表仅包含从 0 到 x 的数字(例如 100'000(。基于此，您可以创建所需的任何类型的序列，无论是"3 小时"，例如您的案例或日期范围或其他数字范围。您将使用如下所示的计数表：

select Id * 3
  from dbo.Tally
 where Id * 3 <= 24;

或更好：

select Id * 3
  from dbo.Tally
 where Id <= 8;

当您需要在给定的开始日期和结束日期之间创建日期范围时，这非常有用：

select dateadd(day, Id, StartDate)
  from dbo.Tally
 where dateadd(day, Id, StartDate) <= EndDate;