如何从两个不同的表中获取数据? [英] How to get data from two diffenerent tables ?
本文介绍了如何从两个不同的表中获取数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
<b>I have a situation ,there are two tables
table1 :
col1 col2 col3
a b c
table2 :
id ,col
1 a
2 c
3 b and so on...
table 1 col1,col2,col3 data will be inserted into table 2 and generate id by identity.
Now I want ids of col1,col2 and col3 like
1,2,3 (a,b,c..id values) in a single select query.
Because already my job is taking more time for remaining scripts I dont want to burden more
Can any one help me out .
</b>
推荐答案
试试这个:
Try this:
DECLARE @src TABLE(col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30))
INSERT INTO @src (col1, col2, col3)
VALUES('a', 'b', 'c')
DECLARE @dst TABLE(id INT IDENTITY(1,1), col VARCHAR(30))
INSERT INTO @dst (col)
SELECT colValue AS col
FROM (
SELECT colValue, colDescription
FROM (
SELECT col1, col2, col3
FROM @src
) AS pvt
UNPIVOT(colValue FOR colDescription IN ([col1], [col2], [col3])) AS unpvt
) AS src
SELECT *
FROM @dst
结果:
Result:
1 a
2 b
3 c
魔法UNPIVOT [ ^ ]声明;)
以下解决方案基于OP的评论。
据我了解OP想要做什么,他/她想插入table3 id
而不是存储的值i n col1
, col2
, col3
等于 S值
。
The magic does UNPIVOT[^] statement ;)
Below solution is based on OP's comments.
As per i understand what OP wants to do, He/She want to insert into table3 id
's instead values stored in col1
, col2
, col3
which are equal to sValue
.
DECLARE @table1 TABLE (col1 VARCHAR(30), col2 VARCHAR(30), col3 VARCHAR(30))
INSERT INTO @table1 (col1, col2, col3)
VALUES('file_abc ', 'Dfile_xyz', 'Mfile_mnp'),
('file_afc', 'Dfile_xyz', 'Mfile_mnp'),
('file_afc', 'Dfile_acd', 'Mfile_oop')
DECLARE @table2 TABLE(id INT IDENTITY(1,1), sValue VARCHAR(30))
INSERT INTO @table2 (sValue)
VALUES ('file_afc'), ('Dfile_acd'),
('Mfile_mnp'), ('file_abc'),
('Dfile_xyz'), ('Mfile_oop')
DECLARE @table3 TABLE(tab3id INT IDENTITY(1,1), col1 INT, col2 INT, col3 INT)
--SELECT *
--FROM @table1
--SELECT *
--FROM @table2
INSERT INTO @table3 (col1, col2, col3)
SELECT (SELECT id FROM @table2 WHERE sValue = t1.col1) AS col1,
(SELECT id FROM @table2 WHERE sValue = t1.col2) AS col2,
(SELECT id FROM @table2 WHERE sValue = t1.col3) AS col3
FROM @table1 AS t1
SELECT *
FROM @table3
ORDER BY tab3id
注意:实现这一目标的方法很少。我向您展示了子查询的方法;)
[/ EDIT]
Note: There are few ways to achieve that. I showed you a way with subqueries ;)
[/EDIT]
insert into table3(col1,col2,col3)
(Select c1.id,c2.id,c3.id
from table1 t1
left join table2 c1 on c1.col=t1.col1
left join table2 c2 on c2.col=t1.col2
left join table2 c3 on c3.col=t1.col3
)
我试过这个.plz建议我是否符合我的要求?
I tried with this .plz suggest me is it fulfill my requirement?
这篇关于如何从两个不同的表中获取数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文