MySql select里面另一个select？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4586472/
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
MySql select inside another select?
提问by XCS
Is there any way to do this?
有没有办法做到这一点？
SELECT sum(price) from table2 WHERE id=(SELECT theid FROM table1 WHERE user_id="myid")
I have table1 with items' IDs, that a user has purchased. I want to calculate the sum of all items purchased by user.
我有一个包含用户购买的项目 ID 的 table1。我想计算用户购买的所有商品的总和。
Is the query above legal? If not, what's the correct form?
以上查询是否合法？如果不是，正确的形式是什么？
回答by Matt
Change where id=(SELECTto where id IN (SELECT
更改where id=(SELECT为where id IN (SELECT
Or what you really want is probably:
或者你真正想要的可能是：
SELECT sum(price) FROM table2 INNER JOIN table1 ON table2.id = table1.theid WHERE table1.user_id = 'my_id'
回答by oezi
you query is ok, as long as the subselectis returning only one row every time.
你的查询没问题，只要子选择每次只返回一行。
if there are more rows returned, you'll have to change your query to:
如果返回更多行，则必须将查询更改为：
[...] WHERE id IN (SELECT [...]
NOTE:in you case, a simple inner join like others suggested would be much more redable (and maybe a tiny little bit faster) - but what you've written is absolutely ok (there are always multiple ways to get the desired result - and it's now always easy to tell wich one is "the best" ;-) )
注意：在你的情况下，像其他人建议的简单内部连接会更红（也许快一点） - 但你写的绝对没问题（总是有多种方法可以获得所需的结果 - 和现在总是很容易分辨哪个是“最好的”;-) )
回答by Sparky
You can also use the JOIN syntax
您还可以使用 JOIN 语法
SELECT sum(price) from table2 t2
join table1 t1 on t1.theID = t2.id
WHERE t1.user_id="myid"
Should give you the same result
应该给你同样的结果
回答by AhmetB - Google
You should use SQL JOINto provide that functionality.
您应该使用 SQLJOIN来提供该功能。
SELECT SUM(table2.price) JOIN table1 ON
table2.id=table1.theid WHERE table1.user_id="myid"
回答by Phil Hunt
A JOINwould be more readable:
AJOIN会更易读：
SELECT SUM(price) FROM table2
INNER JOIN table1 ON table2.id = table1.theid
WHERE table1.user_id = "myid"
