增值表两张表 [英] adding value form two table
本文介绍了增值表两张表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
兼容型号表
compatable model Table
model1 model2 model3 model4 model5 model6 model7
10 8900 8520 9300 9500 9360 9630
11 8100 8300 NULL NULL NULL
备用表
spare Table
spareID SpareCode
1 A
2 B
另一个表SpareModel
another table SpareModel
SpareID model
1 10
1 8900
1 8520
1 9300
1 9500
我想从模型和备用表中添加SpareModel
第1行(spareID)兼容,对模型
i want to add in SpareModel from model and spare table
row 1 (spareID) compatable modle all value in row in model
推荐答案
中的行中的所有值进行模化.您可以将插入查询与select语句一起使用,以从多个表中选择值.
像这样的东西-
you can use insert queries with select statements to select values from multiple tables.
something like this -
insert into table1 select val1,val2... from table2 inner join table3 on table3.t1 = table2.t1
我认为这会有所帮助.
I think this would be helpful.
我认为以下代码块可以为您提供帮助..
Hi,
I think following code block can help you..
WITH MODCTE AS (
SELECT Model1,Model2,Model3,Model4,Model5,Model6,Model7,
Row_Number() OVER( ORDER BY Model1) ROWNUM FROM compatableModel
), ResultCTE AS (
SELECT SpareId,SpareCode,Model1,Model2,Model3,Model4,Model5,Model6,Model7
FROM (SELECT SpareId,SpareCode,Row_NUMBER() OVER(ORDER BY SpareId) ROWNUM FROM Spare
) A
JOIN MODCTE ON MODCTE.ROWNUM = A.ROWNUM
)
SELECT * INTO SpareModel FROM (
SELECT SpareId,Model,ModelNo FROM ResultCTE
UNPIVOT
(
ModelNo FOR Model IN
( Model1,Model2,Model3,Model4,Model5,Model6,Model7) ) As UP
) As A
SELECT * FROM SpareModel
这篇关于增值表两张表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文