sql 查询多张表

select distinct i_id,ss_name,sum(cast(ss_score as float)) as score from BID_EVAL_SUPPLIER_SCORE where i_id in
(SELECt i_id FROM BID_EVAL_ITEMS  WHERe I_ISEND='3')  group by ss_name,i_id

select i_id,s_name2,s_name from BID_EVAL_SUPPLIERS where i_id in
(SELECt i_id FROM BID_EVAL_ITEMS  WHERe I_ISEND='3')

要求第一张图 ss_name下的“供应商1-供应商10”都换成第二张图 s_name “供应商1-供应商10”对应s_name2的名称 

除了“供应商1-供应商10”以外的名字不变

sql2000的数据库

declare @sname varchar(50),@sname2 varchar(50)

declare a cursor for

select s_name2,s_name

from BID_EVAL_SUPPLIERS

where s_name like '供应商%' and  i_id in (SELECt i_id FROM BID_EVAL_ITEMS  WHERe I_ISEND='3');

open a;

fetch a into @sname,@sname2;

while @@fetch_status=0

begin

update  BID_EVAL_SUPPLIER_SCORE set ss_name=@sname2

where ss_name=s_name and  i_id in (SELECT i_id FROM BID_EVAL_ITEMS  WHERe I_ISEND='3')

fetch a into @sname,@sname2;

end

close a;

deallocate a

这样应该可以不过你的确保只有供应商1～10,同时吧把@sname,@sname2变量根据你实际字段s_name,s_name2大小定

用游标吧，思路就是，在第一张表中找出like ‘供应商%' ，然后在第二张表中找到，对应的真实名字， 然后更新到第一张表中 

我的理解是不要图1中供应商1这样的名字，而换成具体的公司名，不知道是不是这样

写个SQL

select i_id, sum(case when s_name like '供应商%' then s_name2 else s_name) as sname, sum(cast(ss_score as float)) as score  from BID_EVAL_SUPPLIER_SCORE where i_id in
(SELECt i_id FROM BID_EVAL_ITEMS  WHERe I_ISEND='3')  group by i_id;