来自一列中的查询值的结果

I am just curios about something I've never come across in sql server before.

我只是对sql server中从未遇到过的东西感到好奇。

This query:

这个查询:

SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) T(N)

gives me result:

给我结果:

+---+
| N |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+

What is the rule here? Obviously this is aligning all values into one column. Is sql server's grammar that defines this with T(N)?

这里的规则是什么?显然，这是将所有值对齐到一列中。是sql server的语法用T(N)定义的吗?

On the other side, this query gives results by separate columns:

另一方面，该查询通过单独的列给出结果:

select 0,1,2,3,4,5,6,7,8,9

I just don't understand why results from the first query aligned all into one column?

我只是不明白为什么第一个查询的结果会排列成一列?

2 个解决方案

#1

James Z is right on the money, but to expand on what it does in the answer you were referencing:

詹姆斯·Z在金钱问题上是正确的，但是为了扩展你所提到的答案，

In the code that is pulled from, that section is used to start numbers table for a stacked cte. The numbers themselves don't matter, but I like them like that. They could all be 1, or 0, it would not change how it is used in this instance.

在从其中提取的代码中，该部分用于为堆积的cte启动数字表。数字本身并不重要，但我喜欢它们。它们都可以是1，也可以是0，它不会改变这个例子中的用法。

Basically we have 10 rows, and then we are going to cross join it to self N number of times to increase the row count until as many or more than we need. In the cross join I alias n with the resulting amount of rows deka is 10, hecto is 100, kilo is 1,000, et cetera.

基本上我们有10行，然后我们要把它与self连接N次以增加行数，直到行数超过我们需要的数量。在交叉连接I别名n中得到的行数deka是10,hecto是100,kilo是1000，等等。

Here is a similar query outside of the function that you were referencing:

这里有一个类似的查询，在你引用的函数之外:

declare @fromdate date = '20000101';
declare @years    int  = 30;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo 
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

The stacked cte is very efficient for generating or simulating a numbers or dates table, though using an actual numbers or calendar table will perform better as the scale increases.

堆叠的cte对于生成或模拟数字或日期表非常有效，尽管随着比例的增加，使用实际的数字或日历表将表现得更好。

Check these out for related benchmarks:

查看这些相关基准:

Generate a set or sequence without loops - 1 - Aaron Bertrand
生成一个没有循环的集合或序列- 1 - Aaron Bertrand
Generate a set or sequence without loops - 2 - Aaron Bertrand
生成一个没有循环的集合或序列——亚伦·伯特兰
Generate a set or sequence without loops - 3 - Aaron Bertrand
生成一个没有循环的集合或序列- 3 - Aaron Bertrand。

In hist articles, Aaron Bertrand creates a stacked cte using

在他的文章中，亚伦伯特兰创造了一个堆叠的cte使用

;WITH e1(n) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b),
....

#2

The values clause is similar what you can use in the insert statement, and it's called Table Value Constructor. Your example has only one column and several rows, but you can also have multiple columns separated by comma. The T(N) define you the alias name for the table (T) and name for the column (N).

值子句类似于insert语句中可以使用的语句，它被称为表值构造函数。您的示例只有一个列和几行，但您也可以使用逗号分隔多个列。T(N)定义了表的别名(T)和列(N)的名称。

#1