在 Select 中合并两个表 (SQL Server 2008)
如果我有两个表，例如:
If I have two tables, like this for example:
表 1(产品)
id
name
price
agentid
表2(代理)
userid
name
email
如何从包含代理名称和电子邮件的产品中获取结果集，这意味着 products.agentid = agent.userid?
How do I get a result set from products that include the agents name and email, meaning that products.agentid = agent.userid?
我如何加入例如 SELECT WHERE price <100?
推荐答案
编辑以支持价格过滤
您可以使用 INNER JOIN 子句来连接这些表.它是这样完成的:
You can use the INNER JOIN clause to join those tables. It is done this way:
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
另一种方法是通过 WHERE 子句:
Another way to do this is by a WHERE clause:
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
请注意，在第二种情况下，您正在对两个表中的所有行进行自然乘积，然后过滤结果.在第一种情况下，您在加入同一步骤时直接过滤结果.DBMS 将了解您的意图(无论您选择以何种方式解决此问题)并以最快的方式处理.
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.
相关文章