计算一对多关系中存在多少个

admin

计算一对多关系中存在多少个

sql

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

这是我的查询

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

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

谢谢你的帮助

阅读 174

2021-07-01

共1个答案

admin

使用派生表，如下所示：

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

这是您对整个查询的摆弄

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

2021-07-01