INNER JOIN (MySQL) 中的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20393685/
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
SubQuery in INNER JOIN (MySQL)
提问by Dee J. Doena
I seem to have a problem with a subquery in an inner join which doesn't do, what I'd.
我似乎对内部联接中的子查询有问题，但我不这样做。
- Server: Localhost via UNIX socket
- Software: MySQL
- Software version: 5.5.32-nmm2-log - (Ubuntu)
- Database client version: libmysql - 5.5.32
- 服务器：本地主机通过 UNIX 套接字
- 软件：MySQL
- 软件版本：5.5.32-nmm2-log - (Ubuntu)
- 数据库客户端版本：libmysql - 5.5.32
There's an m:n table construct with 3 tables of which only 2 are relevant to the problem.
有一个 m:n 表结构，其中包含 3 个表，其中只有 2 个与问题相关。
- The first oneis a table that contains actors, identifier: caid.
- The second oneis the cross-table connects actors to movies (actor id: caid, movie id: id)
- 第一个是包含演员的表，标识符：caid。
- 第二个是演员和电影的交叉表（演员id：caid，movie id：id）
When I create a full inner join like this:
当我像这样创建一个完整的内部连接时：
SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN DVDPROFILER_dvd_actor ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC
I get exactly what i'd expect: The top actors counted by the times he's credited in any movie, even if that's multiple times for multiple roles.
我得到了我所期望的：顶级演员按他在任何电影中获得的次数来计算，即使对于多个角色来说，这是多次。
My goal is to extract the information of how many different movies an actor is profiled in and I thought - naive as I am - it should be as simple as this:
我的目标是提取一个演员在多少部不同的电影中被描述的信息，我认为 - 虽然我很天真 - 它应该像这样简单：
SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN
(SELECT caid
FROM DVDPROFILER_dvd_actor
GROUP BY id) AS DVDPROFILER_dvd_actor
ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC
But the results where totally incorrect
但结果完全不正确
So I checked the subquery with a selected actor
所以我用选定的演员检查了子查询
SELECT caid, id
FROM DVDPROFILER_dvd_actor
WHERE caid = 30801
GROUP BY id
And got exactly what I expected
并且完全符合我的预期
So I toyed a bit and when I introduced a LIMIT clause into the subquery I suddenly got different yet never correct replies.
所以我玩了一下，当我在子查询中引入 LIMIT 子句时，我突然得到不同但从未正确的答复。
SELECT count( * ) AS Count, lastname, firstname
FROM DVDPROFILER_dvd_common_actor
INNER JOIN
(SELECT caid
FROM DVDPROFILER_dvd_actor
GROUP BY id
LIMIT 0 , 50000) AS DVDPROFILER_dvd_actor
ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY DVDPROFILER_dvd_actor.caid
ORDER BY Count DESC
With different LIMITs I got different results but at some point when I go over a certain limit, the result is exactly as it is without the limit - but equally wrong.
使用不同的 LIMIT，我得到了不同的结果，但是当我超过某个限制时，结果与没有限制时的结果完全一样——但同样是错误的。
What am I overlooking here? :-(
我在这里俯瞰什么？:-(
回答by Jason Heo
could you try this? I guess COUNT(DISTINCT DVDPROFILER_dvd_actor.id)is helpful for you.
你能试试这个吗？我想COUNT(DISTINCT DVDPROFILER_dvd_actor.id)对你有帮助。
SELECT lastname, firstname, COUNT(DISTINCT DVDPROFILER_dvd_actor.id) AS Count
FROM DVDPROFILER_dvd_common_actor
INNER JOIN DVDPROFILER_dvd_actor ON DVDPROFILER_dvd_common_actor.caid = DVDPROFILER_dvd_actor.caid
WHERE DVDPROFILER_dvd_actor.caid > 0
GROUP BY lastname, firstname
ORDER BY Count DESC
If not, we are very happy when you post your data and schema on http://www.sqlfiddle.com/. that makes us easier to test
如果没有，我们很高兴您在http://www.sqlfiddle.com/上发布您的数据和架构。这让我们更容易测试
Thanks.
谢谢。
