Discussion :

[ramoucha23]

Bonjour
j'ai une requete qui répète un ligne plusieurs fois je ne sais pas pourquoi!

C'est mon requete , si veux pouvez m'aider en suivant la logique puisque je sais que la requete est un peu compliquée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
select   floor((a.created-c.created)*24)       || ' HOURS ' ||        mod(floor((a.created-c.created)*24*60),60)      || ' MINUTES ' ,a.pkey as AssignedGroupResponseTime,a.pkey,d.newString as AssignedGroup 
 from 
( select g.created,g.issueid as groupid1, ji.pkey ,ci.newString from   changegroup g join   jiraissue ji on (ji.id = g.issueid) join   changeitem ci on (ci.groupid = g.id)
 where (g.created, ji.pkey) in ( select min(g.created) ,ji.pkey 
 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 like 'Triage' 
 and  p.pname = 'project'
 and pr.pname='P3' 
and  ji.created between '01/03/2011' and  '16/08/2011' group by ji.pkey )) a left join 
  ( select ji.created, ji.id as groupid2 ,ji.pkey ,ci.newString     
 from   jiraissue ji     join   changegroup g on (g.issueid = ji.id)  
   join   changeitem ci on (ci.groupid = g.id) 
    join   project p on (p.id = ji.project)  
   where  p.pname = 'project'   
  and ci.field = 'Group'   
  and  ci.oldString like 'Triage'    
  and  ji.created between '01/03/2011' and  '16/08/2011'   ) b
 ON ( a.pkey = b.pkey)    
left join (   select g.created, g.issueid as groupid1 ,ji.pkey ,ci.newString     from   changegroup g  
   join   jiraissue ji on (ji.id = g.issueid)      
join   changeitem ci on (ci.groupid = g.id)    
 where (g.created, ji.pkey) in
 (  select min(g.created) ,ji.pkey     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='assignee'    
   and ci.newString is not NULL      
   and  p.pname = 'project' 
    and pr.pname='P3'   
   and ji.created between '01/03/2011' and  '16/08/2011'   
   group by ji.pkey)) c     
  ON (c.pkey = a.pkey)  
    left join (   
    select g.created, g.issueid as groupid1 ,ji.pkey ,ci.newString    
    from   changegroup g  
    join   jiraissue ji on (ji.id = g.issueid)   
    join   changeitem ci on (ci.groupid = g.id)    
    where (g.created, ji.pkey) 
    in (     select min(g.created) ,ji.pkey    
    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='assigned group'    
    and ci.newString is not NULL    
   and  p.pname = 'project'   
    and pr.pname='P3'   
    and  ji.created between '01/03/2011' and  '16/08/2011'    
   group by ji.pkey)) d   
   ON (d.pkey = c.pkey);

[islamov2000]

sans rentrer dans le detaille de ta requete, si tu as des lignes qui se repetent(identiques) tu utilise

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select DISTINCT.....

[Yanika_bzh]

Le DISTINCT est un pansement !

Il faut analyser la requete pour savoir ce qui provoque les retours multiples (Condition de jointure, prédicats érronés, mauvaises conditions de regroupement...).