Construction d'une requête

[nah_wah]

Bonjour,

j'ai deux tables (t1,t2) ayant la meme structure ,une autre (t3)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
t1: {num_ord,qtebase,cru,nat}
t2: {num_ord,qtebase,cru,nat}
t3:{cru}
 
je voudrais  pour chaque cru de t3 calculer la qtebase sachant que 
 
si nat : 4 je recupere l'info de t1
si nat : 10 je recupere l'info de t2
 
si nat : 1 je recupere l'info de t1
si nat : 15 je recupere l'info de t2
 
4,10 : represente un seul groupe de nature ex : nouveau
1,15  :represente un seul groupe de nature ex : ancien

resultat voulu sous forme de tableau croise:

Code :

1
2
3
4
5
 
              cru1    cru2     cru3   cru4     total
nouveau   10      25         20     20        75
ancien      5       25          10     10        50
total        15      50          30     30        125

Cordialement,

[ojo77]

Si je comprends votre question, il y a un souci de modélisation : nat devrait se trouver dans T3 et ni dans T2 ni dans T1.

En imaginant que nat soit dans t3, la requête devient :

Code sql :

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
60
61
62
63
64
SELECT c1
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru1',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru1',t2qb,0)
                  else NULL
                  end ) cru1				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru2',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru2',t2qb,0)
                  else NULL
                  end ) cru2				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru3',t3qb,0) 
                  when nat IN (10,15) then decode(cru,'cru3',t3qb,0)
                  else NULL
                  end ) cru3
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru4',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru4',t2qb,0)
                  else NULL
                  end ) cru4
     , sum ( case when nat IN ( 4, 1) then t1qb, 
                  when nat IN (10,15) then t2qb
                  else NULL
                  end ) total
FROM ( SELECT case when t3.nat IN ( 4, 10) then 'nouveau'
                   when t3.nat IN ( 1, 15) then 'ancien' 
                   else NULL
                   end c1
            , t1.qtebase t1qb
            , t2.qtebase t2qb
            , t3.cru cru
            , t3.nat nat
       FROM t1 INNER JOIN
            t2 ON t1.cru=t2.cru INNER JOIN 
			t3 ON t1.cru=t3.cru ) t
GROUP BY c1 
union ALL 
SELECT c1
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru1',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru1',t2qb,0)
                  else NULL
                  end ) cru1				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru2',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru2',t2qb,0)
                  else NULL
                  end ) cru2				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru3',t3qb,0) 
                  when nat IN (10,15) then decode(cru,'cru3',t3qb,0)
                  else NULL
                  end ) cru3
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru4',t1qb,0) 
                  when nat IN (10,15) then decode(cru,'cru4',t2qb,0)
                  else NULL
                  end ) cru4
     , sum ( case when nat IN ( 4, 1) then t1qb, 
                  when nat IN (10,15) then t2qb
                  else NULL
                  end ) total
FROM ( SELECT 'total' c1
            , t1.qtebase t1qb
            , t2.qtebase t2qb
            , t3.cru cru
            , t3.nat nat
       FROM t1 INNER JOIN
            t2 ON t1.cru=t2.cru INNER JOIN 
			t3 ON t1.cru=t3.cru ) t
GROUP BY c1;

[nah_wah]

Bonjour,

il n'y a pas de souci de modelisation les tables sont faites comme ça
pouvez m'aider dans ce sens la

Cordialement,

[ojo77]

Alors il faut quelques infos complémentaires :

Pour un "cru" donné, l'info est-elle bien répartie dans les tables T1 et T2 en fonction du "nat" ? C'est à dire si cru=cru1 est-ce qu'on trouve les nat 1 et 4 seulement dant t1 et les nat 10 et 15 seulement dans T2 ?

Si la réponse à la question précédente est négative comment priorise-t-on une table (t1 ou t2) par rapport à l'autre ?

Mes questions sont-elles claires ?

[nah_wah]

pour cru 1 exite dans les deux tables t1 et t2
si nat =4 je recupere l'info de sum(t1.qtebase) et si nat =10 je recup de sum(t2.qtebase)
donc pour nat 4 et 10 represente 1 et 1 seul groupe et donc pour chaque groupe de nature en calcul la somme de quantite existant au niveau t1 et t2

cru 1 = t1.qtebase + t2.qtebase

