Hilfe bei Select-Statement/View

[19RooC90]

Hallo miteinander, ich habe nun seit geraumer Zeit an diesem Problem getüftelt aber ich sehe den Wald vor lauter Bäumen nicht mehr und finde keine Lösung. Oder es gibt für mein Problem keine Lösung. Ich weiß nicht mehr weiter darum benötige ich Rat und Hilfe von einem Externen der sich das Problem mal ansehen kann und mir weiterhilft.

Also zu aller erst zu meiner Aufgabe: Ich bastel seit längerem an einer Kontoverwaltung/Aktienverwaltung (reiner Privatzweck). Vorher habe ich alles in Excel abgetippert. Nun möchte ich mich mal meine Daten in eine Datenbank speisen und diese mithilfe von SQL-Statements und Views in naher Zukunft über eine Applikation abfragen und anzeigen lassen.

So sah meine Excel in etwa aus und so möchte ich meine Daten auch per SQL/View darstellen lassen.

Ist das möglich? Wenn ja Wie?

Ich habe auch eine erste "Lösung".

Erspartes
ID Datum Konto_Name Konto_ID Wert
1; 01.01.2014; Mercedes; 1; 100,00 €
2; 01.01.2014; BMW; 2; 125,00 €
3; 01.02.2014; Mercedes; 1; 100,00 €
4; 01.02.2014; Audi; 3; 150,00 €
5; 01.02.2014; BMW; 2; 125,00 €
6; 01.03.2014; Mercedes; 1; 100,00 €

Konto
ID Konto_Name
1; Mercedes
2; BMW
3; AUDI

Mit diesem Select erzeuge ich mir die "Innere_view" die im Nachgang für eine weitere View nur als Datengrundlage gilt.

