计算一对多关系中存在多少个 [英] Count how many exist in a one-to-many relationship

问题描述

我正在处理一个查询，在该查询中，我需要添加一列带有任务名称的列，其中包含以一对多关系存在的许多任务的数量

I'm working on a query in which I need to add a column with the tasks name that contains the amount of many that exists in a one-to-many relationship

这是我的查询

SELECT 
    e.full_name AS fullName,
    t.issue AS issue,
    CASE t.state
        WHEN 'open' THEN 'open'
        WHEN 'pending' THEN 'In progress'
        WHEN 'closed' THEN 'closed'
    END AS status,
    CASE t.scheduled
        WHEN TRUE THEN 'scheduled'
        WHEN FALSE THEN 'non-scheduled'
    END AS scheduled,
    d.name AS device,
    DATE(t.date_created) AS dateCreated,
    DATE(t.last_updated) AS lastUpdate
FROM
    tickets t
        INNER JOIN
    employees e ON t.employee_id = e.id
        INNER JOIN
    devices d ON d.id = t.device_id
WHERE
    MONTHNAME(t.date_created) = 'August'
ORDER BY dateCreated DESC

这是我工作的方案 http://sqlfiddle.com/#！ 9/39bf3e/1

我尝试添加计数和分组依据，但目前我在猜测

I try to add counts and group by but at this point I'm guessing

我希望结果看起来像这样

I am expecting a result that looks like this

感谢您的帮助

推荐答案

使用派生表，如下所示:

Use a derived table like so:

SELECT ...
       TotalTasks --Add the count column to your select
FROM ticket t
JOIN (SELECT ticked_id, COUNT(1) as TotalTasks
      FROM tasks
      GROUP BY ticked_id) ta ON t.id = ta.ticked_id
...rest of query

这是您的小提琴和整个查询

这里的概念是在连接到 one 表之前，在许多表上进行汇总.这样可以确保1-1联接，并最大程度地防止不必要的重复.

The concept here is to do your aggregation on the many table, before joining back to the one table. This ensures a 1-1 join and best prevents unwanted duplication.

这篇关于计算一对多关系中存在多少个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！