我正在处理一个查询,在其中我需要添加一列,其任务名称包含一对多关系中存在的许多任务的数量
这是我的查询
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
我尝试添加 计数 和 分组依据, 但现在我正在猜测
谢谢你的帮助
使用派生表,如下所示:
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连接,并最大程度地防止不必要的重复。 __