MySQL 如何将查询结果集与现有表连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2576770/
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
how can join a query result set with an existing table?
提问by Yang
is there any way to avoid using tmp table?
有什么办法可以避免使用 tmp 表吗？
I am using a query with aggregate function (sum) to generate the sum of each product: the result looks like this:
我正在使用带有聚合函数 (sum) 的查询来生成每个产品的总和：结果如下所示：
product_name | sum(qty)
product_1 | 100
product_2 | 200
product_5 | 300
now i want to join the above result to another table called products. so that i will have a summary like this:
现在我想将上述结果加入另一个名为 products 的表。这样我就会有一个这样的总结：
product_name | sum(qty)
product_1 | 100
product_2 | 200
product_3 | 0
product_4 | 0
product_5 | 300
i know 1 way of doing this is the dump the 1st query result to a temp table then join it with products table. is there a better way?
我知道这样做的一种方法是将第一个查询结果转储到临时表，然后将其与产品表连接。有没有更好的办法？
回答by egrunin
SELECT Product_Name, Total FROM ProductTable x
LEFT OUTER JOIN (SELECT SUM(qty) as Total, ProductID FROM InventoryTable
GROUP BY ProductID) y
ON x.ProductID = y.ProductID
回答by codingguy3000
You can do it like this
你可以这样做
select table1.productname, virtualtable.qty
from table1
inner join (
select productid, qty
from table2
group by productid
) as virtualtable on virtualtable.productid = table1.productid
回答by Rob Van Dam
Perhaps the UNION syntax is what you are looking for? http://dev.mysql.com/doc/refman/5.0/en/union.htmlSome more information would be useful.
也许 UNION 语法正是您要找的？http://dev.mysql.com/doc/refman/5.0/en/union.html一些更多的信息会很有用。
回答by Mike Daniels
You can do multiple joins in one selectquery. Does that solve your problem? It's hard to tell what you are asking for.
您可以在一个select查询中执行多个连接。这能解决你的问题吗？很难说你在要求什么。
