Waarden 1-8 alle aanwezig?

Status
Niet open voor verdere reacties.
R

Robin S

Gebruiker
Lid geworden
4 dec 2008
Berichten
246
1 Bijlage

Ik heb met een bepaalde query een tabel verkregen met verschillende combinaties voor de velden `a`.`pos`, `b`.`pos` en `c`.`pos`.

Nu zou ik graag uit die tabel de `id`s selecteren waarvoor geldt dat de cijfers 1-8 alle tenminste éénmaal voorkomen in één van de `pos`-velden.

Uit het voorbeeld (zie bijlage) zou ik dus graag willen dat '1663' niet in de result-set zit omdat de waarde 7 ontbreekt, en dat '2132' wel in de result-set zit omdat de waarden 1-8 alle aanwezig zijn. Ik zit hier al een tijdje het hoofd over te breken, maar kan gewoon niet op een oplossing komen :( Hopelijk snuffelt er hier iemand rond die kan helpen. Alvast bedankt :)
 

Bijlagen

  • table.png
    table.png
    34,7 KB · Weergaven: 48
Waarom heb je deze tabel layout? Die is nogal onhandig voor wat je wilt...

(Sterker nog; ik ga het niet eens proberen :/ Die query wordt vreselijk onhandig en traag namelijk)

Begin eens met de 3 pos velden terugbrengen tot 1 pos veld met een koppel tabel (meer rijen), dan kom je al dichter in de buurt.

Daarna zul je ws eindigen met een WHERE EXISTS() met 8 subqueries; nog steeds vreselijk inneficient, maar iig haalbaar. Ik weet iig niks beters.
 
Je hebt gelijk.. De layout hiervan komt door de query die ik gebruikt hem om aan dit resultaat te komen :p Die zal ook ongetwijfeld een stuk beter kunnen. 'k Zal de query eens laten zien :) Wellicht zijn er suggesties die voor mij niet al te obvious zijn. Zou zomaar kunnen... :p

[sql]SELECT `a`.`id`,`a`.`x`,`a`.`y`,`a`.`pos`,`b`.`pos`,`c`.`pos` FROM (
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=2
) GROUP BY `id`,`pos`
) AS `a`,(
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource4','resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource4','resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource4','resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource4','resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=1
) GROUP BY `id`,`pos`
) AS `b`,(
SELECT `id`,`x`,`y`,`pos` FROM (
SELECT `id`-472 AS `id`, `x`-1 AS `x`, `y`+1 AS `y`, '8' AS `pos` FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-471, `x`, `y`+1, '7' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-470, `x`+1, `y`+1, '6' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`-1, `x`-1, `y`, '5' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+1, `x`+1, `y`, '4' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+470, `x`-1, `y`-1, '3' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+471, `x`, `y`-1, '2' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION
SELECT `id`+472, `x`+1, `y`-1, '1' FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6')
) AS `` WHERE `id` IN (
SELECT `id` FROM (
SELECT `id`-472 AS `id` FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-471 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-470 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`-1 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+1 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+470 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+471 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6') UNION ALL
SELECT `id`+472 FROM `map` WHERE `resource` IN ('resource1','resource2','resource3','resource4','resource5','resource6')
) AS `` GROUP BY `id` HAVING count(`id`)>=5
) GROUP BY `id`,`pos`
) AS `c` WHERE `a`.`id`=`b`.`id` AND `a`.`id`=`c`.`id` AND `a`.`pos`<>`b`.`pos` AND `a`.`pos`<>`c`.`pos` AND `b`.`pos`<>`c`.`pos` ORDER BY `a`.`id`,`a`.`pos`,`b`.`pos`,`c`.`pos`[/sql]

Ik heb overigens een (tijdelijke?) oplossing gevonden: ik stop 't tabelletje uit de bijlage in een tijdelijke tabel. Daar laat 'k dan deze (inderdaad vreselijk onhandige) query op los:
[sql]SELECT `id`,`x`,`y` FROM (
SELECT * FROM `tmp` WHERE `pos1`='1' OR `pos2`='1' OR `pos3`='1' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='2' OR `pos2`='2' OR `pos3`='2' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='3' OR `pos2`='3' OR `pos3`='3' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='4' OR `pos2`='4' OR `pos3`='4' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='5' OR `pos2`='5' OR `pos3`='5' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='6' OR `pos2`='6' OR `pos3`='6' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='7' OR `pos2`='7' OR `pos3`='7' GROUP BY `id` UNION ALL
SELECT * FROM `tmp` WHERE `pos1`='8' OR `pos2`='8' OR `pos3`='8' GROUP BY `id`
) AS `` GROUP BY `id` HAVING count(`id`)=8[/sql]
 
