与一对多表连接的mysql count（*）

I need to perform a COUNT on a quite a big query, where one of the joined tables has a one-to-many relationship. This is throwing off my result as all data is being multiplied by the number of times an item is repeated in the 'many' side of the one-to-many table.

我需要在一个相当大的查询上执行COUNT,其中一个连接表具有一对多关系。这会抛弃我的结果,因为所有数据都乘以在一对多表的“多”面中重复项目的次数。

This is a shortened version of the query showing only the relevant portion to highlight the issue:

这是查询的缩短版本,仅显示相关部分以突出显示问题:

SELECT COUNT(trimtype) FROM versiontrim 
INNER JOIN trims USING (trim_id) 
INNER JOIN prices USING(version_id) 
INNER JOIN m_versions USING(version_id)
WHERE trimtype IN('sec', 'help') AND price BETWEEN 200001 AND 210000 
GROUP BY version_id

All tables are quite straighforward except m_versions that has the one-to-many relationship and looks like this:

除了具有一对多关系的m_versions之外,所有表格都非常直观,如下所示:

version_id       serv_id

  1                 1
  1                 2
  1                 3
  1                 4
  1                 5
  .... and so on

The expected result of the query is :

查询的预期结果是:

version_id  COUNT(trimtype)     
44          9
54          7
69          9
214         10
216         6
282         1
290         10

Instead I am getting this,ie, all counts multiplied by 5 which is the number of times version_id is repeated in the m_versions table:

相反,我得到这个,即所有计数乘以5,这是在m_versions表中重复version_id的次数:

version_id  COUNT(trimtype)     
44         45
54          35
69          45
214         50
216         30
282         5
290         50

How to avoid this behavior? Thanks

如何避免这种行为?谢谢

1 个解决方案

#1

It matches to multiple records on table m_version that is why you are getting invalid result. Try wrapping it a subquery,

它匹配表m_version上的多个记录,这就是您获得无效结果的原因。尝试将其包装为子查询,

INNER JOIN (SELECT DISTINCT version_id FROM m_versions) m USING(version_id)

UPDATE

So the full query will look like this,

所以完整的查询将如下所示,

SELECT version_id, COUNT(trimtype) 
FROM   versiontrim 
       INNER JOIN trims USING (trim_id) 
       INNER JOIN prices USING(version_id) 
       INNER JOIN (SELECT DISTINCT version_id FROM m_versions) m USING(version_id)
WHERE  trimtype IN('sec', 'help') AND price BETWEEN 200001 AND 210000 
GROUP  BY version_id

#1