在SQL中使用表行連接值列表 - Joining a list of values with table rows in SQL -开发者知识库

在SQL中使用表行連接值列表 - Joining a list of values with table rows in SQL -开发者知识库,第1张

Suppose I have a list of values, such as 1, 2, 3, 4, 5 and 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 SELECT statement 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 JOIN between my list and the table, so the result would be like follows:

我想創建一個SELECT語句,它將包含列表中的所有值以及與這些值匹配的那些行的信息,即在我的列表和表之間執行LEFT OUTER JOIN,因此結果就像如下:

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 UNION operators?

如何在不創建臨時表或使用多個UNION運算符的情況下執行此操作?

5 个解决方案

#1


27  

If in Microsoft SQL Server 2008 or later, then you can use Table Value Constructor

如果在Microsoft SQL Server 2008或更高版本中,則可以使用表值構造函數

 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是否有類似的結構

最佳答案:

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复