MySQL 在另一个 where 语句（子查询？）中使用一个 sql 查询的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13779338/
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
Use results from one sql query in another where statement (subquery?)
提问by Josh Bond
I see many similar questions but they're either so complex I can't understand them, or they don't seem to be asking the same thing.
我看到许多类似的问题，但它们要么太复杂，我无法理解，要么他们似乎没有问同样的问题。
It's simple: I have two columns: users (dmid) and downloads (dfid).
很简单：我有两列：用户 (dmid) 和下载 (dfid)。
Select all users who downloaded a specific file:
SELECT DISTINCT dmid FROM downloads_downloads where dfid = "7024"Using the users above, find all the files they all downloaded:
SELECT dfid from downloads_downloads WHERE dmid = {user ids from #1 above}Count and order the dfid results , so we can see how many downloads each file received:
dfid dl_count_field ---- -------------- 18 103 3 77 903 66
选择下载特定文件的所有用户：
SELECT DISTINCT dmid FROM downloads_downloads where dfid = "7024"使用上面的用户，找到他们下载的所有文件：
SELECT dfid from downloads_downloads WHERE dmid = {user ids from #1 above}对 dfid 结果进行计数和排序，这样我们就可以看到每个文件收到了多少下载：
dfid dl_count_field ---- -------------- 18 103 3 77 903 66
My attempt at answering.
我试图回答。
This seems close, but MySql bogs down and doesn't respond even after 30 seconds--I restart Apache eventually. And I do not now how to structure the count and order by without getting syntax errors because of the complex statement--and it may not even be the right statement.
这似乎很接近，但 MySql 陷入困境并且即使在 30 秒后也没有响应——我最终重新启动了 Apache。而且我现在不知道如何构造计数和顺序而不会因为复杂的语句而出现语法错误——它甚至可能不是正确的语句。
SELECT dfid from downloads_downloads WHERE dmid IN (
SELECT DISTINCT dmid FROM `downloads_downloads` where dfid = "7024")
回答by Paul Creasey
SELECT dfid,count(*)
from downloads_downloads
WHERE dmid IN (
SELECT dmid
FROM downloads_downloads
where dfid = "7024"
)
group by dfid
or using a self join
或使用自连接
select t1.dfid,count(*)
from downloads_downloads t1
inner join downloads_downloads t2
on t1.dmid = t2.dmid
where t2.dfid = "7024"
if this takes too long then you will probably need to post an explain plan (google it!)
如果这需要太长时间，那么您可能需要发布一个解释计划（谷歌它！）
