SQL 将参数传递给子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3905633/
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
passing a parameter into a subquery
提问by DPERROTT
i was wondering if it is possible to pass a parameter into a select subquery.
我想知道是否可以将参数传递给选择子查询。
What i want to do is collect some product data from one table and then crossref the weight of the item to it's carriage cost in a shipping table to return a cost.
我想要做的是从一张表中收集一些产品数据，然后将物品的重量与运输表中的运费交叉引用以返回成本。
something like:
就像是：
select cataloguenumber, productname,
(select shipping.carriagecost
from shipping
where shipping.carriageweight = weight) as carriagecost
from products
Regards
问候
DPERROTT
德佩罗特
回答by Noel Abrahams
While the subquery would work, a better, more readable and efficient way to define this would be as follows:
虽然子查询可以工作，但更好、更易读、更有效的定义方式如下：
SELECT p.cataloguenumber
, p.productname,
, s.carriagecost
FROM products p
INNER JOIN
shipping s
ON p.weight = s.carriageweight
This assumes that all product weights have a corresponding entry in the shipping table. If that is not the case then change from INNER JOINto LEFT JOINand deal with any nulls.
这假设所有产品重量在运输表中都有相应的条目。如果情况并非如此，则从INNER JOINto更改LEFT JOIN并处理任何空值。
回答by vc 74
select cataloguenumber, productname, shipping.carriagecost as carriagecost
from products, shipping
where shipping.carriageweight = products.weight
or am I missing something?
或者我错过了什么？
回答by Jagmag
SELECT DISTINCT cataloguenumber, productname, shipping.carriagecost
FROM products
LEFT OUTER JOIN shipping
ON shipping.carriageweight = products.weight
回答by thomaux
This is possible I think, but then you should retrieve the column you want to pass in your parent query.
我认为这是可能的，但是您应该检索要在父查询中传递的列。
select cataloguenumber, productname, weight
(select shipping.carriagecost
from shipping
where shipping.carriageweight = weight) as carriagecost
from products
回答by Numenor
Your subquery should only return 1 row, if it returns more then that your query will throw an error in run-time.
您的子查询应该只返回 1 行，如果它返回更多，那么您的查询将在运行时抛出错误。
回答by Shiraj Momin
SELECT DISTINCT products.cataloguenumber, products.productname, shipping.carriagecost
FROM products
LEFT JOIN shipping ON shipping.carriageweight = products.weight
