id grp a
1 A 100
2 A 100
3 A
4 B 20
5 B
6 C 50
7 C
8 C
9 C 50
10 D
11 D 60
12 D
13 E 300
14 E 300
15 E 300
有这样一个表，id为主键grp为分组号，对于每个分组a列都应该有相同的值，但是现在有一些组中a列的值缺失了，需要参照已有的数据更新该表，填充a列的值，以下为要求的结果
id grp a
1 A 100
2 A 100
3 A 100
4 B 20
5 B 20
6 C 50
7 C 50
8 C 50
9 C 50
10 D 60
11 D 60
12 D 60
13 E 300
14 E 300
15 E 300
------解决方案--------------------
楼主结贴率老NB了
- SQL code
create table tb( id int, grp nvarchar(2), a int)insert into tb values(1,'A',100)insert into tb values(2,'A',100)insert into tb values(3,'A',null)insert into tb values(4,'B',20)insert into tb values(5,'B',null)insert into tb values(6,'C',50)insert into tb values(7,'C',null)insert into tb values(8,'C',null)insert into tb values(9,'C',50)insert into tb values(10,'D',null)insert into tb values(11,'D',60)insert into tb values(12,'D',null)insert into tb values(13,'E',300)insert into tb values(14,'E',300)insert into tb values(15,'E',300)update t1 set t1.a = t2.afrom tb t1,tb t2where t1.grp = t2.grp and isnull(t2.a,0) <> 0select * from tb/*id,grp,a1,A,1002,A,1003,A,1004,B,205,B,206,C,507,C,508,C,509,C,5010,D,6011,D,6012,D,6013,E,30014,E,30015,E,300(15 行受影响)*/