在另一个 where 语句(子查询?)中使用一个 sql 查询的结果

2022-01-23 00:00:00 sql subquery mysql

我看到许多类似的问题，但它们要么太复杂以至于我无法理解，要么似乎问的不是同一个问题.

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

  1. 选择下载特定文件的所有用户:

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

    相关文章