select `hauptselect`.`Datum` AS `datum`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes`
where ((`verwaltung`.`erspartes`.`Konto_ID` = 1)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `A`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes`
where ((`verwaltung`.`erspartes`.`Konto_ID` = 2)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `B`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes` where ((`verwaltung`.`erspartes`.`Konto_ID` = 3)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `C`
from `verwaltung`.`erspartes` `hauptselect`
group by `hauptselect`.`Datum`

Danach erzeuge ich die "Oberview" die über das ganze nochmal abfragt und eine neue View erstellt.

select `x`.`datum` AS `Datum`,`x`.`A` AS `Mercedes`,`x`.`B` AS `BMW`,`x`.`C` AS `AUDI,
round((`x`.`A` + `x`.`B` + `x`.`C`),2) AS `Summe` from `verwaltung`.`innerview_erspartes` `x`

So nun mein eigentliches Problem, abgesehen davon das es ***hit Statements wahrscheinlich sind. Aber bin gerne offen für Code Verbesserungen

Wie man sehen kann muss ich noch für jedes Konto die "Konto_ID = x" mit übergeben. Es wird anhand des Datums schon gruppiert ich hätte es aber gerne so kurz wie möglich und das die Konten automatisch übernommen werden. Denn nehmen wir mal an es kommt ein neues Konto Volkswagen [id=4] hinzu so müsste ich immer die View anpassen. Das ist aber nicht sehr effizient. Ich hätte es gerne so das ich das Konto einfügen kann in die Tabellen, jeweils in Konto und in erspartes und dann die View "automatisch" die neue Spalte beim nächsten Start einliest. Das muss doch sicherlich mit dem Konto gejoint werden, nur weiß ich nicht wie ich das korrekt machen soll.

Ich hoffe es war einigermaßen verständlich und hoffe das mir hier jemand Helfen kann.

Bitte nehmt mir den langen Post nicht übel.

Gruß

 

[akretschmer]

Ich hoffe es war einigermaßen verständlich

für mich: leider nicht.

 

[19RooC90]

Hmm das ist schlecht :/ naja um Grund möchte ich das das Ergebnis meines selects so ausschaut wie im Screenshot.
Das Problem ist hierbei das mein Statement extrem lang und schwerfällig wird je mehr "konten" es gibt.
Bisher müsste ich jedes Konto manuell per id einbauen, gibt es eine Möglichkeit die alle Konten durchläuft, sie als Spalten deklariert und die Werte dazu ausgibt?

Sodass das Endergebnis wie in der Excel ausschaut ohne das ich ständig bei jedem kontoneuzugang die view oder das select anpassen muss.


Hoffe das es etwas besser geworden ist.

 

[akretschmer]

Das Problem ist hierbei das mein Statement extrem lang und schwerfällig wird je mehr "konten" es gibt.
Bisher müsste ich jedes Konto manuell per id einbauen, gibt es eine Möglichkeit die alle Konten durchläuft, sie als Spalten deklariert und die Werte dazu ausgibt?

Ah jetzt verstehe ich, Du willst Zeilen zu Spalten machen, hab ich mal hier erklärt: https://www.pg-forum.de/viewtopic.php?t=4067

Ob man das mit MySQL nachbauen kann, ich weiß nicht ...

 

[19RooC90]

Genau, möchte die Zeilen zu Spalten machen. Das funktioniert soweit auch.
Ich habe in der Konten Tabelle zb. 3 Einträge.

select `hauptselect`.`Datum` AS `datum`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes`
where ((`verwaltung`.`erspartes`.`Konto_ID` = 1)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `A`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes`
where ((`verwaltung`.`erspartes`.`Konto_ID` = 2)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `B`,
ifnull((
select `verwaltung`.`erspartes`.`Wert`
from `verwaltung`.`erspartes` where ((`verwaltung`.`erspartes`.`Konto_ID` = 3)
and (`verwaltung`.`erspartes`.`Datum` = `hauptselect`.`Datum`))),0) AS `C`
from `verwaltung`.`erspartes` `hauptselect`
group by `hauptselect`.`Datum`

Mit diesem Code erzeuge ich mir die Aliase A,B,C für diese Konten, wenn nun ein 4. Konto hinzukommt muss ich das gesamte Select erweitern wo ich nach der Konto_ID = 4 abfrage.

Gibt es da eine Möglichkeit das zu umgehen? Ich möchte mir davon eine View erstellen, da aber Konten jederzeit hinzukommen können muss jedes mal die View ergänzt werden. Das ist mein Kernproblem.

 

[akretschmer]

Gibt es da eine Möglichkeit das zu umgehen? Ich möchte mir davon eine View erstellen, da aber Konten jederzeit hinzukommen können muss jedes mal die View ergänzt werden. Das ist mein Kernproblem.

Ja. In PostgreSQL gibt es ein Contrib-Modul, was das macht. Ansonsten mußt halt eine Funktion schreiben, die eine Tabelle zurückliefert und zur Laufzeit das nötige SQL selber erstellt, also dynamisches SQL, via EXECUTE ausführen.

 

[19RooC90]

Danke dir, also deinem Post entnehme ich das es ohne weiteres (Funktion) nicht möglich ist was ich vorhabe mit einem normalen Statement.

 

[akretschmer]

Danke dir, also deinem Post entnehme ich das es ohne weiteres (Funktion) nicht möglich ist was ich vorhabe mit einem normalen Statement.

Ich weiß nicht, ob es Datenbanken gibt, die eine Pivot-Funktion on Board haben. Aber ohne ist es halt zwar machbar, aber eben nicht trivial und wenn es auch noch dynamisch sein soll dann steigt der Aufwand nochmals. MySQL ist ja eh a bissl behindert, da sollte man schon eher richtige Systeme nehmen. Wie gesagt, in PG ist es machbar.

 

[BerndB]

Hi,

hier mal eine kurze form für eine Pivot Tabelle

SELECT h.*,
sum( IF ( e.konto = 1 , e.wert , NULL))  AS Konto_01,
sum( IF ( e.konto = 2 , e.wert , NULL))  AS Konto_02,
sum( IF ( e.konto = 3 , e.wert , NULL))  AS Konto_03,
sum( IF ( e.konto = 4 , e.wert , NULL))  AS Konto_04,
sum( IF ( e.konto = 5 , e.wert , NULL))  AS Konto_05
FROM haupt h
LEFT JOIN erspartes e ON h.datum = e.datum
GROUP BY h.datum;

+------------+-----------+----------------+----------+----------+----------+----------+----------+
| datum  | feld1  | feld2  | Konto_01 | Konto_02 | Konto_03 | Konto_04 | Konto_05 |
+------------+-----------+----------------+----------+----------+----------+----------+----------+
| 2015-01-01 | text  | NULL  |  100 |  102 |  NULL |  444 |  NULL |
| 2015-01-02 | nehr text | NULL  |  33 |  45 |  321 |  NULL |  NULL |
| 2015-01-04 | NULL  | auch text  |  NULL |  NULL |  NULL |  NULL |  NULL |
| 2015-01-05 | NULL  | noch mehr text |  NULL |  NULL |  NULL |  NULL |  NULL |
+------------+-----------+----------------+----------+----------+----------+----------+----------+
4 rows in set (0,00 sec)

mysql> select * from haupt;
+------------+-----------+----------------+
| datum      | feld1     | feld2          |
+------------+-----------+----------------+
| 2015-01-01 | text      | NULL           |
| 2015-01-02 | nehr text | NULL           |
| 2015-01-04 | NULL      | auch text      |
| 2015-01-05 | NULL      | noch mehr text |
+------------+-----------+----------------+
4 rows in set (0,00 sec)

mysql> select * from erspartes;
+------------+-------+------+
| datum      | konto | wert |
+------------+-------+------+
| 2015-01-01 |     1 |  100 |
| 2015-01-01 |     2 |  102 |
| 2015-01-01 |     4 |  444 |
| 2015-01-02 |     1 |   33 |
| 2015-01-02 |     2 |   45 |
| 2015-01-02 |     3 |  321 |
+------------+-------+------+
6 rows in set (0,00 sec)

CREATE TABLE `haupt` (
  `datum` date NOT NULL DEFAULT '0000-00-00',
  `feld1` varchar(30) DEFAULT NULL,
  `feld2` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`datum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `erspartes` (
  `datum` date NOT NULL DEFAULT '0000-00-00',
  `konto` int(11) NOT NULL DEFAULT '0',
  `wert` float DEFAULT NULL,
  PRIMARY KEY (`datum`,`konto`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Gruss

Bernd