在另一个where语句（子查询？）中使用一个sql查询的结果

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结果进行计数和排序，这样我们就可以看到每个文件收到的下载次数：
```
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")

1 个解决方案

#1

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!)

如果这需要太长时间，那么你可能需要发布一个解释计划（google it！）

#1

SELECT dfid,count(*) 
from downloads_downloads 
WHERE dmid IN (
    SELECT dmid 
    FROM downloads_downloads 
    where dfid = "7024"
)
group by dfid