我似乎在内连接中有一个子查询有问题,但是我没有.
服务器:通过UNIX套接字的Localhost
软件:MySQL
软件版本:5.5.32-nmm2-log - (Ubuntu)
数据库客户端版本:libmysql - 5.5.32
有一个m:n表构造,有3个表,其中只有2个与问题相关.
第一个是包含actor的表,标识符:caid.
第二个是跨表连接演员到电影(演员ID:caid,电影ID:id)
当我像这样创建一个完整的内部联接:
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
我得到的正是我所期待的:顶级演员按照他在任何电影中记入的时间计算,即使多次担任多个角色也是如此.
我的目标是提取有关演员被分析的电影的数量的信息,我认为 - 我很天真 - 它应该像这样简单:
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
但结果完全不正确
所以我用一个选定的actor检查了子查询
SELECT caid, id FROM DVDPROFILER_dvd_actor WHERE caid = 30801 GROUP BY id
并得到我所期望的
所以我玩了一下,当我在子查询中引入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
对于不同的LIMIT,我得到了不同的结果,但在某些时候,当我超过一定限度时,结果完全没有限制 - 但同样错误.
我在这里俯瞰什么?:-(
你能试试吗?我想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
如果没有,我们非常高兴您在http://www.sqlfiddle.com/上发布数据和架构.这使我们更容易测试
谢谢.