34,588
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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 [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 行受影响)
*/
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 行受影响)
*/
select *,ROW_NUMBER()OVER (PARTITION BY [c2] ORDER BY [c2]) AS C3
from ta
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