---------------------------------------------
--> 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
--> 测试数据：[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
*/
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
*/
-------------------------------------
-- 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 行受影响)
*/
