在另一个where语句(子查询?)中使用来自一个sql查询的结果 [英] Use results from one sql query in another where statement (subquery?)
问题描述
我看到了许多类似的问题,但是它们要么太复杂,以至于我听不懂,要么似乎并没有问同样的事情.
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.
很简单:我有两列:用户(dmid)和下载(dfid).
It's simple: I have two columns: users (dmid) and downloads (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}
对dfid结果进行计数和排序,因此我们可以看到每个文件下载了多少次:
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
我尝试回答.
这似乎很接近,但是MySql陷入困境,甚至在30秒后也没有响应-我最终重新启动了Apache.而且,由于复杂的语句,我现在不知道如何在不引起语法错误的情况下构造计数和顺序,而且它甚至可能不是正确的语句.
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.
SELECT dfid from downloads_downloads WHERE dmid IN (
SELECT DISTINCT dmid FROM `downloads_downloads` where dfid = "7024")
推荐答案
SELECT dfid,count(*)
from downloads_downloads
WHERE dmid IN (
SELECT dmid
FROM downloads_downloads
where dfid = "7024"
)
group by dfid
或使用自我联接
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!)
这篇关于在另一个where语句(子查询?)中使用来自一个sql查询的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!