I have 2 tables like:
我有两张桌子:
ID NAME
-------------
1 someValue
2 someValue
3 someValue
4 someValue
...
ID NAME
-------------
1 var
2 var2
3 var3
4 var4
...
I want to insert in a @table
2 results from table1, and, 3 results from table2, with different ID, Currently I have something like:
我想在表1中插入一个@table 2的结果,从table2中得到3个结果,有不同的ID,现在我有一些类似的东西:
Declare @vars TABLE(
field1 varchar(80),
field2 varchar(80),
field3 varchar(80),
field4 varchar(80),
field5 varchar(80)
)
INSERT INTO @vars(field1, field2, field3, field4, field5)
SELECT a.Name,
b.Name,
c.Name,
d.Name,
e.Name
FROM @Table1 a,
@Table1 b,
@Table2 c,
@Table2 d,
@Table2 e
WHERE a.ID = 1
AND b.ID = 25
AND c.ID = 12
AND d.ID = 25
AND e.ID = 14
I get the expected values, but...
我得到了期望值,但是…
- Is there a more elegant way to do this? I guess I´m doing more than needed.
- 有更优雅的方法吗?我想我´m比需要做更多。
P.D. I get something like:
我得到的是:
field1 field2 field3 field4 field5
-------------------------------------
val1 var2 val4 val7 var7
2 个解决方案
#1
4
If ID is a primary key or unique key in Table1 and Table 2 you can do this instead.
如果ID是表1和表2中的主键或惟一键,则可以这样做。
insert into @vars
select
(select Name from @Table1 where ID = 1),
(select Name from @Table1 where ID = 25),
(select Name from @Table2 where ID = 12),
(select Name from @Table2 where ID = 25),
(select Name from @Table2 where ID = 14)
#2
1
I'm not sure if it's more elegant, but here's a different query that does the same.
我不确定它是否更优雅,但这里有一个不同的查询也做同样的事情。
WITH myTables AS
( SELECT ID, NAME, 'Table1' AS SOURCE
FROM Table1
UNION ALL
SELECT ID, NAME, 'Table2' AS SOURCE
FROM Table2
)
INSERT INTO @vars(field1, field2, field3, field4, field5)
SELECT MAX(CASE WHEN source = 'Table1' AND id = 1 THEN NAME END) AS field1,
MAX(CASE WHEN source = 'Table1' AND id = 25 THEN NAME END) AS field2,
MAX(CASE WHEN source = 'Table2' AND id = 12 THEN NAME END) AS field3,
MAX(CASE WHEN source = 'Table2' AND id = 25 THEN NAME END) AS field4,
MAX(CASE WHEN source = 'Table2' AND id = 14 THEN NAME END) AS field5
FROM myTables
WHERE SOURCE = ('Table1' AND ID IN (1, 25))
OR (SOURCE = 'Table2' AND ID IN (12, 25, 14)) ;
It uses a common table expression (CTE) to create a union of the two tables. It also pivots the data using the MAX aggregation. You can also do this with the PIVOT operator.
它使用公共表表达式(CTE)创建两个表的联合。它还使用最大聚合来透视数据。你也可以用主算子来做。
#1
4
If ID is a primary key or unique key in Table1 and Table 2 you can do this instead.
如果ID是表1和表2中的主键或惟一键,则可以这样做。
insert into @vars
select
(select Name from @Table1 where ID = 1),
(select Name from @Table1 where ID = 25),
(select Name from @Table2 where ID = 12),
(select Name from @Table2 where ID = 25),
(select Name from @Table2 where ID = 14)
#2
1
I'm not sure if it's more elegant, but here's a different query that does the same.
我不确定它是否更优雅,但这里有一个不同的查询也做同样的事情。
WITH myTables AS
( SELECT ID, NAME, 'Table1' AS SOURCE
FROM Table1
UNION ALL
SELECT ID, NAME, 'Table2' AS SOURCE
FROM Table2
)
INSERT INTO @vars(field1, field2, field3, field4, field5)
SELECT MAX(CASE WHEN source = 'Table1' AND id = 1 THEN NAME END) AS field1,
MAX(CASE WHEN source = 'Table1' AND id = 25 THEN NAME END) AS field2,
MAX(CASE WHEN source = 'Table2' AND id = 12 THEN NAME END) AS field3,
MAX(CASE WHEN source = 'Table2' AND id = 25 THEN NAME END) AS field4,
MAX(CASE WHEN source = 'Table2' AND id = 14 THEN NAME END) AS field5
FROM myTables
WHERE SOURCE = ('Table1' AND ID IN (1, 25))
OR (SOURCE = 'Table2' AND ID IN (12, 25, 14)) ;
It uses a common table expression (CTE) to create a union of the two tables. It also pivots the data using the MAX aggregation. You can also do this with the PIVOT operator.
它使用公共表表达式(CTE)创建两个表的联合。它还使用最大聚合来透视数据。你也可以用主算子来做。