AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren

SQL Vereinfachen

Ein Thema von Medium · begonnen am 16. Feb 2012 · letzter Beitrag vom 17. Feb 2012
Antwort Antwort
Seite 1 von 2  1 2   
Medium

Registriert seit: 23. Jan 2008
3.679 Beiträge
 
Delphi 2007 Enterprise
 
#1

SQL Vereinfachen

  Alt 16. Feb 2012, 16:06
Datenbank: MySQL • Version: 4.1.9 • Zugriff über: UnIDAC
Spätmahlzeit.

Ich habe folgendes Szenario:
In meiner DB habe ich eine Produktionsanlage abgebildet, von der der hier wesentliche Teil eine Menge von Behältern ist. Diese Behälter können Quellen und/oder Ziele für andere Behälter sein, was durch die Verrohrung vorbestimmt ist. Einige Behälter beinhalten Rohstoffe.
Der Anlagenbediener kann sich aus diesen Rohstoffen ein Rezept nach Belieben anfertigen, und der Anlage dann zur vollautomatischen Produktion vorgeben. Ich möchte bei der Vorgabe eines Rezeptes einen Auswahldialog haben, der mir anzeigt, welche Zielbehälter von allen Quellbehältern erreicht werden können, die die dort angegebenen Rohstoffe beinhalten.

Tabellen (nur die relevanten Felder):
beh (Behälterdaten)
- id (autoinc)
- Behälternummer
- Behältername
- Rohstoff (im Behälter)

qundz (Quellen und Ziele - Kreuztabelle der Behälternummern aus "beh" untereinander, gibt an wer wen wie direkt erreichen kann)
- id (autoinc)
- Quelle
- Ziel

rezpos (Rezeptpositionen aller Rezepte. Die mit typ=1 sind Dosier-Angaben (es gibt noch andere, die hier aber keine Rolle spielen sollen))
- id (autoinc)
- Rezeptnummer
- Typ
- Rohstoff

Es gibt also z.B. ein Rezept, in das soll Wasser, Wein und Orangensaft. Ich möchte nun alle Zielbehälter ermitteln, die Quellbehälter direkt angeschlossen haben, in denen alle 3 Rohstoffe vorhanden sind. Sprich die Behälter, in denen das Rezept angefertigt werden kann. Behälternummer und Behältername.

Im Moment mache ich das so:
Code:
SELECT k.ziel, e.name FROM
  (SELECT COUNT(z.id) qcount, z.ziel FROM                                                                // Zähle die erfüllbaren Dosierpositionen für alle Ziele, die von den beteiligten Quellbehältern erreicht werden können
    (SELECT p.id, q.ziel FROM beh b
     JOIN (SELECT id, rohstoff FROM rezpos WHERE rezeptnummer=:rn AND typ=1) p ON b.rohstoff = p.rohstoff // aus dem gewünschten Rezept "rezeptnummer"
     JOIN qundz q ON b.behaelternummer = q.quelle) z
   GROUP BY z.ziel) k
JOIN (SELECT COUNT(id) mcount FROM rezpos WHERE rezeptnummer=:rn AND typ=1) m                            // Zähle alle Dosierpositionen im Rezept
JOIN beh e ON k.ziel=e.behaelternummer                                                                   // Zielbehälternamen anflanschen
WHERE mcount=qcount                                                                                      // Nur die anzeigen, wo "möchliche Anzahl zu bedienender Positionen" der "gewünschten Anzahl zu bedienender Positionen" entspricht
Das tut was es soll, ist aber ein Monster (und ein wenig von hinten durch die Brust irgendwie). Da meine Hirnverknotung aber gerade recht hoch ist, komme ich nicht ganz auf den einfachen Weg, den es vermutlich dafür gibt. Mag mir da wer auf die Sprünge helfen?
"When one person suffers from a delusion, it is called insanity. When a million people suffer from a delusion, it is called religion." (Richard Dawkins)
  Mit Zitat antworten Zitat
shmia

Registriert seit: 2. Mär 2004
5.508 Beiträge
 
Delphi 5 Professional
 
#2

AW: SQL Vereinfachen

  Alt 16. Feb 2012, 17:10
Deine Tabelle qundz sollte übrigens so aussehen:
Code:
- Quelle (1. Primärschlüsselfeld)
- Ziel  (2. Primärschlüsselfeld)
Dies hat 3 Vorteile:
* Tabelle ist kleiner, Abfragen sind schneller
* es wird verhindert dass gleiche Behälter-Verbindungen mehrfach vorhanden sein können
* Abfragen profitieren davon, dass die Felder Quelle und Ziel schon durch den Primärschlüssel indiziert sind.
Andreas
  Mit Zitat antworten Zitat
Blup

Registriert seit: 7. Aug 2008
Ort: Brandenburg
1.429 Beiträge
 
Delphi 10.4 Sydney
 
#3

AW: SQL Vereinfachen

  Alt 16. Feb 2012, 17:48