Misschien moet je eens beginnen met uitleggen wat je nou eigenlijk precies allemaal aan het doen bent, want dit ziet er vreselijk omslachtig en onduidelijk uit :/
 
Prima, hier komt-ie :)

Ik speel een spelletje: Mytholia. Dit is een multiplayer browsergame waarbij je dorpjes opbouwt, je leger uitbreidt, etc. Je werkt met 5 verschillende economische grondstoffen: food, wood, iron, stone, gold. Wanneer je een nieuw dorp sticht zoek je de specifieke locatie uit, onder andere aan de hand van de typen grond die direct om dat dorp heen liggen.

Er zijn 6 typen grond die allemaal een bepaalde grondstof produceren. Ik houd even de engelse benaming van het spel aan ;)
- grass, produceert food.
- lake, produceert food.
- forest, produceert wood.
- iron mine, produceert iron.
- stone quarry, produceert stone.
- gold mine, produceert gold.

Een dorp heeft beschikking over de 8 omliggende grondstofvelden.
Voorbeeld: [zie bijlage: mytholia_dorpje.png]
Dit dorp heeft de beschikking over de volgende acht grondstrofvelden:
- 3 forests -> wood
- 2 stone quarries -> stone
- 2 gold mines -> gold
- 1 iron mines -> iron

Ik heb me in mijn hoofd gehaald dat ik wel even een zoekmachine kon maken voor de gewenste economische samenstelling :p Het tabelletje waarin de zoekcriteria ingesteld worden ziet er ongeveer zo uit: [zie bijlage: zoekfunctie.png]

Goed.. Wat zit er in mijn database?
De kaart van mytholia heeft een breedte en hoogte van 470 velden. Ieder veld in mijn database heb ik een `id` gegeven.
[Zie bijlage: mytholia_ids.png]
Het veld links-bovenin heeft `id`=1. Het veld direct rechts daarnaast heeft `id`=2, het veld rechts bovenin heeft `id`=470, het veld onder het eerste veld heeft `id`=471, etc.
Verder heeft ieder veld een `resource`. Deze waarde is een enum:
'resource1' staat voor grass -> food
'resource2' staat voor lake -> food
'resource3' staat voor forest -> wood
'resource4' staat voor iron mine -> iron
'resource5' staat voor stone quarry -> stone
'resource6' staat voor gold mine -> gold

Aan de hand van deze waarden in de database en de zoekcriteria moeten dus de resultaten geselecteerd worden. De query moet inderdaad dynamisch worden, maar dat lukt wel in de php. Ik ben aan het testen geslagen met de query zoals die gevormd wordt door de instellingen de bijlage zoekfunctie.png: 2xgold, 1x(iron óf gold), en 5 velden waarvan het grondstoftype eigenlijk niet uitmaakt.

Voordat ik verder ga.. Is m'n verhaal tot hier aan toe duidelijk? :p Zal ik stukje voor stukje vertellen hoe en waarom die lange query zo in elkaar zit?
 

Bijlagen

  • mytholia_dorpje.png
    mytholia_dorpje.png
    36,4 KB · Weergaven: 23
  • zoekfunctie.png
    zoekfunctie.png
    22 KB · Weergaven: 19
  • mytholia_ids.png
    mytholia_ids.png
    2,6 KB · Weergaven: 21
Het verhaal is duidelijk. Dit klinkt als iets dat je in PHP wil doen, niet in SQL.

Ik raad je aan om gewoon blokken van de map in te laden, en in PHP berekeningen te doen om bepaalde velden te zoeken, dan is het veel makkelijker (zeker als het ook nog dynamisch moet)

Als je dit in SQL aan wilt pakken, raad ik je aan om een STORED PROCEDURE te maken om het te doen, maar das heel wat anders dan gewoon SQL, want met een enkele query wordt het echt een bitch...
 
Thnx, die mogelijkheden ga ik zeker even uitspitten :) Bedankt voor de hulp!
 
Misschien begrijp ik het niet helemaal, maar t kan ook best in SQL volgens mij en ook niet zo complex. Ook geen geweldige performance vermoed ik, maar ik denk dat het met de traagheid wel mee zal vallen.

Ik sla de boel eerst plat door een tabel met maar 1 pos-kolom te maken ipv 3 (dit gebeurt met de unions). Vervolgens kijk ik hoeveel verschillende pos-waarden een ID heeft en ik toon alleen de ID’s met 8 verschillende waarden.

[SQL]Select count(distinct posjes), id
from
(
Select pos1 as posjeslijst, id from tabel
Union
Select pos2, id from tabel
Union
Select pos3, id from tabel
)
Group by id
Having count(distinct posjes) = 8[/SQL]
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan