只用SQL怎么能解决么?
一张发给LA的货单储存在tb_goods
id comp place name NO_1
1 LA 1-5687A 活节a 5874
2 LA A-B627A 活节b 5874
3 LA C-D637A 活节c 52274
4 LA 2-D687A 活节d 581174
由于位置码（place）第一位
开头为数字的在库存数据在表tb_1里
开头为字母的在库存数据在表tb_2里
其中NO_1就是在库存表（tb_1,tb_2）里的ID ，所以上面1，2号数据虽然NO_1一样，但是实际不一样
tb_1,tb_2表结构一样，NO_1＝ID
ID name text1 text2
现在想得到这个货单每个货带的text1和text2的规格文本，如下：
id comp place name NO_1 text1 text2
1 LA 1-5687A 活节a 5874
2 LA A-B627A 活节b 5874
3 LA C-D637A 活节c 52274
4 LA 2-D687A 活节d 581174
[解决办法]
seelct a.*,text1 = (case when isnull(b.text1, ' ')= ' ' then c.text1 else b.text1 end ),text2 = (case when isnull(b.text2, ' ')= ' ' then c.text2 else b.text2 end ) from tb_goods a left join tb_1 b on a.NO_1 = b.ID left join tb_2 c on a.NO_1 = c.ID
[解决办法]
select a.id,comp,place,name,[NO_1],text1,text2 from
tb_goods a inner join [tb_1] b on a.NO_1=b.id
Union all
select a.id,comp,place,name,[NO_1],text1,text2 from
tb_goods a inner join [tb_2] b on a.NO_1=b.id
[解决办法]
select a.id,a.comp,a.place,a.name,a.NO_1,b.text1,b.text2 from tb_goods a,tb_1 b where isnumeric(left(a.place,1)) = 1 and a.no_1 = b.id
union all
select a.id,a.comp,a.place,a.name,a.NO_1,b.text1,b.text2 from tb_goods a,tb_2 b where isnumeric(left(a.place,1)) = 0 and a.no_1 = b.id
[解决办法]
select t.*,
(case patindex( '%[0-9]% ', place) when 1 then a.text1 else b.text1 end) as text1,
(case patindex( '%[0-9]% ', place) when 1 then a.text2 else b.text2 end) as text2
from tb_goods t left join tb_1 a on t.NO_1 = a.ID
left join tb_2 b on t.NO_1 = b.ID
