增值表两张表 [英] 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屋！