Ungetestet im Prinzip so:
SQL-Code:
select distinct a.nr, a.name
from beh a
left join qundz b on (b.ziel = a.nr)
left join beh c on (c.nr = b.quelle) and (c.rohstoff = :rohstoff1)
left join beh d on (d.nr = b.quelle) and (d.rohstoff = :rohstoff2)
left join beh e on (e.nr = b.quelle) and (e.rohstoff = :rohstoff3)
where (c.nr is not null) and (d.nr is not null) and (e.nr is not null)

Geändert von mkinzler (16. Feb 2012 um 17:50 Uhr) Grund: Tag für SQL ergänzt
  Mit Zitat antworten Zitat
Benutzerbild von p80286
p80286

Registriert seit: 28. Apr 2008
Ort: Stolberg (Rhl)
6.659 Beiträge
 
FreePascal / Lazarus
 
#4

AW: SQL Vereinfachen

  Alt 16. Feb 2012, 17:56
Meiner Meinung nach kann das nicht so richti funktionieren, da zu jeder Rezeptur auch mehrere Rohstoffe gehören. Du solltest also Deine Tabellen erweitern:

Rezept
RezeptID

RezeptRohstoff
RezeptID
RohstoffID
RohstoffMenge

Behälter(Rohstoff)
BehälterID(RohstoffID)

dann sollte auch die gesuchte zusamenstellung kein Problem sein:

Code:
select ...
from Rezept,rezeptrohstoff,behälter as rohstoff,qundz,behalter produkt
where Rezept.RezeptID=RezeptRohstoff.RezeptID
  and Rohstoff.BehälterID=RezeptRohstoff.RohstoffID
  and Rohstoff.BehälterID=qundz.Quelle
  and produkt.BehälterID=qundz.Ziel
Ich hoffe ich habe da nichts falsch verstanden

Gruß
K-H
Programme gehorchen nicht Deinen Absichten sondern Deinen Anweisungen
R.E.D retired error detector
  Mit Zitat antworten Zitat
omata

Registriert seit: 26. Aug 2004
Ort: Nebel auf Amrum
3.154 Beiträge
 
Delphi 7 Enterprise
 
#5

AW: SQL Vereinfachen

  Alt 16. Feb 2012, 18:29
Vielleicht so...

SQL-Code:
SELECT q.ziel, e.Behaeltername
FROM beh b
INNER JOIN rezpos p
  ON b.rohstoff = p.rohstoff
INNER JOIN qundz q
  ON b.behaelternummer = q.quelle
INNER JOIN beh e
  ON q.ziel = e.behaelternummer
WHERE rezeptnummer = :rn
  AND typ = 1
GROUP BY p.id, q.ziel, e.Behaeltername, p.rezeptnummer, p.typ
HAVING COUNT(*) = (SELECT COUNT(*)
                   FROM rezpos
                   WHERE rezeptnummer = p.rezeptnummer
                     AND typ = p.typ)

