下面是表结构和数据。/****** Object: Table [dbo].[TT] Script Date: 11/05/2007 20:52:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TT](
[a] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[b] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[c] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[d] [varchar](50) COLLATE Japanese_CI_AS NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[a] ASC,
[b] ASC,
[c] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF记录:
a b c d
____________________________a1 b1 c1 d
a1 b1 c2 d
a1 b1 c3 d
a1 b2 c4 d
a1 b2 c5 d
a2 b3 c6 d
******************我想要的结果是:***************
a b c id
____________________________
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b2 c4 1
a1 b2 c5 2
a2 b3 c6 1
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TT](
[a] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[b] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[c] [varchar](50) COLLATE Japanese_CI_AS NOT NULL,
[d] [varchar](50) COLLATE Japanese_CI_AS NULL,
CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
(
[a] ASC,
[b] ASC,
[c] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF记录:
a b c d
____________________________a1 b1 c1 d
a1 b1 c2 d
a1 b1 c3 d
a1 b2 c4 d
a1 b2 c5 d
a2 b3 c6 d
******************我想要的结果是:***************
a b c id
____________________________
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b2 c4 1
a1 b2 c5 2
a2 b3 c6 1
insert @t select 'a1','b1','c1','d'
insert @t select 'a1','b1','c2','d'
insert @t select 'a1','b1','c3','d'
insert @t select 'a1','b2','c4','d'
insert @t select 'a1','b2','c5','d'
insert @t select 'a2','b3','c6','d' select a,b,c,
(select count(1) from @t where a.a = a and a.b = b and c <= a.c) as count
from @t a/*
a b c count
---- ---- ---- -----------
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b2 c4 1
a1 b2 c5 2
a2 b3 c6 1(所影响的行数为 6 行)
*/
declare @TT table(a varchar(2),b varchar(2),c varchar(2),d varchar(1))
insert @TT
select 'a1','b1','c1','d' union all
select 'a1','b1','c2','d' union all
select 'a1','b1','c3','d' union all
select 'a1','b2','c4','d' union all
select 'a1','b2','c5','d' union all
select 'a2','b3','c6','d'select *, id = row_number() over (partition by a,b order by a) from @TT/*
a b c d id
---- ---- ---- ---- --------------------
a1 b1 c1 d 1
a1 b1 c2 d 2
a1 b1 c3 d 3
a1 b2 c4 d 1
a1 b2 c5 d 2
a2 b3 c6 d 1
*/
declare @TT table(a varchar(2),b varchar(2),c varchar(2),d varchar(1))
insert @TT
select 'a1','b1','c1','d' union all
select 'a1','b1','c2','d' union all
select 'a1','b1','c3','d' union all
select 'a1','b2','c4','d' union all
select 'a1','b2','c5','d' union all
select 'a2','b3','c6','d'/*
partition by a,b -> 分组
order by c ->分组编号顺序,如果顺序不敏感,可如上例 order by a
*/
select *, id = row_number() over (partition by a,b order by c) from @TT/*
a b c d id
---- ---- ---- ---- --------------------
a1 b1 c1 d 1
a1 b1 c2 d 2
a1 b1 c3 d 3
a1 b2 c4 d 1
a1 b2 c5 d 2
a2 b3 c6 d 1
*/
declare @t table(a char(2),b char(2),c char(2),d char(2))
insert @t select 'a1','b1','c1','d'
insert @t select 'a1','b1','c2','d'
insert @t select 'a1','b1','c3','d'
insert @t select 'a1','b2','c4','d'
insert @t select 'a1','b2','c5','d'
insert @t select 'a2','b3','c6','d' select *,ID=1 into # from @t order by a ,b asc
go
declare @a char(2),@b char(2),@i int
update #
set @i=case when a=@a and b=@b then @i+1 else 1 end,ID=@i,@a=a,@b=bgo
select a,b,c,ID from #
a b c ID
---- ---- ---- -----------
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b2 c4 1
a1 b2 c5 2
a2 b3 c6 1(所影响的行数为 6 行)--drop table #
declare @t table(a char(2),b char(2),c char(2),d char(2))
insert @t select 'a1','b1','c1','d'
insert @t select 'a1','b1','c2','d'
insert @t select 'a1','b1','c3','d'
insert @t select 'a1','b2','c4','d'
insert @t select 'a1','b2','c5','d'
insert @t select 'a2','b3','c6','d'
select a,b,c,bh=(select count(1) from @t where b=t1.b and c<t1.c)+1 from @t t1