根据table1的tb1.id和tb2.field选择count(tb2.field_name) [英] selecting count(tb2.field_name) based on tb1.id of the table1 and tb2.field

问题描述

可能以前曾问过这个问题，但我找不到.因为我无法正确询问所有情况.所以请原谅我是一个重复的问题. 我有2张桌子: tb1-类别 tb2-任务 我想按每个类别选择所有任务和任务数量.

this question might have been asked before, but I could not find. because I was not able to ask correctly all situations. so pardon me if it is a repeated question. I have 2 tables: tb1 - categories tb2 - tasks I want to select all tasks and quantity of tasks by each category.

这是我做的:

SELECT category_id, category_title, 
       (SELECT count(task_id) FROM tasks_of_app WHERE category_id ) AS counted_tasks 
FROM categories_of_app

但是我得到每个类别的所有任务的数量.

but I get all tasks' quantity for each category.

我无法使用WHERE，因为没有输入参数.

I can't use WHERE because there is not input param.

请大家帮忙或说出要读的书，以便进行此类查询.

please, guys help out or tell what book to read to be able to make such kind of queries.

推荐答案

WHERE category_id需要指定category_id应该是什么.您需要将其与另一个表中的值进行匹配，以使其成为关联的子查询.

WHERE category_id needs to specify what category_id should be. You need to match it with the value from the other table to make it a correlated subquery.

SELECT category_id, category_title, 
       (SELECT count(task_id) 
        FROM tasks_of_app 
        WHERE tasks_of_app.category_id = categories_of_app.category_id) AS counted_tasks 
FROM categories_of_app

但是写一个更好的方法是在表之间使用LEFT JOIN.

But a better way to write this is as a LEFT JOIN between the tables.

SELECT c.category_id, c.category_title, COUNT(t.task_id) AS counted_tasks
FROM categories_of_app AS c
LEFT JOIN tasks_of_app AS t ON c.category_id = t.category_id
GROUP BY c.category_id

这篇关于根据table1的tb1.id和tb2.field选择count(tb2.field_name)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！