Datenbank - Anfragen

E

eroveli65

Benutzer
Beiträge
7
Hi,

ich sitze schon den ganzen Abend dran aber komm irgendiwe nicht zum Punkt.

Sondern will ich eine Datenbank Anfrage starten:

Ich will wissen wie die Lieferanten heißen, die die schwersten Teile liefern?

Vorhandene Tabellen: (Spalten habe ich nur mal ins deutsche übersetzt)

supplier : lieferant, lieferantennr., stadt
part: teilenr, teilename, gewicht, stadt
supp_part: teilenr, lieferantennr, menge

hierzu habe ich als erstes das hier geschrieben:

select a.suppname as "Lieferant",max(k.weight) as "Schwerstes Teil"
from company.supplier a,company.part k
group by a.suppname

ausgabe: alle lieferanten und alle mit dem max gewicht , daher schonmal FALSCH

dann ist mir aber bewusst geworden, dass hierzu auch die spalten lieferantennr und teilenr benutzen muss, damit das maximal gewicht auch dem lieferanten zugeordnet werden kann

sozusagen: Lieferanten => Lieferrantennr => Teilenr. => Gewicht

dann hab ich folgendes geschrieben:

select a.suppname as "Lieferant"
from company.supplier a
where a.suppno in
(select a1.suppno
from COMPANY.supp_part a1
where a1.partno in
(select a2.partno
from COMPANY.part a2
where max(a2.weight) as "Max Gewicht));

Ausgabe: error

Ich weiß auch nicht weiter, abgesehen davon das ich purer anfänger bin, zerbricht das mir den Kopf und kann nicht davon loslassen...

Ich würde mich freuen, wenn jmd mir paar tipps ggf beispiele geben kann auch wenn es für euch ein kinderspielchen ist.

Vielen Dank
 
Werbung:
Da gibt es viele Wege, da du kein konkretes Gewicht von bis geschrieben hast, würde ich einfach mal eine Übersicht machen. Die AND p.gewicht >= Bedingung ist nur optional:
Code: 
SELECT    s.lieferantennr,
        s.lieferant,
        p.teilenr,
        p.teilname,
        p.gewicht
FROM    supplier s,
        supp_part z,
        part p
WHERE    s.lieferantennr = z.lieferantennr
AND        z.teilenr = p.teilenr
AND        p.gewicht >= 100
ORDER BY p.gewicht,s.lieferant

Alternativ geht auch:
Code: 
SELECT    s.lieferantennr,
        s.lieferant,
        p.teilenr,
        p.teilname,
        p.gewicht
FROM    supplier s,
        supp_part z,
        part p
WHERE    s.lieferantennr = z.lieferantennr
AND        z.teilenr = p.teilenr
AND        p.gewicht = (    SELECT    max(gewicht)
                        FROM    part )
ORDER BY p.gewicht,s.lieferant
 
Super !!

genau so habe ich es mir vorgestellt mit mein Überlegung, das sieht schön sauber aus, das hat mir bei einigen anderen Übungen geholfen und hab was dazu gelernt ;-)

jetzt hänge ich wieder an einer Stelle fest

hab jetzt folgenden code erstellt:

Code: 
select p.partname,
      s.suppname,
      z.quantity   
from  company.supplier s,
      company.supp_part z,
      company.part p         
where  s.suppno=z.suppno
and    z.partno=p.partno
order by p.partname,
        s.suppname

dieser code wiedergibt die Tabelle so wie ich es haben will

Aber jetzt will ich eine extra Zeile unter der Tabelle, die die Summe der Menge(Quantity)ausgibt die wie folgt aussehen soll: Summe: 31000

also um die Summe zuberechnen ist mir denke ich mal klar glaube ich: sum(menge)

Kann man dies auch sortieren, dass "Summe:" unter Partname steht und die Summe aus der Menge unter der Menge(Quantity) steht, mit einer Zeile Abstand zur Tabelle

Hat da vllt jemand ne idee wie man dies elegant hinkriegen würde. Ich wollte dies durch eine einzige Anfrage hinkriegen
 
Ich wüsste da schon einen Weg, aber kann dir nicht sagen ob das der eleganteste ist (eigentlich würde ich eher sagen ein bischen gefrickel, aber sollte laufen):
Code: 
SELECT    *
FROM    (    SELECT    p.partname,
                    s.suppname,
                    z.quantity
            FROM    company.supplier s,
                    company.supp_part z,
                    company.part p
            WHERE    s.suppno = z.suppno
            AND        z.partno = p.partno
            UNION ALL
            SELECT    'zzz Summe' AS partname,
                    '' AS suppname,
                    (    SELECT    sum(z.quantity)
                        FROM    company.supplier s,
                                company.supp_part z,
                                company.part p
                        WHERE    s.suppno = z.suppno
                        AND        z.partno = p.partno ) AS quantity ) tabelle
ORDER BY partname, suppname

Ist natürlich etwas unkomfortabel aber sollte gehen. Testen kannst du mal:
Code: 
SELECT    *
FROM    (    SELECT    p.partname,
                    s.suppname,
                    z.quantity
            FROM    company.supplier s,
                    company.supp_part z,
                    company.part p
            WHERE    s.suppno = z.suppno
            AND        z.partno = p.partno
            UNION ALL
            SELECT    'zzz Summe' AS partname,
                    '' AS suppname,
                    (    SELECT    sum(z.quantity)
                        FROM    tabelle ) ) tabelle
ORDER BY partname, suppname

Geht aber glaube ich nicht. Eine komulierte Summenspalte habe ich noch als Beispiel gefunden :
Code: 
SELECT
 
  a.wert, (SELECT SUM(b.wert) FROM SummeTest b WHERE b.recid <= a.recid) AS 'kumulative Summe'
 
FROM
 
  SummeTest a

Allerdings musst du natürlich ein passendes WHERE Kriterium haben. Im Idealfall ordnest du also nicht nach Namen sondern hast irgendeine ID nach der sortiert werden kann. Ich habs mal versucht mit ROW_NUMBER() aber ich weiss nicht ob es die Funktion bei Oracle gibt und funktionieren tut das auch bei mir nicht so richtig:
Code: 
SELECT    ROW_NUMBER() OVER (ORDER BY p1.partname, s1.suppname ) AS zeilennr,
        p1.partname,
        s1.suppname,
        z1.quantity,
        (    SELECT    sum(z2.quantity)
            FROM    company.supplier s2,
                    company.supp_part z2,
                    company.part p2
            WHERE    s2.suppno = z2.suppno
            AND        z2.partno = p2.partno
            AND        ROW_NUMBER() OVER (ORDER BY p2.partname, s2.suppname ) <= zeilennr ) AS [kumulative Summe]
FROM    company.supplier s1,
        company.supp_part z1,
        company.part p1
WHERE    s1.suppno = z1.suppno
AND        z1.partno = p1.partno
ORDER BY p1.partname, s1.suppname
 
musste den code leicht abändern, da ein fehler auftrat

Code: 
SELECT    *
FROM    (  SELECT  p.partname,
                    s.suppname,
                    z.quantity
            FROM    supplier s,
                    supp_part z,
                    part p
            WHERE  s.suppno = z.suppno
            AND    z.partno = p.partno
            UNION ALL
            select 'Summe:',' ',sum(quantity)
            from supp_part)
order by p.partname,s.suppname;

aber ich habe vergessen das ich zwischen der summe und der tabelle eine leere zeile haben wollte dafür hab ich den code erweitert:

Code: 
SELECT    *
FROM    (  SELECT  p.partname,
                    s.suppname,
                    z.quantity
            FROM    supplier s,
                    supp_part z,
                    part p
            WHERE  s.suppno = z.suppno
            AND    z.partno = p.partno
            UNION ALL
            select ' ',' ',NULL
            from dual
            union all
            select 'Summe:',' ',sum(quantity)
            from supp_part)
order by p.partname,s.suppname;

Habe für die leere zeile den dual dummy benutzt, weiß nicht ob es noch eine andere methode dafür gibts...aber da hab ich jetzt das problem das durch den order by die leere zeile an erste stelle in der tabelle vorkommt und in der leeren zeile kriege ich in der 3 spalte die ' NULL ' nicht weg kann es nur durch eine Zahl ersetzen wie krieg ich das alles hin??
 
Nunja, du kannst dort eine 0 statt NULL angeben, aber du versuchst hier mehr oder weniger Layout spezifische Dinge über die Abfrage zu regeln, was eigentlich nicht so gedacht ist. Eine andere Möglichkeit ausser NULL oder 0 anzuzeigen sehe ich nicht, es sei denn du machst ein cast(z.quantity) AS VARCHAR(10) und hast somit eine VARCHAR() Spalte, dann geht aber eventuell dein sum() nicht mehr. Das Layout sollte man eventuell dann doch einem Frontend für die DB überlassen.
 
und wie kriege ich es hin, das die leere Zeile nicht an erster Stelle landet wenn ich order by benutze??

siehe code:
Code: 
SELECT    *
FROM    (  SELECT  p.partname,
                    s.suppname,
                    z.quantity
            FROM    supplier s,
                    supp_part z,
                    part p
            WHERE  s.suppno = z.suppno
            AND    z.partno = p.partno
            UNION ALL
            select ' ',' ',NULL
            from dual
 
            UNION ALL
            select 'Summe:',' ',sum(quantity)
            from supp_part)
order by p.partname,s.suppname;

kann man die Stelle
Code: 
UNION ALL
select ' ',' ',NULL
from dual
auch als Unterabfrage benutzen? oder wie soll man das hinkriegen?
 
Eigentlich müsste in diesem Fall auch die Summen Spalte irgendwo anders stehen, denn du hast ja das "zzz" bei mir raus genommen. Man könnte aber noch etwas tricksen:
Code: 
-- Den eigentlichen Spaltennamen kannst du über AS nochmal nach belieben
-- anpassen. Mit einer CASE Schleife nehme ich die zzz wieder raus, die
-- ich zur Sortierung nutze.
SELECT    (    CASE
            WHEN    partname LIKE 'zzz%'
            THEN    right(partname,len(partname)-3)
            ELSE    partname
            END ) AS Artikel,
        suppname AS Lieferant,
        quantity AS Menge
FROM (    SELECT    p1.partname,
                s1.suppname,
                z1.quantity
        FROM    supplier s1,
                supp_part z1,
                part p1
        WHERE    s1.suppno = z1.suppno
        AND        z1.partno = p1.partno
        UNION ALL
        -- Du musst den Werten mit AS einen Spaltennamen geben, sonst sind
        -- deine Spalten namenlos. Da wir aber im äußeren SELECT kein * mehr
        -- verwenden sondern Spaltennamen, müssen diese auch vorhanden sein.
        SELECT    'zzz' AS partname,
                '' AS suppname,
                0 AS quantity
        UNION ALL
        SELECT    'zzzSumme:' AS partname,
                '' AS suppname,
                sum(z2.quantity) AS quantity
        -- An dieser Stelle hattest du nur eine Tabelle ausgewählt um die
        -- Summe aus quantity zu bilden. Das kann aber dazu führen das deine
        -- Summe nicht mit der Ausgabe übereinstimmt wenn z.B. In supp_part
        -- Einträge existieren, die keinen Eintrag in supplier oder part haben.
        -- Auch wenn das eigentlich nicht sein sollte muss man das im Hinterkopf
        -- behalten und auch bei weiteren Bedingungen zur Auswahl von Daten
        -- kann das schnell Verwirrung auslösen. Daher nehme ich das vorsichts-
        -- halber mit rein.
        FROM    supplier s2,
                supp_part z2,
                part p2
        WHERE    s2.suppno = z2.suppno
        AND        z2.partno = p2.partno )
-- Die ORDER BY Bedingung bezieht sich auf die Spalten, daher müsste zzz bei
-- der Sortierung berücksichtigt werden.
ORDER BY partname,suppname
 
Danke..

habe es hingekriegt :)

von dir lerne ich mehr als von meiner Dozenten, und ich weiß es sehr zu schätzen das du dir zeit nimmst mir zu helfen ;-) das macht nicht jeder Danke ;-)

Jetzt bin ich grad dabei eine weitere Aufgabe zu machen, wo ich noch nichts gemacht habe, sondern

Will ich de Firmensitzes des Lieferanten (Suppname) „Smith“ nach Berlin(City) und auch die Herstellungsorte(Part --> City) seiner Produkte nach Berlin verlagern. Diejenigen Produkte, die nicht nur von ihm geliefert werden, sollen davon nicht betroffen sein.

Tabelle:
35ic4k6.jpg


Das heisst, ich muss in der Tabelle " Supplier die Spalte "City" ändern, und in der Tabelle (Part) die Spalte (City) ändern aber da hab ich das Problem das auch andere Firmen das selbe Produkt liefern, das heisst ich muss neue Zeilen entwerfen, aber dann ändert sich die (Partno) womit ich dann auch die Tabelle (Supp_Part) ändern muss.

Am besten wäre es beim update-statement eine Unterabfrage zu benutzen.

Dafür habe ich ein Code geschrieben der nicht elegant ist:

Code: 
update supplier
set city = 'Berlin'
where suppname = 'Smith'
and suppno = 'S1';
 