Geändert von omata (16. Feb 2012 um 21:07 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#6

AW: SQL Vereinfachen

  Alt 16. Feb 2012, 21:46
Vielleicht hab ich das falsch verstanden, mir ist nicht klar, wozu die counts alle gut sein sollen, wenn Du eine Liste der möglichen Quell- und oder Zielbehälter haben möchtest. Also gebe ich einfach alles aus, was in Frage kommt.

SQL-Code:
Select r.rohstoff,
       q.id, q.behaelternummer, q.behaeltername,
       z.id, z.behaelternummer, z.behaeltername, z.rohstoff as Produkt
  from rezpos r
  join beh q on r.rohstoff = q.rohstoff
  join qundz u on q.behaelternummer = u.quelle
  join beh z on u.ziel = z.behaelternummer
 where r.rezeptnummer = :rn
   and r.typ = 1
P.S.: Ich habe die IDs nicht genutzt, auch wenn sie vermutlich teilweise irgendwo die Keys bzw Fk sind. Die anderen Spalten schienen mir "sprechender", Prinzip ist ja eh das gleiche.
Gruß, Jo

Geändert von jobo (16. Feb 2012 um 21:48 Uhr)
  Mit Zitat antworten Zitat
Benutzerbild von BUG
BUG

Registriert seit: 4. Dez 2003
Ort: Cottbus
2.094 Beiträge
 
#7

AW: SQL Vereinfachen

  Alt 17. Feb 2012, 00:24
Die Division wäre vielleicht was für dich.

Du konstruierst eine Relation R1(Zielbehälter, Quellbehälterinhalt), die alle vorhanden Tupel (Zielbehälter, Quellbehälterinhalt) enthält (Projektion von Verbund aus beh, qundz und beh). Dann konstruierst du eine Relation R2(Zutat), die alle für das gewünschte Rezept nötigen Zutaten enthält (Select auf rezpos).

Wenn du nun R1/R2 rechnest, kommen genau die Behälter heraus, die die nötigen Zuflüsse haben.
Das Berechnen der Division ist leider nicht mit einem SQL-Befehl erschlagen, so dass die Lösung evtl. auch lang wird.

Wie man die uA. nötige Mengen-Differenz erhält, ist zB. hier beschrieben.

Geändert von BUG (17. Feb 2012 um 00:45 Uhr)
  Mit Zitat antworten Zitat
Medium

Registriert seit: 23. Jan 2008
3.679 Beiträge
 
Delphi 2007 Enterprise
 
#8

AW: SQL Vereinfachen

  Alt 17. Feb 2012, 10:36
Danke für die rege Beteiligung!

Das Problem scheint wohl doch komplexer zu sein als ich gestern noch im guten Glauben annahm. Fast alle Lösungen leiden unter dem selben Problem: Ich erhalte eine Liste aller Zielbehälter, die von jedem Rohstoff einzeln erreicht werden können, wobei ich gerne wüsste welche Ziele von allen Rohstoffen gleichsam erreicht werden können. Was ich leicht bekomme ist also:
Code:
rohstoff  zielbehälter
------------------------
   1           101
   1           130
   1           140
   2            50
   2           130
   3            50
   3           101
   3           130
   3           140
Das sind schon nur die Rohstoffe aus einem konkreten Rezept, so dass das schon recht nah an der Lösung ist, wobei ich dies letztlich im Grunde mit einer Mischung eurer Vorschläge erreicht habe:
SQL-Code:
SELECT a.rohstoff, a.behnr FROM
  (SELECT b1.rohstoff, b2.behnr FROM qundz qz
   JOIN beh b1 ON b1.behnr=qz.quelle
   JOIN rohstoffe rs ON b1.rohstoff=rs.rnummer
   JOIN beh b2 ON b2.behnr=qz.ziel) a
INNER JOIN
  (SELECT rohstoff FROM rezpos
   WHERE reznr=:rn AND typ=1) b
USING (rohstoff)
Im Ergebnis sieht man, dass Behälter 130 als einziger von allen Rohstoffen erreicht werden kann, und genau diesen suche ich! (Es kann generell durchaus mehrere Ziele geben bei manchen Rezepturen.)
Im Grunde lande ich, der noch keinen Kaffee hatte, wieder bei etwas ähnlichem von gestern: Rohstoffe pro Ziel zählen, und mit der Anzahl Rohstoffe im Rezept vergleichen. Das geht doch sicherlich hübscher

@shima: Ich habe den Primärindex nur auf der (an sich hier eher unbeteiligten) Spalte "id" liegen, aber einen UNIQUE-Index auf (quelle, ziel). Beraube ich mich da ggf. Vorteile, die ich im Primärindex hätte wie du ihn vorschlägst?

Danke euch schon mal!
"When one person suffers from a delusion, it is called insanity. When a million people suffer from a delusion, it is called religion." (Richard Dawkins)
  Mit Zitat antworten Zitat
Benutzerbild von DeddyH
DeddyH

Registriert seit: 17. Sep 2006
Ort: Barchfeld
27.534 Beiträge
 
Delphi 11 Alexandria
 
#9

AW: SQL Vereinfachen

  Alt 17. Feb 2012, 10:47
Die Lösung von omata bringt auch nicht das gewünschte Ergebnis? Etwas Ähnliches hatte ich nämlich auch im Sinn: zuerst alle passenden per INNER JOIN ermitteln und dann die Anzahl mit der der Rezeptpositionen vergleichen.
Detlef
"Ich habe Angst vor dem Tag, an dem die Technologie unsere menschlichen Interaktionen übertrumpft. Die Welt wird eine Generation von Idioten bekommen." (Albert Einstein)
Dieser Tag ist längst gekommen
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#10

AW: SQL Vereinfachen

  Alt 17. Feb 2012, 10:53
Das Problem scheint wohl doch komplexer zu sein als ich gestern noch im guten Glauben annahm. Fast alle Lösungen leiden unter dem selben Problem: Ich erhalte eine Liste aller Zielbehälter, die von jedem Rohstoff einzeln erreicht werden können, wobei ich gerne wüsste welche Ziele von allen Rohstoffen gleichsam erreicht werden können. Was ich leicht bekomme ist also..


Im Ergebnis sieht man, dass Behälter 130 als einziger von allen Rohstoffen erreicht werden kann, und genau diesen suche ich! (Es kann generell durchaus mehrere Ziele geben bei manchen Rezepturen.)
Das Problem ist vielleicht auch, dass Du die Aufgabenstellung nicht präzise formulierst- bis jetzt. Was bedeutet denn "kann auch mehrere"? Kann sein, dass es die gibt, interessiert mich aber nicht?
Ich will jeden einzelnen Zielbehälter, der geht (Sonst wird es ja keine Liste)?
..
Gruß, Jo
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 2  1 2   

Themen-Optionen Thema durchsuchen
Thema durchsuchen:

Erweiterte Suche
Ansicht

Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 12:19 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz