OBS/Adminhilfe/MySQL Aufgaben: Unterschied zwischen den Versionen

Aus OBS Wiki
Zur Navigation springen Zur Suche springen
Für diese Seite wurde eine Zugriffsbeschränkung eingerichtet. Falls du diese Nachricht siehst, bist du nicht berechtigt, diese Seite einzusehen.
Keine Bearbeitungszusammenfassung
Keine Bearbeitungszusammenfassung
 
(18 dazwischenliegende Versionen von 4 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
{{Adminhilfe}}<font size="2" color="#000000" face="Arial"> </font>
{{ZugriffOBS}}


<div><center><font size="6">SQL Aufgaben</font></center></div><div><font size="3" color="#000000"><br /><div><font size="3" color="#000000"><u>1. Alle Rechnungen aus dem letzten Jahr.</u><br />''SELECT * FROM rechsta''''WHERE (a_datum, 4) = year(now())''<br /><br /><div><font size="3" color="#000000"><u>2. Liste der Personen mit der Anzahl an Rechnungen des Vorjahres.</u><br />''SELECT ps_name, count(ps_name) as anzahl FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''WHERE LEFT(a_datum, 4) = year(now())-1''''GROUP BY ps_name''<br /><br /><div><font size="3" color="#000000"><u>3. Pro Kunde die Bruttosummen aller Rechnungen.</u><br />''SELECT ps_nr, ps_name, ps_vorname, sum(a_bbetrag) as bruttosumme FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''WHERE not isnull(a_bbetrag)''''GROUP BY ps_nr''<br /><br /><div><font size="3" color="#000000"><u>4. Verkaufsmengen der Artikel, anhand der Rechnungspositionen, absteigend sortiert nach der Verkaufsmenge.</u><br />''SELECT az_artnr, az_bez1, az_bez2, sum(az_menge) as menge FROM rechpos''''WHERE az_bez1 &lt;&gt; 0''''GROUP BY az_bez1 ''''ORDER BY menge DESC''<br /><br /><div><font size="3" color="#000000"><u>5. Auflistung aller gekauften Artikel mit Gesamtmenge für die Person 102057 (Tabellen: Perssta, Rechsta und Rechpos)</u><div><font size="3" color="#000000"><br />''SELECT ps_nr, ps_name, az_artnr, az_bez1, az_bez2, sum(az_menge) FROM perssta''''LEFT JOIN rechsta ON ps_nr = a_knr''''LEFT JOIN rechpos ON a_nr = az_nr''''<nowiki>WHERE ps_nr = '102057' AND az_menge &lt;&gt; 0 AND az_bez1 &lt;&gt; ''</nowiki>''''GROUP BY az_bez1''<br /><br /><div><font size="3" color="#000000"><u>6. Eine Liste aller Warengruppen, mit Anzahl der Artikel und ihrer Verkaufsmengen, im Jahr 2011 und dem letzten Rechnungsdatum   </u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>(Tabellen: warengr, artikel, rechpos und rechsta)</u><br />''SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, az_nr''''FROM warengr''''LEFT JOIN artikel ON wg_nr = a_wgr''''LEFT JOIN rechpos ON artikel.a_nr  = az_artnr''''LEFT JOIN rechsta ON rechsta.a_nr  = az_nr''''WHERE year(rechsta.a_datum) = '2011'''''GROUP BY wg_nr''<br /><br /><div><font size="3" color="#000000"><u>7. Liste aller Personen - mit Personennummer, Name (Vorname + Nachname in einer Spalte) und der Anzahl der Aufträge - die im ersten </u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>Quartal des aktuellen Jahres (Hinweis: Feld a_datum)  Aufträge haben. (Neue SQL-Funktionen hierbei: CONCAT und QUARTER)</u><br />''SELECT ps_nr, concat(ps_vorname, ps_name), count(ps_nr) as aufträge FROM perssta''''LEFT JOIN auftrag ON a_knr = ps_nr''''WHERE quarter(a_datum) = 1 ''''AND year(a_datum) = year(now())''''GROUP BY ps_nr''''ORDER BY ps_nr''<br /><br /><div><font size="3" color="#000000"><u>8. Die Liste von 1. erweitert um die Informationen höchster Auftragswert und durchschnittlicher Auftragswert.</u><div><font size="3" color="#000000">    </font><font size="3" color="#000000"><u>(Neue SQL-Funktionen hierbei: AVG und MAX)</u><br />''SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, ''''        ''</font><font size="3">''    max(rechsta.a_datum) as letzte_rechnung, max(a_bbetrag) as höchster_auftragswert, ''''        ''</font><font size="3">''    avg(a_bbetrag)as durchschnittlicher_auftragswert''''FROM warengr''''LEFT JOIN artikel ON wg_nr = a_wgr''''LEFT JOIN rechpos ON artikel.a_nr  = az_artnr''''LEFT JOIN rechsta ON rechsta.a_nr  = az_nr''''WHERE year(rechsta.a_datum) = '2011'''''GROUP BY wg_nr''<br /><br /><u>9. Alle Artikel anzeigen, die von allen Lieferanten nicht mehr lieferbar sind. (Hinweis: ein Artikel kann mehrere Lieferanten haben) Dann auf </u>    </font><font size="3"><u>Inaktiv setzen.</u><br />''<u>1. Lösung (0,390 sec)</u>''''SELECT a.a_nr, a.a_name1, a.a_name2, ek_bemerk FROM artikel as a''''LEFT JOIN (SELECT a_nr, min(ek_nolieferbar) as mini FROM artikel ''''                     LEFT JOIN artiek ON a_nr = ek_artnr ''''                    GROUP BY a_nr) as b ON b.a_nr = a.a_nr''''LEFT JOIN artiek ON a.a_nr = ek_artnr''''WHERE ek_nolieferbar = b.mini ''''AND ek_nolieferbar &gt; '0' ''''AND A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''GROUP BY a.a_nr''''ORDER BY a.a_nr''<br /><br />''<u>2.Lösung (0,187 sec)</u>''''SELECT a_nr, a_name1, a_name2, a.ek_bemerk ''''FROM (SELECT b.ek_artnr, b.ek_nolieferbar, if(min(b.ek_nolieferbar) &gt; 0, 1, 0) as neu, b.ek_bemerk''''        ''</font><font size="3">''FROM artiek as b ''''           GROUP BY b.ek_artnr) as a ''''LEFT JOIN artikel ON a_nr = a.ek_artnr''''WHERE a.neu = a.ek_nolieferbar ''''AND a.ek_nolieferbar &gt; 0 ''''AND A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''ORDER BY a_nr''<br /><br />''<u>3. Lösung (0,265 sec)</u>''''SELECT a_nr, a_name1, a_name2, ek_bemerk FROM artiek as a''''LEFT JOIN artikel ON a_nr = a.ek_artnr''''where A_INAKTIV &lt;&gt; '1' ''''AND not isnull(ek_nolieferbar)  ''''AND (a_bestand+a_abestand) = 0''''GROUP BY ek_artnr''''Having min(ek_nolieferbar) &gt; '0'''<br /><br />''<u>4. Lösung von Jörn (0,280 sec)</u>''''SELECT a_nr, a_name1 FROM artikel''''WHERE ''''(SELECT min( ek_nolieferbar) FROM ARTIEK ''''WHERE a_nr=ek_artnr) = '1'''''AND a_inaktiv &lt;&gt; "1"''''AND (a_bestand+a_abestand) = 0''[[Category:html]]
 
----
=SQL Aufgaben=
[[Benutzer:Auto import|Auto import]] 15:26, 25. Aug. 2011 (CEST)
 
'''1. Alle Im letzten Jahr erstellten Rechnungen. (Tabelle: RECHSTA)'''
<source lang="sql">
SELECT * FROM rechsta  
WHERE LEFT(a_datum, 4) = year(now())-1</source>
 
'''2. Liste der Personen mit der Anzahl an Rechnungen des Vorjahres. (Tabelle: PERSSTA) (Lösung mit JOIN)'''
<source lang="sql">
SELECT ps_name, count(ps_name) as anzahl FROM perssta
LEFT JOIN rechsta ON ps_nr = a_knr
WHERE year(a_datum) = year(now())-1
GROUP BY ps_name</source>
 
'''3. Pro Kunde die Bruttosummen aller Rechnungen.'''
<source lang="SQL">
SELECT ps_nr, ps_name, ps_vorname, sum(a_bbetrag) as bruttosumme FROM perssta
LEFT JOIN rechsta ON ps_nr = a_knr
WHERE not isnull(a_bbetrag)
GROUP BY ps_nr</source>
 
'''4. Verkaufsmengen der Artikel, anhand der Rechnungspositionen, absteigend sortiert nach der Verkaufsmenge. (Tabelle: RECHPOS)'''
<source lang="sql">
SELECT az_artnr, az_bez1, az_bez2, sum(az_menge) as menge FROM rechpos
WHERE az_artnr <> ''
GROUP BY az_artnr
ORDER BY menge DESC</source>
 
'''5. Auflistung aller gekauften Artikel mit Gesamtmenge für die Person 102057 (Tabellen: PERSSTA, RECHPOS)'''
<source lang="SQL">
SELECT ps_nr, ps_name, az_artnr, az_bez1, az_bez2, sum(az_menge) FROM perssta
LEFT JOIN rechsta ON ps_nr = a_knr
LEFT JOIN rechpos ON a_nr = az_nr
WHERE ps_nr = '102057' AND az_menge <> 0 AND az_bez1 <> ''
GROUP BY az_bez1</source>
 
'''6. Eine Liste aller Warengruppen, mit Anzahl der Artikel und ihrer Verkaufsmengen, im Jahr 2011 und dem letzten Rechnungsdatum(Tabellen: warengr, artikel, rechpos und rechsta)'''
<source lang="SQL">
SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, az_nr FROM warengr
LEFT JOIN artikel ON wg_nr = a_wgr
LEFT JOIN rechpos ON artikel.a_nr  = az_artnr
LEFT JOIN rechsta ON rechsta.a_nr  = az_nr
WHERE year(rechsta.a_datum) = '2011'
GROUP BY wg_nr</source>
 
'''7. Liste aller Personen - mit Personennummer, Name (Vorname + Nachname in einer Spalte) und der Anzahl der Aufträge - die im ersten Quartal des aktuellen Jahres (Hinweis: Feld a_datum)  Aufträge haben. (Neue SQL-Funktionen hierbei: CONCAT und QUARTER)'''
<source lang="SQL">
SELECT ps_nr, concat(ps_vorname, ps_name), count(ps_nr) as aufträge FROM perssta
LEFT JOIN auftrag ON a_knr = ps_nr
WHERE quarter(a_datum) = 1
AND year(a_datum) = year(now())
GROUP BY ps_nr
ORDER BY ps_nr</source>
 
'''8. Die Liste von 6. erweitert um die Informationen höchster Auftragswert und durchschnittlicher Auftragswert.(Neue SQL-Funktionen hierbei: AVG und MAX)'''
<source lang="SQL">
SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, max(a_bbetrag) as höchster_auftragswert, avg(a_bbetrag)as durchschnittlicher_auftragswert FROM warengr
LEFT JOIN artikel ON wg_nr = a_wgr
LEFT JOIN rechpos ON artikel.a_nr  = az_artnr
LEFT JOIN rechsta ON rechsta.a_nr  = az_nr
WHERE year(rechsta.a_datum) = '2011'
GROUP BY wg_nr</source>
 
'''9. Alle Artikel ohne Bestand anzeigen, die von allen Lieferanten nicht mehr lieferbar sind. (Hinweis: ein Artikel kann mehrere Lieferanten haben / Tabelle: ARTIEK)'''
 
''1. Lösung (obs_mysql_bergau_thiel 0,968 sec)''
<source lang="SQL">
SELECT a.a_nr, a.a_name1, a.a_name2, ek_bemerk FROM artikel as a
LEFT JOIN (SELECT a_nr, min(ek_nolieferbar) as mini FROM artikel
LEFT JOIN artiek ON a_nr = ek_artnr
GROUP BY a_nr) as b ON b.a_nr = a.a_nr
LEFT JOIN artiek ON a.a_nr = ek_artnr
WHERE ek_nolieferbar = b.mini
AND ek_nolieferbar > '0'
AND A_INAKTIV <> '1'
AND not isnull(ek_nolieferbar)
AND (a_bestand+a_abestand) = 0
GROUP BY a.a_nr
ORDER BY a.a_nr</source>
 
''2.Lösung (obs_mysql_bergau_thiel 0,375 sec)''
<source lang="SQL">
SELECT a_nr, a_name1, a_name2, a.ek_bemerk
FROM (SELECT b.ek_artnr, b.ek_nolieferbar, if(min(b.ek_nolieferbar) > 0, 1, 0) as neu, b.ek_bemerk FROM artiek as b
GROUP BY b.ek_artnr) as a
LEFT JOIN artikel ON a_nr = a.ek_artnr
WHERE a.neu = a.ek_nolieferbar
AND a.ek_nolieferbar > 0
AND A_INAKTIV <> '1'
AND not isnull(ek_nolieferbar)
AND (a_bestand+a_abestand) = 0
ORDER BY a_nr
</source>
 
''3. Lösung (obs_mysql_bergau_thiel 1,265 sec)''
<source lang="SQL">
SELECT a_nr, a_name1, a_name2, ek_bemerk FROM artiek as a
LEFT JOIN artikel ON a_nr = a.ek_artnr
where A_INAKTIV <> '1'
AND not isnull(ek_nolieferbar)
AND (a_bestand+a_abestand) = 0
GROUP BY ek_artnr
Having min(ek_nolieferbar) > '0'
</source>
 
''4. Lösung von Jörn (obs_mysql_bergau_thiel 0,235 sec)''
<source lang="SQL">
SELECT a_nr, a_name1 FROM artikel
WHERE(SELECT MIN( ek_nolieferbar) FROM ARTIEK
WHERE a_nr=ek_artnr) = '1'
AND a_inaktiv <> "1"
AND (a_bestand + a_abestand) = 0
</source>
 
''5. Lösung von Nimz (obs_mysql_bergau_thiel 0,141 sec)''
<source lang="SQL">
SELECT a_nr, a_name1, a_name2, ek_bemerk
FROM (
    SELECT ek_artnr, ek_bemerk, MIN(ek_nolieferbar) AS liefbar
    FROM artiek
    GROUP BY ek_artnr
    HAVING liefbar=1) eknolief
JOIN artikel
    ON a_nr=ek_artnr
    AND a_inaktiv<>'1'
    AND (a_bestand + a_abestand) = 0;
</source>
[[Category:html]]

Aktuelle Version vom 11. August 2023, 15:44 Uhr

Dies ist eine zugriffsgeschützte Seite.


Admin-Hilfe

FAQ
FIBU
Häufige Fehler FAQs
Allgemeines
Tools
Widgets
Wiki Hilfe
Syntax
Wiki-Gruppen Benutzerlisten
MySQL
MySQL Installation
Programmierung und Mergen
Delphi
Einrichtung
MMX
Git/SmartGit
Programm Update
Schaltflächen
F10: Weitere Funktionen
PAX
Nützliche Funktionen
SteVe
OBS-Administration / Installation
Einrichtung
MySQL
OBS
Dienste
Kasse/Notfallkasse
Replikation
Zentrale
Fleet-Management einrichten
VirtualUI
OBS Umzug
Admin Funktionen
Service Firmen
F10
O Support
D Programmierer-Tools
V Crypt Autologin Edit
J Update Marko Lib/Pmode/Script Lib
kundenspezifische Anpassungen
OBS Protokolle
OBS DEMO
Shop-Administration
modified eCommerce
modified eCommerce 2.x
modified eCommerce 1.06
Amazon
VShop 4.0
Kundeninformationen
OfficeStar
MDS


SQL Aufgaben

1. Alle Im letzten Jahr erstellten Rechnungen. (Tabelle: RECHSTA) 

 SELECT * FROM rechsta 
 WHERE LEFT(a_datum, 4) = year(now())-1

2. Liste der Personen mit der Anzahl an Rechnungen des Vorjahres. (Tabelle: PERSSTA) (Lösung mit JOIN) 

 SELECT ps_name, count(ps_name) as anzahl FROM perssta
 LEFT JOIN rechsta ON ps_nr = a_knr
 WHERE year(a_datum) = year(now())-1
 GROUP BY ps_name

3. Pro Kunde die Bruttosummen aller Rechnungen. 

 SELECT ps_nr, ps_name, ps_vorname, sum(a_bbetrag) as bruttosumme FROM perssta
 LEFT JOIN rechsta ON ps_nr = a_knr
 WHERE not isnull(a_bbetrag)
 GROUP BY ps_nr

4. Verkaufsmengen der Artikel, anhand der Rechnungspositionen, absteigend sortiert nach der Verkaufsmenge. (Tabelle: RECHPOS) 

 SELECT az_artnr, az_bez1, az_bez2, sum(az_menge) as menge FROM rechpos
 WHERE az_artnr <> ''
 GROUP BY az_artnr
 ORDER BY menge DESC

5. Auflistung aller gekauften Artikel mit Gesamtmenge für die Person 102057 (Tabellen: PERSSTA, RECHPOS) 

 SELECT ps_nr, ps_name, az_artnr, az_bez1, az_bez2, sum(az_menge) FROM perssta
 LEFT JOIN rechsta ON ps_nr = a_knr
 LEFT JOIN rechpos ON a_nr = az_nr
 WHERE ps_nr = '102057' AND az_menge <> 0 AND az_bez1 <>  ''
 GROUP BY az_bez1

6. Eine Liste aller Warengruppen, mit Anzahl der Artikel und ihrer Verkaufsmengen, im Jahr 2011 und dem letzten Rechnungsdatum(Tabellen: warengr, artikel, rechpos und rechsta) 

 SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, az_nr FROM warengr
 LEFT JOIN artikel ON wg_nr = a_wgr
 LEFT JOIN rechpos ON artikel.a_nr  = az_artnr
 LEFT JOIN rechsta ON rechsta.a_nr  = az_nr
 WHERE year(rechsta.a_datum) = '2011'
 GROUP BY wg_nr

7. Liste aller Personen - mit Personennummer, Name (Vorname + Nachname in einer Spalte) und der Anzahl der Aufträge - die im ersten Quartal des aktuellen Jahres (Hinweis: Feld a_datum)  Aufträge haben. (Neue SQL-Funktionen hierbei: CONCAT und QUARTER) 

 SELECT ps_nr, concat(ps_vorname, ps_name), count(ps_nr) as aufträge FROM perssta
 LEFT JOIN auftrag ON a_knr = ps_nr
 WHERE quarter(a_datum) = 1
 AND year(a_datum) = year(now())
 GROUP BY ps_nr
 ORDER BY ps_nr

8. Die Liste von 6. erweitert um die Informationen höchster Auftragswert und durchschnittlicher Auftragswert.(Neue SQL-Funktionen hierbei: AVG und MAX) 

 SELECT wg_nr, wg_name, count(wg_nr) as artikel_anzahl, sum(az_menge) as verkaufsmengen, max(rechsta.a_datum) as letzte_rechnung, max(a_bbetrag) as höchster_auftragswert, avg(a_bbetrag)as durchschnittlicher_auftragswert FROM warengr
 LEFT JOIN artikel ON wg_nr = a_wgr
 LEFT JOIN rechpos ON artikel.a_nr  = az_artnr
 LEFT JOIN rechsta ON rechsta.a_nr  = az_nr
 WHERE year(rechsta.a_datum) = '2011'
 GROUP BY wg_nr

9. Alle Artikel ohne Bestand anzeigen, die von allen Lieferanten nicht mehr lieferbar sind. (Hinweis: ein Artikel kann mehrere Lieferanten haben / Tabelle: ARTIEK)

1. Lösung (obs_mysql_bergau_thiel 0,968 sec) 

 SELECT a.a_nr, a.a_name1, a.a_name2, ek_bemerk FROM artikel as a
 LEFT JOIN (SELECT a_nr, min(ek_nolieferbar) as mini FROM artikel 
 LEFT JOIN artiek ON a_nr = ek_artnr
 GROUP BY a_nr) as b ON b.a_nr = a.a_nr
 LEFT JOIN artiek ON a.a_nr = ek_artnr
 WHERE ek_nolieferbar = b.mini
 AND ek_nolieferbar > '0'
 AND A_INAKTIV <> '1'
 AND not isnull(ek_nolieferbar)
 AND (a_bestand+a_abestand) = 0
 GROUP BY a.a_nr
 ORDER BY a.a_nr

2.Lösung (obs_mysql_bergau_thiel 0,375 sec) 

 SELECT a_nr, a_name1, a_name2, a.ek_bemerk
 FROM (SELECT b.ek_artnr, b.ek_nolieferbar, if(min(b.ek_nolieferbar) > 0, 1, 0) as neu, b.ek_bemerk FROM artiek as b
 GROUP BY b.ek_artnr) as a
 LEFT JOIN artikel ON a_nr = a.ek_artnr
 WHERE a.neu = a.ek_nolieferbar
 AND a.ek_nolieferbar > 0
 AND A_INAKTIV <> '1'
 AND not isnull(ek_nolieferbar)
 AND (a_bestand+a_abestand) = 0
 ORDER BY a_nr

3. Lösung (obs_mysql_bergau_thiel 1,265 sec) 

 SELECT a_nr, a_name1, a_name2, ek_bemerk FROM artiek as a
 LEFT JOIN artikel ON a_nr = a.ek_artnr
 where A_INAKTIV <> '1'
 AND not isnull(ek_nolieferbar)
 AND (a_bestand+a_abestand) = 0
 GROUP BY ek_artnr
 Having min(ek_nolieferbar) > '0'

4. Lösung von Jörn (obs_mysql_bergau_thiel 0,235 sec) 

 SELECT a_nr, a_name1 FROM artikel
 WHERE(SELECT MIN( ek_nolieferbar) FROM ARTIEK
 WHERE a_nr=ek_artnr) = '1'
 AND a_inaktiv <> "1"
 AND (a_bestand + a_abestand) = 0

5. Lösung von Nimz (obs_mysql_bergau_thiel 0,141 sec) 

 SELECT a_nr, a_name1, a_name2, ek_bemerk 
 FROM (
     SELECT ek_artnr, ek_bemerk, MIN(ek_nolieferbar) AS liefbar
     FROM artiek 
     GROUP BY ek_artnr
     HAVING liefbar=1) eknolief
 JOIN artikel
     ON a_nr=ek_artnr
     AND a_inaktiv<>'1'
     AND (a_bestand + a_abestand) = 0;
Abgerufen von „http:///index.php?title=OBS/Adminhilfe/MySQL_Aufgaben&oldid=62481“