update supp_part
set partno = 'P7'
where suppno= 'S1'
and partno = 'P1';
update supp_part
set partno = 'P8'
where suppno= 'S1'
and partno = 'P2';
update supp_part
set partno = 'P9'
where suppno = 'S1'
and partno ='P3';
update supp_part
set partno = 'P10'
where suppno = 'S1'
and partno = 'P4';
update supp_part
set partno = 'P11'
where suppno= 'S1'
and partno='P5';
update supp_part
set partno ='P12'
where suppno ='S1'
and partno = 'P6';
 
insert into part (partno,partname,color,weight,city)
values ('P7','Nut','Red','12','Berlin');
insert into part (partno,partname,color,weight,city)
values ('P8','Bolt','Green','17','Berlin');
insert into part (partno,partname,color,weight,city)
values ('P9','Screw','Blue','17','Berlin');
insert into part (partno,partname,color,weight,city)
values ('P10','Screw','Red','14','Berlin');
insert into part (partno,partname,color,weight,city)
values ('P11','Cam','Blue','12','Berlin');
insert into part (partno,partname,color,weight,city)
values ('P12','Cog','Red','19','Berlin');

aber es funzt nicht ganz.

- Firmensitz wird geändert = ok
- aber der Rest nicht
--> in der Tabelle Part fügt er (P7,P8,P9) ein aber wiederholt es einmal und fügt nicht (P10,P11,P12) dafür ein.
--> in der Tabelle Supp_part ändert er alle (Partno) die zur (Suppno) der Firma gehören nur in (P8) ab

