sql 查询多张表

答案:5 悬赏:70 手机版

解决时间 2021-05-05 15:21

提问者网友：蔚蓝的太阳
2021-05-05 08:38

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的数据库

最佳答案

五星知识达人网友：不想翻身的咸鱼
2021-05-05 09:34

哥们你是不是江西的，我是江西的。如果你的"供应商1-10"是固定的，你可以这样写：select i_id,ss_name=case when ss_name='供应商1' then (select s_name2 from Table_2 where s_name='供应商1') when ss_name='供应商2' then (select s_name2 from Table_2 where s_name='供应商2') when ss_name='供应商3' then (select s_name2 from Table_2 where s_name='供应商3') when ss_name='供应商4' then (select s_name2 from Table_2 where s_name='供应商4') when ss_name='供应商5' then (select s_name2 from Table_2 where s_name='供应商5') when ss_name='供应商6' then (select s_name2 from Table_2 where s_name='供应商6') when ss_name='供应商7' then (select s_name2 from Table_2 where s_name='供应商7') when ss_name='供应商8' then (select s_name2 from Table_2 where s_name='供应商8') when ss_name='供应商9' then (select s_name2 from Table_2 where s_name='供应商9') else ss_name end,scorefrom Table_1
如果是不固定的，你可以这样写：select i_id,ss_name=case when ss_name<>'供应商10' and (select s_name2 from Table_2 where s_name=ss_name) is null then ss_name else (select s_name2 from Table_2 where s_name=ss_name) end,scorefrom Table_1
Table_1是你的第一张表，Table_2是第二张表。两次的查询结果完全一样，如下图：不知道可否是这种结果？

全部回答

1楼网友：佘樂
2021-05-05 12:55

追不到800分的。300顶了方法很多，asp 把13张表的表名放到数组里，再循环这个数组，动态生成SQL查询，sqlstr ="select top 30 * from " & tbname(i) & " order by orderid"如果用一句SQL查询得到的话select top 30 * from tb1 order by orderid union all select top 30* from tb2 order by orderid union all ....union allselect top 30 from tb13 order by orderid 另外不明白你是前30条记录是什么意思，上面写的是每个表的前30条，如果合起来后只列前30条就是seelct top * from (select * from tb1 union all select * from tb2union all ......union all select * from tb13 ) tx order by orderid 你可以把sql写到存储过程里，也可以用asp动态组合成句子，存储过程会优化查询，速度上可能会快一点。上面我写了order by orderid 你改成 order by [date]吧，就是你的字段名如果表字段有区别，把* 改你的字段名。

2楼网友：狂恋
2021-05-05 12:21

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大小定

3楼网友：底特律间谍
2021-05-05 11:08

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

4楼网友：上分大魔王
2021-05-05 10:05

我的理解是不要图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;

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息