求助：按表的某个列分类给序列号 请高手指点

[bersagliere005]

有一个表aa如下：
u id
aa 2
bb 7
hh 6
aa 8
uu 0
aa 77
bb 5
u 78
我想把它放到另一个表中 结果如下：
u id bh
aa 2 1
aa 8 1
aa 77 1
bb 7 2
bb 5 2
hh 6 3
u 78 4
uu 0 5
就是按u的编号分类，一类给一个编号，如上表
如和实现
请高手指点

[nononono]

select IDENTITY(int,1,1) as uid, u
into #t
from (select distinct u from t1) as a
order by u

select t1.u, t1.id a.uid
into t2
from t1 inner join #t as a on t1.u=a.u

[凨叔]

declare @tb_src table(u varchar(10), id int);
declare @tb_tmp table(bh int identity(1, 1), u varchar(10));

insert into @tb_src values('aa', 2);
insert into @tb_src values('bb', 7);
insert into @tb_src values('hh', 6);
insert into @tb_src values('aa', 8);
insert into @tb_src values('uu', 0);
insert into @tb_src values('aa', 77);
insert into @tb_src values('bb', 5);
insert into @tb_src values('u', 78);

insert into @tb_tmp(u) select distinct u from @tb_src order by u

select t1.u, t1.id, t2.bh
from @tb_src t1 inner join @tb_tmp t2 on t1.u=t2.u
order by t1.u

[冷箫轻笛]

insert into #表(u,id,bh)
select t0.u,t0.id,t3.bh
from aa t0 left join
(select distinct u,bh=(select count(distinct u) from aa t1 where t1.u <= t2.u)
from aa t2) t3
where t0.u = t3.u

[冷箫轻笛]

修改一下
insert into #表(u,id,bh)
select t0.u,t0.id,t3.bh
from aa t0 left join
(select distinct u,bh=(select count(distinct u) from aa t1 where t1.u <= t2.u)
from aa t2) t3
on t0.u = t3.u

[bersagliere005]

nononono(null,null) ：
你的方法好像不行啊
我不是只想导一次
我是想在一个触发器上实现啊

[samfeng_2003]

第一次需要把数据插入到表中，然后触发器来接管以后aa表中的数据变化就可以了！

[create] table aa(u varchar(10), id int)
insert into aa values('aa', 2);
insert into aa values('bb', 7);
insert into aa values('hh', 6);
insert into aa values('aa', 8);
insert into aa values('uu', 0);
insert into aa values('aa', 77);
insert into aa values('bb', 5);
insert into aa values('u', 78);



create table ad(bh int, u varchar(10),id int)


insert into ad
select a.a_id,a.u,b.id from (
select [a_id]=(select count(1) from (select u from aa group by u) a where a.u<=b.u),u
from (select u from aa group by u) b) a inner join aa b on a.u=b.u
go
create trigger t_aa
on aa
after insert,update,delete
as
begin
delete from ad
insert into ad
select a.a_id,a.u,b.id from (
select [a_id]=(select count(1) from (select u from aa group by u) a where a.u<=b.u),u
from (select u from aa group by u) b) a inner join aa b on a.u=b.u
end
go


insert into aa values('k', 90);
select * from ad
delete from aa where u='aa' and id=2
select * from ad

drop trigger t_aa

drop table aa
drop table ad


go

bh u id
----------- ---------- -----------
1 aa 2
1 aa 8
1 aa 77
2 bb 5
2 bb 7
3 hh 6
4 k 90
5 u 78
6 uu 0

（所影响的行数为 9 行）


（所影响的行数为 9 行）


（所影响的行数为 1 行）

bh u id
----------- ---------- -----------
1 aa 8
1 aa 77
2 bb 5
2 bb 7
3 hh 6
4 k 90
5 u 78
6 uu 0

（所影响的行数为 8 行）

[liujinqi2141]

不明白，谁能写一个确实调试通过的 方法给大家学习一下？？？？？？

[samfeng_2003]

你要测试的话，把[CREATE]的[]去掉！我刚才测试是通过了的啊！