查询中的sql server变量增量

In MySQL I can do something like this:

在MySQL中，我可以这样做：

SELECT @row := @row + 1 as num FROM
(select 0 union all select 1) t1,
(select 0 union all select 1) t2,
(select 0 union all select 1) t3,
(select @row := 0) as t4;

The output of which is:

其输出是：

I tried to do this in sql server, but met many road blocks:

我试图在sql server中这样做，但遇到了很多路障：

First I tried this:

首先我尝试了这个：

SELECT * FROM
(select 0 union all select 1) t1,
(select 0 union all select 1) t2,
(select 0 union all select 1) t3;

and received:

并收到：

Msg 8155, Level 16, State 2, Line 7
No column name was specified for column 1 of 't1'.
Msg 8155, Level 16, State 2, Line 8
No column name was specified for column 1 of 't2'.
Msg 8155, Level 16, State 2, Line 9
No column name was specified for column 1 of 't3'.

So I did this:

所以我这样做了：

SELECT * FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

Why do I have to specify a name for column 1 on the derived tables?
为什么我必须在派生表上为列1指定名称？

Next, I tried to setup a scalar, I guessed I had to do it this way:

接下来，我试图设置一个标量，我猜我必须这样做：

DECLARE @row as int
SET @row = 0

I can do this:

我可以做这个：

SELECT @row = @row + 1

Which results in nothing back until I do SELECT @row which now shows a 1

在我执行SELECT @row之前没有任何结果，现在显示1

I cannot do (as I was in MySQL at the start):

我不能这样做（就像我一开始在MySQL中一样）：

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 as num FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

I get:

我明白了：

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 't1'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 't2'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 't3'.

How do I mimic the behavior of the first query (from MySQL) in sql server?
我如何模仿sql server中第一个查询（来自MySQL）的行为？

3 个解决方案

#1

Well, if you just want the row number, you could just use ROW_NUMBER() in TSQL;

好吧，如果你只想要行号，你可以在TSQL中使用ROW_NUMBER（）;

SELECT ROW_NUMBER() OVER (ORDER BY t1.f) num FROM
  (SELECT 0 AS f UNION ALL SELECT 1) t1,
  (SELECT 0 AS f UNION ALL SELECT 1) t2,
  (SELECT 0 AS f UNION ALL SELECT 1) t3;

That will return the row number for each combination. ROW_NUMBER() requires an ORDER BY, so just order by the first convenient column.

这将返回每个组合的行号。 ROW_NUMBER（）需要一个ORDER BY，所以只需按第一个方便的列排序。

SQLFiddle here.

SQLFiddle在这里。

#2

If I understand the intent, and if you have SQL Server 2005 or above, try this:

如果我理解了意图，并且您有SQL Server 2005或更高版本，请尝试以下操作：

select row_number() over(order by t1.f) as num from
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3,
(select 0 as f) as t4;

Yields:

产量：

num
--------------------
1
2
3
4
5
6
7
8

#3

Well, for this little query :

好吧，对于这个小问题：

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 as num FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

you try to give a name to variable, so setting AND outputting is what you want, that is not possible. you have to do the first or the last, not both..

你试图给变量命名，所以设置AND输出就是你想要的，这是不可能的。你必须做第一个或最后一个，而不是两个..

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

SELECT @row as num

#1