问题SQL语句的结果
问题描述:
我有以下情形使用SQL语句, 实现我分配到组分流,GX,GY一个问题:问题SQL语句的结果
组分流 - >组GX
组GX - >组分流
组分流 - >组GY
我想只提取第一次,当我的问题是分配到分诊组而忽略其他,我想这样做但总是不好的结果(回顾所有行当分配到分诊组问题)
SQL语句:
select g.created ,ji.pkey as issueName
from changegroup g
join changeitem ci on (ci.groupid = g.id)
join jiraissue ji on (ji.id = g.issueid)
join project p on (p.id = ji.project)
join priority pr on (pr.id = ji.priority)
where ci.field = 'Group'
and ci.oldString = 'Triage'
and p.pname = 'Test'
and pr.pname='P3'
and ji.created between '2011-08-11 14:01:00' and '2011-08-12 14:11:00'
语句的结果(正如你看到的,因此请求检索2行中的问题200被分配到组分流2次):
表changegroup具有这种结构:
表changeitem具有这样的结构:
答
试试这一个。但它可以很慢查询
select g.created, ji.pkey as issueName
from changegroup g
join jiraissue ji on (ji.id = g.issueid)
where (g.created, issueName) in (
select min(g.created) ,ji.pkey as issueName
from changegroup g
join changeitem ci on (ci.groupid = g.id)
join jiraissue ji on (ji.id = g.issueid)
join project p on (p.id = ji.project)
join priority pr on (pr.id = ji.priority)
where ci.field = 'Group'
and ci.oldString = 'Triage'
and p.pname = 'Test'
and pr.pname='P3'
and ji.created between '2011-08-11 14:01:00' and '2011-08-12 14:11:00'
group by issueName
)