数据库难题!

[myheartzhang]

我看到一家公司出了如下一道数据库题,可能是太菜了,没有思路,想问一下大家的看法:

有表T,该表只有一列i,该列i信息(数据)如下:
1
NULL
2
3
8
用SQL语句求如下的结果集（一列）：
0
1
2
3
4
要求：使用一条语句得到结果（不得使用子查询）

[bancxc]

什么算法

[--小F--]

我也看不懂 哎 太菜了

[-狙击手-]

select row_number() over (order by getdate()) - 1
from t

[华夏小卒]

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-02 16:20:46
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @tb
declare @tb table (id int)
insert into @tb
select 1 union all
select null union all
select 2 union all
select 3 union all
select 8


---sql 2005
select px=row_number()over(order by getdate())-1 from @tb


px
--------------------
0
1
2
3
4

[xiequan2]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([col] int)
insert [t1]
select 1 union all
select null union all
select 2 union all
select 3 union all
select 8



 

select  col=(select count(1) from t1 where col<=t.col) from t1 t order by col
  
/*
col
-----------
0
1
2
3
4
*/

[foxmailxxx]

路过，看看，学习一下

[stevehxh]

CREATE TABLE #T1([data] int);
INSERT INTO #T1([data]) VALUES(1);
INSERT INTO #T1([data]) VALUES(NULL);
INSERT INTO #T1([data]) VALUES(2);
INSERT INTO #T1([data]) VALUES(3);
INSERT INTO #T1([data]) VALUES(8);
select * from #T1

SELECT (row_number() over (order by data)-1) as num from #T1

/*
结果：
num
--------------------
0
1
2
3
4
*/

[--小F--]

[liangCK]

-------------------------------------
--  Author : liangCK 梁爱兰
--  Comment: 小梁 爱 兰儿
--  Date   : 2009-12-02 17:25:34
-------------------------------------
 
--> 生成测试数据: @tb
DECLARE @tb TABLE (col INT)
INSERT INTO @tb
SELECT 1 UNION ALL
SELECT null UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 8

--SQL查询如下:


SELECT COUNT(*) - 1 AS col
FROM @tb AS A
    JOIN @tb AS B
ON A.col >= B.col OR B.col IS NULL
GROUP BY A.col

/*
col
-----------
0
1
2
3
4

(5 行受影响)

*/