T1
id name linkman bealong
1 a 1d
2 b 2sd
3 a 5g
4 b 6fg
5 c 8jfg
6 a 9qw
表结构如上,通过 group by(name) 之后 给name编号相同得name,bealong也相同,bealong字段最好从数字1开始递增
要得到的表是下面的
id name linkman bealong
1 a 1d 1
2 b 2sd 2
3 a 5g 1
4 b 6fg 2
5 c 8jfg 3
6 a 9qw 1
id name linkman bealong
1 a 1d
2 b 2sd
3 a 5g
4 b 6fg
5 c 8jfg
6 a 9qw
表结构如上,通过 group by(name) 之后 给name编号相同得name,bealong也相同,bealong字段最好从数字1开始递增
要得到的表是下面的
id name linkman bealong
1 a 1d 1
2 b 2sd 2
3 a 5g 1
4 b 6fg 2
5 c 8jfg 3
6 a 9qw 1
(select count(distinct name) from T1 where name<=a.name) as bealong
from T1 a
/*
id name linkman bealong
1 a 1d
2 b 2sd
3 a 5g
4 b 6fg
5 c 8jfg
6 a 9qw
*/declare @temp table
( id int not null,
name varchar(8) not null,
linkman varchar(8) not null,
primary key (id))insert into @temp
select 1,'a','1d'
union all
select 2,'b','2sd'
union all
select 3,'a','5g'
union all
select 4,'b','6fg'
select a.*,b.bealong from @temp a left join
(
select name, ROW_NUMBER() OVER(order by [name] asc) as bealong from @temp
group by name
) b on a.name = b.name
set
bealong=(select count(distinct name) from T1 b where b.id<=(select min(id) from T1 where name=a.name))
from
T1 aselect * from T1
insert into @T1(id,name,linkman) values(1,'a','1d ')
insert into @T1(id,name,linkman) values(2,'b','2sd ')
insert into @T1(id,name,linkman) values(3,'a','5g ')
insert into @T1(id,name,linkman) values(4,'b','6fg ')
insert into @T1(id,name,linkman) values(5,'c','8jfg')
insert into @T1(id,name,linkman) values(6,'a','9qw ')update a
set
bealong=(select count(distinct name) from @T1 b where b.id<=(select min(id) from @T1 where name=a.name))
from
@T1 aselect * from @T1/*
id name linkman bealong
----------- -------------------- -------------------- -----------
1 a 1d 1
2 b 2sd 2
3 a 5g 1
4 b 6fg 2
5 c 8jfg 3
6 a 9qw 1
*/
into ##tmp
from T1
group by name
update T1
set bealong = cnt
from ##tmp
where T1.name = ##tmp.name
drop table ##tmp
(select count(distinct name) from T1 where name<=a.name) as bealong
from T1 a
这样行吗?
select *,bealong=ceiling(convert(float,(identity(2,1)/2)) from Ti group by name
if object_id('T1') is not null
drop table T1
go
create table T1
( id int,
name nvarchar(10),
linkman nvarchar(10),
bealong nvarchar(10)
)
insert into T1 select 1,'a','1d',''
union all select 2,'b','2sd',''
union all select 3,'a','5g',''
union all select 4,'b','6fg',''
union all select 5,'c','8jfg',''
union all select 6,'a','9qw',''
select name ,identity(int,1,1) as bealong into Tab12 from T1 group by name
select T1.id,T1.name,Tab12.bealong from T1 inner join Tab12
on T1.name = Tab12.name