IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta] GO CREATE TABLE [ta] ([c1] [int],[c2] [nvarchar](10)) INSERT INTO [ta] SELECT '1','aa' UNION ALL SELECT '2','aa' UNION ALL SELECT '3','aa' UNION ALL SELECT '4','bb' UNION ALL SELECT '5','bb'
select *,ROW_NUMBER()OVER (PARTITION BY [c2] ORDER BY [c2]) AS C3 from ta
2000的 ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-11-11 12:10:40 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([C1] int,[C2] varchar(2)) insert [tb] select 1,'aa' union all select 2,'aa' union all select 3,'aa' union all select 4,'bb' union all select 5,'bb' --------------开始查询-------------------------- ---2000 select *,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1) from tb t---2005 select *,c3=row_number()over(partition by c2 order by c1) from tb ----------------结果---------------------------- /* C1 C2 c3 ----------- ---- ----------- 1 aa 1 2 aa 2 3 aa 3 4 bb 1 5 bb 2(5 行受影响) */
create table [tb]([C1] int,[C2] varchar(2)) insert [tb] select 1,'aa' union all select 2,'aa' union all select 3,'aa' union all select 4,'bb' union all select 5,'bb' go select c1,c2,c1-(select min(c1) from tb where c2=a.c2)+1 as c3 from tb a go drop table tb /* c1 c2 c3 ----------- ---- ----------- 1 aa 1 2 aa 2 3 aa 3 4 bb 1 5 bb 2(5 行受影响) */
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([C1] int,[C2] varchar(2)) insert [tb] select 1,'aa' union all select 2,'aa' union all select 3,'aa' union all select 4,'bb' union all select 5,'bb'---2000 select *,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1) from tb t---2005,2008 select *,c3=row_number()over(partition by c2 order by c1) from tb /* C1 C2 c3 ----------- ---- ----------- 1 aa 1 2 aa 2 3 aa 3 4 bb 1 5 bb 2(5 行受影响) */
CREATE TABLE tt( id int, c2 varchar(10) )INSERT INTO tt SELECT 1,'aa' UNION ALL SELECT 2,'aa' UNION ALL SELECT 3,'aa' UNION ALL SELECT 4,'bb' UNION ALL SELECT 5,'bb' SELECT *,ROW_NUMBER() OVER(partition by c2 ORDER BY id) AS c3 FROM tt
DROP TABLE [ta]
GO
CREATE TABLE [ta] ([c1] [int],[c2] [nvarchar](10))
INSERT INTO [ta]
SELECT '1','aa' UNION ALL
SELECT '2','aa' UNION ALL
SELECT '3','aa' UNION ALL
SELECT '4','bb' UNION ALL
SELECT '5','bb'
from ta
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-11-11 12:10:40
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([C1] int,[C2] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'
--------------开始查询--------------------------
---2000
select
*,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1)
from
tb t---2005
select
*,c3=row_number()over(partition by c2 order by c1)
from
tb
----------------结果----------------------------
/* C1 C2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2(5 行受影响)
*/
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'
go
select c1,c2,c1-(select min(c1) from tb where c2=a.c2)+1 as c3 from tb a
go
drop table tb
/*
c1 c2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2(5 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([C1] int,[C2] varchar(2))
insert [tb]
select 1,'aa' union all
select 2,'aa' union all
select 3,'aa' union all
select 4,'bb' union all
select 5,'bb'---2000
select
*,c3=(select count(c2)+1 from tb where c2=t.c2 and c1<t.c1)
from
tb t---2005,2008
select
*,c3=row_number()over(partition by c2 order by c1)
from
tb
/* C1 C2 c3
----------- ---- -----------
1 aa 1
2 aa 2
3 aa 3
4 bb 1
5 bb 2(5 行受影响)
*/
id int,
c2 varchar(10)
)INSERT INTO tt
SELECT 1,'aa' UNION ALL
SELECT 2,'aa' UNION ALL
SELECT 3,'aa' UNION ALL
SELECT 4,'bb' UNION ALL
SELECT 5,'bb'
SELECT *,ROW_NUMBER() OVER(partition by c2 ORDER BY id) AS c3 FROM tt