如何从“select”的“from”子句中动态地生成一个数据集

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

In the above code, "hours" contains hours which are multiple of 2. I need to write some sql statements like above example, the difference between these statements is that they are multiple of other numbers, such as multiple of 3 or 4. So I want to optimize above sql statement with a parameter, like below pseudo code:

在上面的代码中，“hours”包含的小时数是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

But I don't how to write it with sql, could you help me? Thanks.

但是我不会用sql写，你能帮我吗?谢谢。

4 个解决方案

#1

This I believe should be a simple table and a textbook use case for modulus(%). There is no need to use recursive CTE's. I would expect my solution to perform well and it's simple.

我认为这应该是一个简单的表格和一个关于模数(%)的教科书用例。不需要使用递归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

Returns:

#2

You can use a recursive CTE:

您可以使用递归CTE:

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

If you like, you can put this into a user-defined table valued function. Then you could call it as:

如果您愿意，可以将其放入用户定义的表值函数中。那么你可以这样称呼它:

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

Note: hour is a reserved word in SQL Server, so I prefer using hh as the name of the column.

注意:在SQL Server中，hour是一个保留词，所以我更喜欢使用hh作为列的名称。

#3

If I am reading your question correctly, this would work:

如果我正确地阅读了你的问题，这是可行的:

Using a stacked cte (common table expression) for numbers:

对数字使用一个堆叠的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 demo: http://rextester.com/PHZDZA97504

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

returns:

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

#4

For any kind of sequence it makes sense to have a so-called "Tally" table in your database. A tally table just contains numbers from 0 to x (e. g. 100'000). Based on that, you can create whatever kind of sequence you need, be it "3 hours" like in your case or date ranges or other number ranges. You would use a tally table like so:

对于任何类型的序列，在数据库中都应该有一个所谓的“计数”表。计数表只包含从0到x的数字(例如10万)。在此基础上，您可以创建任何类型的序列，无论是“3小时”，就像您的案例或日期范围或其他数字范围。你会使用这样的理货表格:

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

or better:

或更好:

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

That's very useful when you need to create a date range between a given start and end date:

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

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

#1