表Aguigeid shangpinid yanse chima
----------- ----------- ---------- ---------
65501 1211 白/金 NS
103497 1211 白/金 NS
65500 1211 黑/金 NS
103496 1211 黑/金 NS
65525 1233 粉/黑 NS
103508 1233 粉/黑 NS
65524 1233 蓝 NS
103507 1233 蓝 NS
65526 1233 绿 NS
103509 1233 绿 NS
103506 1234 NS
65527 1234 NS
65528 1234 NS表Bautoid guigeid
----------- -----------
1196 65501
1264 65500
1306 65525
1317 65524
1327 103508
1336 103509
1371 65527修改表B的信息:
表A根据shangPinID、yanSe、chiMa都相等的信息会有一到多个guiGeID
表B里也有对应的guigeid
将表B的guigeid修改到对应表A中最大的一个guiGeID(要求shangPinID、yanSe、chiMa都相等)例如:表B修改后的结果为:autoid guigeid
----------- -----------
1196 103497
1264 103496
1306 103508
1317 103507
1327 103508
1336 103509
1371 103506哪位高手知道怎么解决
帮帮我,谢谢
----------- ----------- ---------- ---------
65501 1211 白/金 NS
103497 1211 白/金 NS
65500 1211 黑/金 NS
103496 1211 黑/金 NS
65525 1233 粉/黑 NS
103508 1233 粉/黑 NS
65524 1233 蓝 NS
103507 1233 蓝 NS
65526 1233 绿 NS
103509 1233 绿 NS
103506 1234 NS
65527 1234 NS
65528 1234 NS表Bautoid guigeid
----------- -----------
1196 65501
1264 65500
1306 65525
1317 65524
1327 103508
1336 103509
1371 65527修改表B的信息:
表A根据shangPinID、yanSe、chiMa都相等的信息会有一到多个guiGeID
表B里也有对应的guigeid
将表B的guigeid修改到对应表A中最大的一个guiGeID(要求shangPinID、yanSe、chiMa都相等)例如:表B修改后的结果为:autoid guigeid
----------- -----------
1196 103497
1264 103496
1306 103508
1317 103507
1327 103508
1336 103509
1371 103506哪位高手知道怎么解决
帮帮我,谢谢
set guigeid = m.guigeid
from b , a n,
(select t.* from a t where guigeid = (select max(guigeid) from a where shangpinid = t.shangpinid and yanse = t.yanse and chima = t.chima)) m
where b.guigeid = n.guigeid and n.shangpinid = m.shangpinid and n.yanse = m.yanse and n.chima = m.chima
insert into a values(65501 , '1211' , '白/金' , 'NS')
insert into a values(103497 , '1211' , '白/金' , 'NS')
insert into a values(65500 , '1211' , '黑/金' , 'NS')
insert into a values(103496 , '1211' , '黑/金' , 'NS')
insert into a values(65525 , '1233' , '粉/黑' , 'NS')
insert into a values(103508 , '1233' , '粉/黑' , 'NS')
insert into a values(65524 , '1233' , '蓝' , 'NS')
insert into a values(103507 , '1233' , '蓝' , 'NS')
insert into a values(65526 , '1233' , '绿' , 'NS')
insert into a values(103509 , '1233' , '绿' , 'NS')
insert into a values(103506 , '1234' , '' , 'NS')
insert into a values(65527 , '1234' , '' , 'NS')
insert into a values(65528 , '1234' , '' , 'NS')
create table b(autoid int, guigeid int)
insert into b values(1196 , 65501)
insert into b values(1264 , 65500)
insert into b values(1306 , 65525)
insert into b values(1317 , 65524)
insert into b values(1327 , 103508)
insert into b values(1336 , 103509)
insert into b values(1371 , 65527)
goupdate b
set guigeid = m.guigeid
from b , a n,
(select t.* from a t where guigeid = (select max(guigeid) from a where shangpinid = t.shangpinid and yanse = t.yanse and chima = t.chima)) m
where b.guigeid = n.guigeid and n.shangpinid = m.shangpinid and n.yanse = m.yanse and n.chima = m.chimaselect * from bdrop table a , b/*
autoid guigeid
----------- -----------
1196 103497
1264 103496
1306 103508
1317 103507
1327 103508
1336 103509
1371 103506(所影响的行数为 7 行)*/
-- Author: T.O.P
-- Create date: 20091126
-- Version: SQL SERVER 2000
-- =============================================
declare @tb table([guigeid] int,[shangpinid] int,[yanse] varchar(5),[chima] varchar(2))
insert @tb
select 65501,1211,'白/金','NS' union all
select 103497,1211,'白/金','NS' union all
select 65500,1211,'黑/金','NS' union all
select 103496,1211,'黑/金','NS' union all
select 65525,1233,'粉/黑','NS' union all
select 103508,1233,'粉/黑','NS' union all
select 65524,1233,'蓝','NS' union all
select 103507,1233,'蓝','NS' union all
select 65526,1233,'绿','NS' union all
select 103509,1233,'绿','NS' union all
select 103506,1234,'','NS' union all
select 65527,1234,'','NS' union all
select 65528,1234,'','NS'declare @tb1 table([autoid] int,[guigeid] int)
insert @tb1
select 1196,65501 union all
select 1264,65500 union all
select 1306,65525 union all
select 1317,65524 union all
select 1327,103508 union all
select 1336,103509 union all
select 1371,65527update c
set c.guigeid = a.guigeid
from (
select *
from @tb a
where not exists(select 1 from @tb where a.shangpinid = shangpinid and a.yanse = yanse and a.chima = chima and a.guigeid<guigeid)
) a inner join @tb b on a.shangpinid = b.shangpinid and a.yanse = b.yanse and a.chima = b.chima
inner join @tb1 c on b.guigeid = c.guigeidselect * from @tb1
--测试结果:
/*autoid guigeid
----------- -----------
1196 103497
1264 103496
1306 103508
1317 103507
1327 103508
1336 103509
1371 103506(所影响的行数为 7 行)*/