si toutefois vous avez besoin de plus d'info je suis la

encore merci

[ojo77]

Je repose la question :

Si cru=cru1 est-ce qu'on trouve les nat 1 et 4 seulement dant t1 et les nat 10 et 15 seulement dans T2 ? (même question pour toutes les valeurs de cru )

[nah_wah]

oui

[ojo77]

Dans ce cas :

Code sql :

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
SELECT c1
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru1',qtbase,0) 
                  when nat IN (10,15) then decode(cru,'cru1',qtbase,0)
                  else NULL
                  end ) cru1				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru2',qtbase,0) 
                  when nat IN (10,15) then decode(cru,'cru2',qtbase,0)
                  else NULL
                  end ) cru2				  
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru3',qtbase,0) 
                  when nat IN (10,15) then decode(cru,'cru3',qtbase,0)
                  else NULL
                  end ) cru3
     , sum ( case when nat IN ( 4, 1) then decode(cru,'cru4',qtbase,0) 
                  when nat IN (10,15) then decode(cru,'cru4',qtbase,0)
                  else NULL
                  end ) cru4
     , sum ( case when nat IN ( 4, 1) then qtbase  
                  when nat IN (10,15) then qtbase 
                  else NULL
                  end ) total
FROM ( SELECT case when t3.nat IN ( 4, 10) then 'nouveau'
                   when t3.nat IN ( 1, 15) then 'ancien' 
                   else NULL
                   end c1
            , t.qtebase qtbase
            , t3.cru cru
            , t.nat nat
       FROM ( selet * FROM t1 union ALL SELECT * FROM t2 ) t INNER JOIN
			t3 ON t.cru=t3.cru ) tab
GROUP BY c1 
union ALL 
SELECT c1
     , sum ( decode(cru,'cru1',qtbase,0) ) cru1				  
     , sum ( decode(cru,'cru2',qtbase,0) ) cru2				  
     , sum ( decode(cru,'cru3',qtbase,0) ) cru3
     , sum ( decode(cru,'cru4',qtbase,0) ) cru4
     , sum ( qtbase ) total
FROM ( SELECT 'total' c1
            , t.qtebase qtbase
            , t3.cru cru
       FROM ( selet * FROM t1 union ALL SELECT * FROM t2 ) t INNER JOIN
			t3 ON t.cru=t3.cru ) tab
GROUP BY c1;

[Waldar]

Ça me paraît un peu complexe, mais un exemple illustré est toujours plus simple à appréhender.

Les données (sous forme de CTE) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH t1 AS
(
SELECT 'cru1' AS cru,  8 AS qte,  4 AS nat FROM dual union ALL
SELECT 'cru1'       ,  2       ,  1        FROM dual union ALL
SELECT 'cru2'       , 25       ,  4        FROM dual union ALL
SELECT 'cru2'       , 25       ,  1        FROM dual union ALL
SELECT 'cru4'       , 20       ,  4        FROM dual
)
  ,  t2 AS
(
SELECT 'cru1' AS cru,  2 AS qte, 10 AS nat FROM dual union ALL
SELECT 'cru1'       ,  3       , 15        FROM dual union ALL
SELECT 'cru3'       , 20       , 10        FROM dual union ALL
SELECT 'cru3'       , 10       , 15        FROM dual union ALL
SELECT 'cru4'       , 10       , 15        FROM dual
)
  ,  t3 AS
(
SELECT 'cru1' AS cru FROM dual union ALL
SELECT 'cru2'        FROM dual union ALL
SELECT 'cru3'        FROM dual union ALL
SELECT 'cru4'        FROM dual union ALL
SELECT 'cru5'        FROM dual -- Je rajoute ce cas de figure volontairement
)

La requête (à coller au code précédent) :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
  ,  sr AS
(
SELECT t1.cru,
       t1.nat,
       t1.qte
  FROM t1
 WHERE t1.nat IN (1, 4)
 union ALL
SELECT t2.cru,
       t2.nat,
       t2.qte
  FROM t2
 WHERE t2.nat IN (10, 15)
)
  SELECT coalesce(case when nat IN (4, 10) then 'Nouveau' else 'Ancien' end, 'Total') AS groupe,
         coalesce(sum(case cru when 'cru1' then qte end), 0) AS qte_cru1, 
         coalesce(sum(case cru when 'cru2' then qte end), 0) AS qte_cru2, 
         coalesce(sum(case cru when 'cru3' then qte end), 0) AS qte_cru3, 
         coalesce(sum(case cru when 'cru4' then qte end), 0) AS qte_cru4,
         coalesce(sum(case cru when 'cru5' then qte end), 0) AS qte_cru5,
         coalesce(sum(qte), 0) AS qte_totale
    FROM sr
