在 Select 中合并两个表 (SQL Server 2008)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9304984/
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
Combine Two Tables in Select (SQL Server 2008)
提问by Jesper
If I have two tables, like this for example:
如果我有两个表，例如这样：
Table 1 (products)
表1（产品）
id
name
price
agentid
Table 2 (agent)
表2（代理）
userid
name
email
How do I get a result set from products that include the agents name and email, meaning that products.agentid = agent.userid?
如何从包含代理名称和电子邮件的产品中获取结果集，这意味着products.agentid = agent.userid？
How do I join for example SELECT WHERE price < 100?
例如，我如何加入SELECT WHERE price < 100？
回答by Mosty Mostacho
Edited to support price filter
编辑以支持价格过滤器
You can use the INNER JOINclause to join those tables. It is done this way:
您可以使用INNER JOIN子句连接这些表。它是这样完成的：
select p.id, p.name as ProductName, a.userid, a.name as AgentName
from products p
inner join agents a on a.userid = p.agentid
where p.price < 100
Another way to do this is by a WHEREclause:
另一种方法是通过WHERE子句：
select p.id, p.name as ProductName, a.userid, a.name as AgentName
from products p, agents a
where a.userid = p.agentid and p.price < 100
Note in the second case you are making a natural product of all rows from both tables and then filtering the result. In the first case you are directly filtering the result while joining in the same step. The DBMS will understand your intentions (regardless of the way you choose to solve this) and handle it in the fastest way.
请注意，在第二种情况下，您正在对两个表中的所有行进行自然乘积，然后过滤结果。在第一种情况下，您在加入同一步骤时直接过滤结果。DBMS 将了解您的意图（无论您选择以何种方式解决此问题）并以最快的方式处理它。
回答by Michael Berkowski
This is a very rudimentary INNER JOIN:
这是一个非常基本的INNER JOIN：
SELECT
products.name AS productname,
price,
agent.name AS agentname
email
FROM
products
INNER JOIN agent ON products.agentid = agent.userid
I recommend reviewing basic JOINsyntax and concepts. Here's a link to Microsoft's documentation, though what you have above is pretty universal as standard SQL.
我建议复习基本的JOIN语法和概念。这是指向 Microsoft 文档的链接，尽管您上面的内容与标准 SQL 一样通用。
Note that the INNER JOINhere assumes every product has an associated agentidthat isn't NULL. If there are NULL agentidin products, use LEFT OUTER JOINinstead to return even the products with no agent.
请注意，INNER JOIN此处假定每个产品都有一个agentid非 NULL的关联。如果有NULLagentid中products，使用LEFT OUTER JOIN而不是无代理返回连产品。
回答by Vikram K
This is my join for slightly larger tables in Prod.Hope it helps.
这是我对 Prod 中稍大表的连接。希望它有所帮助。
SELECT TOP 1000 p.[id]
,p.[attributeId]
,p.[name] as PropertyName
,p.[description]
,p.[active],
a.[appId],
a.[activityId],
a.[Name] as AttributeName
FROM [XYZ.Gamification.V2B13.Full].[dbo].[ADM_attributeProperty] p
Inner join [XYZ.Gamification.V2B13.Full].[dbo].[ADM_activityAttribute] a
on a.id=p.attributeId
where a.appId=23098;
回答by Thit Lwin Oo
select p.name productname, p.price, a.name as agent_name, a.email
from products p
inner join agent a on (a.userid = p.agentid)
回答by John Dewey
select ProductName=p.[name]
, ProductPrice=p.price
, AgentName=a.[name]
, AgentEmail=a.email
from products p
inner join agent a on a.userid=p.agentid
