sql - 聚合函数正在计算每个父代而不是最近的一个

Question

首先，我将展示我的表结构:

table work
 id  | scid | starttime  | project_id
-----+------+------------+----------------
 293 |    1 | 2013-10-11 |      235
 294 |    2 | 2013-10-15 |      235
 295 |    5 | 2013-10-16 |      236
 296 |    7 | 2013-10-28 |      236


table project
 id  |projecttype_id| description |            name
-----+--------------+-------------+----------------------------
 235 |            1 |             | Project_name1
 236 |            1 |             | Project_name2
 237 |            1 |             | Project_name3
 238 |            1 |             | Project_name4

table task
  id  |  work_id    | taskengine_id | severity | project_id
------+-------------+---------------+----------+----------
 8536 |         294 |             1 | 0        |      235
 8926 |         294 |             1 | 2        |      235
 8458 |         293 |             1 | 3        |      235
 8459 |         293 |             1 | 3        |      235
 8460 |         293 |             1 | 2        |      235
 8461 |         293 |             1 | 2        |      235
 8462 |         293 |             1 | 0        |      235
 8463 |         293 |             1 | 2        |      235
 8464 |         293 |             1 | 0        |      235
 8465 |         293 |             1 | 3        |      235

我想执行查询，该查询将计算分为严重性类别的任务。但主要标准是只计算最近的任务 work 我的意思是 work.starttime 的最新日期。

我有一个看起来像这样的查询:

select p.name as pname,
count(case when t.severity = '0' then 1 else null end) as zero, 
count(case when t.severity = '1' then 1 else null end) as one,  
count(case when t.severity = '2' then 1 else null end) as two,  
count(case when t.severity = '3' then 1 else null end) as three 
from project p, task t 
where p.id = t.project_id 
group p.name 
order p.name;

然而，通过这种方式，我一直在获取带有任务的Projects列表，我只想获取与最近的工作开始时间相关的列表.谁能给我一些提示，告诉我如何调整我的查询？

Answer 1

你需要加入最大的startime，

w.starttime = (SELECT max(starttime) from work w1 where w1.id = t.work_id)

然后

select p.name as pname,
count(case when t.severity = '0' then 1 else null end) as zero, 
count(case when t.severity = '1' then 1 else null end) as one,  
count(case when t.severity = '2' then 1 else null end) as two,  
count(case when t.severity = '3' then 1 else null end) as three 
from project p, task t, work w
where p.id = t.project_id 
and  p.id= w.project_id
and w.starttime = (SELECT max(starttime) from work w1 where w1.id = t.work_id)
group p.name 
order p.name;