wie kann ich die beiden Fehler beheben und wie kann ich beim update statement eine Unterabfrage einbauen, was denke ich sinnvoller wäre?
 
Also das mit dem INSERT muss funktionieren. Du hast eventuell etwas doppelt ausgeführt oder nur teilweise markiert so das nur der markierte Teil ausgeführt wurde. Auch das UPDATE der Partnummern muss gehen.
 
Also der Code an sich stimmt meinst du?? gut kann sein das ich beim makrieren und asführen was falsch gemacht habe

Wie sieht es aus mit einer unterabfrage.. wie baut man sowas in ein update-statement ein im bsp der Änderung der Partno?
 
Da stellt sich jetzt eher die Frage stimmt das Datenmodell. Wenn ein Part (eigentlich gleiche Nummer) an unterschiedlichen Standorten produziert wird muss der Standort eigentlich in eine eigene Tabelle ausgelagert werden. Stichwort Relationales Datenbank http://de.wikipedia.org/wiki/Relationale_Datenbank

Natürlich kann man das auch so machen wie du es vor hattest und damit leben das dieser Grundsatz in einzelfällen durchbrochen wird. Dann muss man sich aber Fragen ob man für sowas wirklich ein automatisiertes Script braucht. Das geht, ist aber nen bischen Arbeit.
 
Habe es hingekriegt :-)

habe jetzt noch ein letztes problem...

habe diesen code erstellt:

Code: 
select a.artbez,
        a.artnr,
        b.menge,
        b.preis,
        (select nvl2(sum(b.menge), 'bereits bestellt', 'Ladenhüter')
        from aufpos b where b.artnr = a.artnr) as Status
from  artst a left join aufpos b on b.artnr = a.artnr
order by a.artbez;

jetzt will ich nur noch die werte die als NULL angezeigt werden in den Spalten Preis und Menge durch 0 ersetzen
 
Code: 
select a.artbez,
        a.artnr,
        isnull(b.menge,0),
        isnull(b.preis,0),
        (select nvl2(sum(b.menge), 'bereits bestellt', 'Ladenhüter')
        from aufpos b where b.artnr = a.artnr) as Status
from  artst a left join aufpos b on b.artnr = a.artnr
order by a.artbez;
So wäre es bei MSSQL, gibt sicher isnull() oder etwas ähnliches auch bei Oracle.
 
Werbung:

Ähnliche Themen

L
Pokemon Datenbank Problem
Antworten
9
Aufrufe
3K
akretschmer
akretschmer
B
SQL-Anfragen
Antworten
2
Aufrufe
2K
bluepeople12
B
C
Textsuche in Datenbank
Antworten
7
Aufrufe
3K
chuchi
C
Zurück
Oben