Hi to all.
It's a pleasure to receive your help.
I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta and I am working with the two follows tables.
I want to get a soccer team with the highest combined score averages and participations considering all possible formations and my budget, but I'm not finding a way to get a cost effective processing.
Suggestions?
create table ESQUEMA
(
ESQUEMA INTEGER,
ZAGUEIROS INTEGER,
LATERAIS INTEGER,
MEIAS INTEGER,
ATACANTES INTEGER,
P1 CHAR(3),
P2 CHAR(3),
P3 CHAR(3),
P4 CHAR(3),
P5 CHAR(3),
P6 CHAR(3),
P7 CHAR(3),
P8 CHAR(3),
P9 CHAR(3),
P10 CHAR(3)
)
;
esquema table - sample data
| ESQUEMA | ZAGUEIROS | LATERAIS | MEIAS | ATACANTES | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 |
| 1 | 442 | 2 | 2 | 4 | 2 | ZAG | ZAG | LAT | LAT | MEI | MEI | MEI | MEI | ATA | ATA |
| 2 | 433 | 2 | 2 | 3 | 3 | ZAG | ZAG | LAT | LAT | MEI | MEI | MEI | ATA | ATA | ATA |
| 3 | 532 | 3 | 2 | 3 | 2 | ZAG | ZAG | ZAG | LAT | LAT | MEI | MEI | MEI | ATA | ATA |
| 4 | 352 | 3 | | 5 | 2 | ZAG | ZAG | ZAG | MEI | MEI | MEI | MEI | MEI | ATA | ATA |
| 5 | 343 | 3 | | 4 | 3 | ZAG | ZAG | ZAG | MEI | MEI | MEI | MEI | ATA | ATA | ATA |
| 6 | 451 | 2 | 2 | 5 | 1 | ZAG | ZAG | LAT | LAT | MEI | MEI | MEI | MEI | MEI | ATA |
| 7 | 541 | 3 | 2 | 4 | 1 | ZAG | ZAG | ZAG | LAT | LAT | MEI | MEI | MEI | MEI | ATA |
create table JOGADORES
(
ID INTEGER not null,
CLUBE VARCHAR2(100),
NOME VARCHAR2(100),
POSICAO CHAR(3),
PRECO NUMBER(4,2),
MEDIA NUMBER(4,2),
JOGOS INTEGER
)
;
jogadores table - sample data
| ID | CLUBE | NOME | POSICAO | PRECO | MEDIA | JOGOS |
| 80 | 403 | Fluminense | Abel Braga | TEC | 17,51 | 5,36 | 4 |
| 70 | 393 | Coritiba | Alex | MEI | 16,98 | 5,08 | 5 |
| 42 | 365 | Náutico | Auremir | LAT | 7,73 | 5,48 | 4 |
| 18 | 341 | Grêmio | Barcos | ATA | 18,96 | 3,67 | 4 |
| 102 | 425 | Grêmio | Bressan | ZAG | 10,85 | 5,90 | 4 |
| 31 | 354 | Crisciúma | Bruno | GOL | 10,66 | 3,20 | 5 |
| 105 | 428 | Cruzeiro | Bruno Rodrigo | ZAG | 15,47 | 5,28 | 5 |
| 82 | 405 | Vitória | Caio Júnior | TEC | 11,40 | 4,45 | 5 |
| 41 | 364 | Fluminense | Carlinhos | LAT | 12,40 | 6,93 | 3 |
| 61 | 384 | Vasco | Carlos Alberto | MEI | 15,01 | 6,75 | 2 |
| 13 | 336 | Ponte Preta | Chiquinho | ATA | 9,97 | 4,64 | 5 |
| 72 | 395 | Ponte Preta | Cicinho | MEI | 11,42 | 4,72 | 5 |
| 92 | 415 | Santos | Claudinei Oliveira | TEC | 11,92 | 3,17 | 3 |
| 104 | 427 | Ponte Preta | Cléber | ZAG | 14,61 | 5,78 | 5 |
| 68 | 391 | Portuguesa | Correa | MEI | 8,44 | 5,60 | 4 |
| 93 | 416 | Bahia | Cristóvão Borges | TEC | 8,27 | 2,92 | 5 |
| 96 | 419 | Atlético-MG | Cuca | TEC | 12,62 | 2,32 | 4 |
| 29 | 352 | Corinthians | Cássio | GOL | 12,51 | 3,74 | 5 |
I want a result like this query could bring, if it had a low cost. Should I make any changes in tables or attempting to use PL / SQL?
select *
from jogadores j1,
jogadores j2,
jogadores j3,
jogadores j4,
jogadores j5,
jogadores j6,
jogadores j7,
jogadores j8,
jogadores j9,
jogadores j10,
jogadores j11,
jogadores j12,
esquema e
where j1.posicao = e.p1
and j2.posicao = e.p2
and j3.posicao = e.p3
and j4.posicao = e.p4
and j5.posicao = e.p5
and j6.posicao = e.p6
and j7.posicao = e.p7
and j8.posicao = e.p8
and j9.posicao = e.p9
and j10.posicao = e.p10
and j11.posicao = 'TEC'
and j12.posicao = 'GOL'
--
and j1.id NOT IN (j2.id,
j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j2.id NOT IN (j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j3.id NOT IN
(j4.id, j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j4.id NOT IN
(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j5.id NOT IN (j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
--
and j9.id NOT IN (j10.id, j11.id, j12.id)
--
and j10.id NOT IN (j11.id, j12.id)
--
and j11.id <> j12.id
and (e.esquema,
(j1.media + j2.media + j3.media + j4.media + j5.media + j6.media +
j7.media + j8.media + j9.media + j10.media + j11.media + j12.media +
j1.jogos + j2.jogos + j3.jogos + j4.jogos + j5.jogos + j6.jogos +
j7.jogos + j8.jogos + j9.jogos + j10.jogos + j11.jogos + j12.jogos)) in
(select e.esquema,
nvl(max(j1.media + j2.media + j3.media + j4.media + j5.media +
j6.media + j7.media + j8.media + j9.media + j10.media +
j11.media + j12.media + j1.jogos + j2.jogos +
j3.jogos + j4.jogos + j5.jogos + j6.jogos + j7.jogos +
j8.jogos + j9.jogos + j10.jogos + j11.jogos +
j12.jogos),
0)
from jogadores j1,
jogadores j2,
jogadores j3,
jogadores j4,
jogadores j5,
jogadores j6,
jogadores j7,
jogadores j8,
jogadores j9,
jogadores j10,
jogadores j11,
jogadores j12,
esquema e
where j1.posicao = e.p1
and j2.posicao = e.p2
and j3.posicao = e.p3
and j4.posicao = e.p4
and j5.posicao = e.p5
and j6.posicao = e.p6
and j7.posicao = e.p7
and j8.posicao = e.p8
and j9.posicao = e.p9
and j10.posicao = e.p10
and j11.posicao = 'TEC'
and j12.posicao = 'GOL'
--
and j1.id NOT IN (j2.id,
j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j2.id NOT IN (j3.id,
j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j3.id NOT IN (j4.id,
j5.id,
j6.id,
j7.id,
j8.id,
j9.id,
j10.id,
j11.id,
j12.id)
--
and j4.id NOT IN
(j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j5.id NOT IN
(j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
--
and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
--
and j9.id NOT IN (j10.id, j11.id, j12.id)
--
and j10.id NOT IN (j11.id, j12.id)
--
and j11.id <> j12.id
and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco +
j6.preco + j7.preco + j8.preco + j9.preco + j10.preco +
j11.preco + j12.preco) <= &patrimonio
group by e.esquema)
and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +
j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) <=
&patrimonio;
Thank in advance,
Filippe