MySQL-加入和计算另一个表中的行 [英] MySQL - Join & Count rows from another table

问题描述

我有3张桌子，例如:
parent(id, name)
children(id, data, parent_id, timestamp)
table_votes(id, user_id, child_id)

I have 3 tables, like:
parent(id, name)
children(id, data, parent_id, timestamp)
table_votes(id, user_id, child_id)

我想从children表中获取具有特定parent_id的所有行，还显示每个table_votes中出现的次数.

I want to get all rows from children table that have a specific parent_id, showing also the count of occurences in table_votes for each one.

我尝试执行以下操作，但似乎不起作用，我想我错过了children中所有在table_votes中没有条目的行.

I try something like the following, but doesn't seem to work, I think I miss all rows from children that have no entry in table_votes

SELECT  
    `children`.`id`,  
    `children`.`data`,  
    `children`.`parent_id`,  
    `children`.`timestamp`,  
    COUNT(`v`.`children_id`)  
FROM `children` LEFT JOIN `table_votes` `v` ON `children`.`id` = `v`.`child_id`  
WHERE `children`.`parent_id` = 20 ORDER BY `timestamp` ASC  

有人暗示我在做什么错吗?

Any hints what am I doing wrong?

提前谢谢

推荐答案

可能的选项很少，其中之一是:

There are few possible options, one of them:

SELECT * ,
  (SELECT count(*)
   FROM `table_votes`
   WHERE `children`.`id` = `table_votes`.`child_id`) AS `Count`
FROM `children`
WHERE `parent_id` = 20

您也可以使用查询，但必须添加GROUP BY:

You can use your query as well, but will have to add GROUP BY:

SELECT  
 `children`.`id`,  
 `children`.`data`,  
 `children`.`parent_id`,  
 `children`.`timestamp`,  
 COUNT(`v`.`children_id`)  
FROM `children` LEFT JOIN `table_votes` `v` ON `children`.`id` = `v`.`child_id`  
WHERE `children`.`parent_id` = 20 
GROUP BY `children`.`id`, `children`.`data`, `children`.`parent_id`, `children`.`timestamp`,
ORDER BY `timestamp` ASC

这篇关于MySQL-加入和计算另一个表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！