GROUP BY rollup(case when nat IN (4, 10) then 'Nouveau' else 'Ancien' end)
ORDER BY case when nat IN (4, 10) then 'Nouveau' else 'Ancien' end DESC nulls last;

Le résultat :

Code :

1
2
3
4
5
GROUPE    QTE_CRU1   QTE_CRU2   QTE_CRU3   QTE_CRU4   QTE_CRU5 QTE_TOTALE
------- ---------- ---------- ---------- ---------- ---------- ----------
Nouveau         10         25         20         20          0         75
Ancien           5         25         10         10          0         50
Total           15         50         30         30          0        125

Finalement il y a juste un union all entre les tables t1 et t2, puis un pivot + rollup.

Mais cette présentation vous fige un peu trop à mon goût, je cous encourage plutôt à utiliser une requête relationnelle plus classique :

Code :

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
  ,  sr AS
(
SELECT t1.cru,
       t1.nat,
       t1.qte
  FROM t1
 WHERE t1.nat IN (1, 4)
 union ALL
SELECT t2.cru,
       t2.nat,
       t2.qte
  FROM t2
 WHERE t2.nat IN (10, 15)
)
  SELECT t3.cru,
         case
           when sr.nat IN (4, 10)
           then 'Nouveau'
           when sr.nat IN (1, 15)
           then 'Ancien'
         end AS groupe,
         coalesce(sum(sr.qte), 0) AS qte
    FROM t3
         LEFT OUTER JOIN sr
           ON sr.cru = t3.cru
GROUP BY t3.cru,
         case
           when sr.nat IN (4, 10)
           then 'Nouveau'
           when sr.nat IN (1, 15)
           then 'Ancien'
         end
ORDER BY t3.cru ASC, groupe DESC;

Qui donne comme résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
CRU  GROUPE         QTE
---- ------- ----------
cru1 Nouveau         10
cru1 Ancien           5
cru2 Nouveau         25
cru2 Ancien          25
cru3 Nouveau         20
cru3 Ancien          10
cru4 Nouveau         20
cru4 Ancien          10
cru5                  0

[nah_wah]

Merci pour vos reponses c'etait tres instructif j'ai utilse la derniere solution

Code :

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
  ,  sr AS
(
SELECT t1.cru,
       t1.nat,
       t1.qte
  FROM t1
 WHERE t1.nat IN (1, 4)
 union ALL
SELECT t2.cru,
       t2.nat,
       t2.qte
  FROM t2
 WHERE t2.nat IN (10, 15)
)
  SELECT t3.cru,
         case
           when sr.nat IN (4, 10)
           then 'Nouveau'
           when sr.nat IN (1, 15)
           then 'Ancien'
         end AS groupe,
         coalesce(sum(sr.qte), 0) AS qte
    FROM t3
         LEFT OUTER JOIN sr
           ON sr.cru = t3.cru
GROUP BY t3.cru,
         case
           when sr.nat IN (4, 10)
           then 'Nouveau'
           when sr.nat IN (1, 15)
           then 'Ancien'
         end
ORDER BY t3.cru ASC, groupe DESC;

proposer par Waldar juste un seul probleme avec ce resultat comment peut on afficher

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
CRU  GROUPE         QTE
---- ------- ----------
cru1 Nouveau         10
cru1 Ancien           5
cru2 Nouveau         25
cru2 Ancien          25
cru3 Nouveau         20
cru3 Ancien          10
cru4 Nouveau         20
cru4 Ancien          10
cru5                  0

ce resultat sous la forme d'un tableau :

Code :

1
2
3
4
5
GROUPE    QTE_CRU1   QTE_CRU2   QTE_CRU3   QTE_CRU4   QTE_CRU5 QTE_TOTALE
------- ---------- ---------- ---------- ---------- ---------- ----------
Nouveau         10         25         20         20          0         75
Ancien           5         25         10         10          0         50
Total           15         50         30         30          0        125

Cordialement,