能不能实现类似ROWNUM 的功能？

[nowthwind25]

能不能实现类似ROWNUM 的功能？

Dept ROWNUM Name
Sale 1 OO
Sale 2 OS
Manage 3 M1
Manage 4 M2

我想要是...

Dept ROWNUM Name
Sale 1 OO
Sale 2 OS
Manage 1 M1
Manage 2 M2

[superhsj]

SELECT Dept, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS rownum,name
FROM table;

[bw555]

SELECT Dept,ROW_NUMBER()OVER (PARTITION BY Dept  ORDER BY name) AS rownum,name
FROM tab1

[pyxyu]

select 表.Dept, 表.ROWNUM-t.mRN-1 ROWNUM, 表.Name
from 表,(select Dept, min(ROWNUM) mRN from 表 group by Dept) t where 表.Dept=t.Dept

[nowthwind25]

[Quote=引用 1 楼 superhsj 的回复:]
SELECT Dept, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS rownum,name
FROM table;
[/Quote]

好，SQL ....

[bingkunchen]

[Quote=引用 1 楼 superhsj 的回复:]
SELECT Dept, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS rownum,name
FROM table;

[/Quote]
这个执行时会报错，因为ROWNUM是关键字不能做字段别名。

[fuyou001]

[Quote=引用 1 楼 superhsj 的回复:]
SELECT Dept, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS rownum,name
FROM table;
[/Quote]
你改成
SELECT Dept, ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Dept) AS rn,name
FROM table;