将值列表与 SQL 中的表行连接起来
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23377098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining a list of values with table rows in SQL
提问by uncoder
Suppose I have a list of values, such as 1, 2, 3, 4, 5and a table where some of those values exist in some column. Here is an example:
假设我有一个值列表，例如1, 2, 3, 4, 5一个表，其中某些值存在于某些列中。下面是一个例子：
id name
1 Alice
3 Cindy
5 Elmore
6 Felix
I want to create a SELECTstatement that will include all of the values from my list as well as the information from those rows that match the values, i.e., perform a LEFT OUTER JOINbetween my list and the table, so the result would be like follows:
我想创建一个SELECT语句，该语句将包含我的列表中的所有值以及与这些值匹配的行中的信息，即LEFT OUTER JOIN在我的列表和表之间执行 a ，因此结果如下所示：
id name
1 Alice
2 (null)
3 Cindy
4 (null)
5 Elmore
How do I do that without creating a temp table or using multiple UNIONoperators?
如何在不创建临时表或使用多个UNION运算符的情况下做到这一点？
回答by Charles Bretana
If in Microsoft SQL Server 2008 or later, then you can use Table Value Constructor
如果在 Microsoft SQL Server 2008 或更高版本中，则可以使用Table Value Constructor
Select v.valueId, m.name
From (values (1), (2), (3), (4), (5)) v(valueId)
left Join otherTable m
on m.id = v.valueId
Don't know if Oracle has similar construction
不知道oracle有没有类似的构造
回答by collapsar
the following solution for oracle is adopted from this source. the basic idea is to exploit oracle's hierarchical queries. you have to specify a maximum length of the list (100 in the sample query below).
以下 oracle 解决方案是从这个来源采用的。基本思想是利用oracle的分层查询。您必须指定列表的最大长度（在下面的示例查询中为 100）。
select d.lstid
, t.name
from (
select substr(
csv
, instr(csv,',',1,lev) + 1
, instr(csv,',',1,lev+1 )-instr(csv,',',1,lev)-1
) lstid
from (select ','||'1,2,3,4,5'||',' csv from dual)
, (select level lev from dual connect by level <= 100)
where lev <= length(csv)-length(replace(csv,','))-1
) d
left join test t on ( d.lstid = t.id )
;
check out this sql fiddleto see it work.
查看这个 sql fiddle看看它是否有效。
回答by EdmCoff
Bit late on this, but for Oracle you could do something like this to get a table of values:
有点晚了，但是对于 Oracle，您可以执行以下操作来获取值表：
SELECT rownum + 5 /*start*/ - 1 as myval
FROM dual
CONNECT BY LEVEL <= 100 /*end*/ - 5 /*start*/ + 1
... And then join that to your table:
...然后将其加入您的桌子：
SELECT *
FROM
(SELECT rownum + 1 /*start*/ - 1 myval
FROM dual
CONNECT BY LEVEL <= 5 /*end*/ - 1 /*start*/ + 1) mypseudotable
left outer join myothertable
on mypseudotable.myval = myothertable.correspondingval
回答by user353gre3
Assuming myTable is the name of your table, following code should work.
假设 myTable 是您的表的名称，以下代码应该可以工作。
;with x as
(
select top (select max(id) from [myTable]) number from [master]..spt_values
),
y as
(select row_number() over (order by x.number) as id
from x)
select y.id, t.name
from y left join myTable as t
on y.id = t.id;
Caution: This is SQL Server implementation.
注意：这是 SQL Server 实现。
回答by xbb
Suppose your table that has values 1,2,3,4,5is named list_of_values, and suppose the table that contain some values but has the name column as some_values, you can do:
假设您的具有值的表1,2,3,4,5名为list_of_values，并假设包含一些值但名称列为 的表some_values，您可以执行以下操作：
SELECT B.id,A.name
FROM [list_of_values] AS B
LEFT JOIN [some_values] AS A
ON B.ID = A.ID
回答by Amarnadh
For getting sequential numbers as required for part of output (This method eliminates values to type for n numbers):
用于获取部分输出所需的序列号（此方法消除了要键入 n 个数字的值）：
declare @site as int
set @site = 1
while @site<=200
begin
insert into ##table
values (@site)
set @site=@site+1
end
Final output[post above step]:
最终输出[post above step]：
select * from ##table
select v.id,m.name from ##table as v
left outer join [source_table] m
on m.id=v.id
