如何从两个不同的表中获取数据？ [英] 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屋！