Gerne stelle ich meine erste Frage zu StackOverflow.
Ich habe zwei Tabellen (Mitarbeiter und Gehälter) in meiner Datenbank (Mitarbeiter).

Die Mitarbeitertabelle sieht folgendermaßen aus:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

Die Gehaltsliste sieht folgendermaßen aus:

+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| emp_no    | int  | NO   | PRI | NULL    |       |
| salary    | int  | NO   |     | NULL    |       |
| from_date | date | NO   | PRI | NULL    |       |
| to_date   | date | NO   |     | NULL    |       |
+-----------+------+------+-----+---------+-------+

Ich möchte Werte für ein bestimmtes Datumsintervall zurückgeben. Zum Beispiel möchte ich wissen, welches Gehalt ein Mitarbeiter für ein Intervall in einer dynamischen Ansicht hatte, für die Jahre 1990-1991 für die Jahre 1991-1992 usw.

Ich habe folgende Abfrage geschrieben:

SELECT 
    e.emp_no,
    e.first_name,
    e.last_name,
    s.salary AS Salary_for_1990
FROM
    employees e
        JOIN
    salaries s ON e.emp_no = s.emp_no
WHERE
    from_date BETWEEN '1990-01-01' AND '1990-12-31'
GROUP BY e.emp_no
ORDER BY e.emp_no ASC
LIMIT 10;

Ich habe folgendes Ergebnis erhalten:

+--------+------------+-----------+-----------------+
| emp_no | first_name | last_name | Salary_for_1990 |
+--------+------------+-----------+-----------------+
|  10001 | Georgi     | Facello   |           66961 |
|  10004 | Christian  | Koblick   |           48271 |
|  10005 | Kyoichi    | Maliniak  |           82621 |
|  10006 | Anneke     | Preusig   |           40000 |
|  10007 | Tzvetan    | Zielinski |           60740 |
|  10009 | Sumant     | Peace     |           70889 |
|  10011 | Mary       | Sluis     |           42365 |
|  10013 | Eberhardt  | Terkki    |           46305 |
|  10018 | Kazuhide   | Peha      |           61648 |
|  10021 | Ramzi      | Erde      |           59700 |
+--------+------------+-----------+-----------------+ 

Es funktioniert also für einen einzelnen Wert '1990-1991' Jahre. Ich suche jedoch nach einer Möglichkeit, in der Abfrage zusätzliche Spalte für '1991-1992', '1992-1993' Jahre usw. hinzuzufügen. um das dynamische Wachstum der Gehälter der Mitarbeiter sehen zu können.
Ich habe über Variablen nachgedacht, aber ich habe keine Ahnung, wie ich sie im Kontext abfragen soll.

Freuen Sie sich auf Ihre Antworten.
Vielen Dank im Voraus.

1
John Galt 19 Feb. 2020 im 12:25

3 Antworten

Beste Antwort

Menschen haben ein Gehalt ab einem Datum nicht ab einer Reihe von Daten.

Für Ihr Problem können Sie die bedingte Aggregation verwenden:

SELECT e.emp_no, e.first_name, e.last_name,
       MAX(CASE WHEN from_date <= '1990-01-01' AND to_date > '1990-01-01' THEN salary END) as salary_19900101,
       MAX(CASE WHEN from_date <= '1991-01-01' AND to_date > '1991-01-01' THEN salary END) as salary_19910101,
       MAX(CASE WHEN from_date <= '1992-01-01' AND to_date > '1991-02-01' THEN salary END) as salary_19910101
FROM employees e JOIN
     salaries s 
     ON e.emp_no = s.emp_no
GROUP BY e.emp_no, e.first_name, e.last_name
ORDER BY e.emp_no ASC
LIMIT 10;

Anmerkungen:

  • Dies hat das Gehalt ab dem ersten Tag des Jahres. Sie können das Gehalt ab einem bestimmten Tag haben (z. B. De 31 oder 1. Juli).
  • Wenn ein Mitarbeiter für ein bestimmtes Jahr kein Gehalt hat, lautet der Wert NULL.
  • Die GROUP BY -Klausel enthält alle nicht aggregierten Spalten. Obwohl dies formal nicht erforderlich ist, wenn emp_no der Primärschlüssel ist, ist es eine gute Angewohnheit, wenn Sie SQL lernen.
0
Gordon Linoff 19 Feb. 2020 im 12:27

Wenn Sie dies "skalierbar" machen möchten, sollten Sie beispielsweise eine bedingte Aggregation und vorbereitete Anweisungen verwenden

drop table if exists t;
create table t(emp int,dt date,salary int);

insert into t values
(1,'2015-06-30',7),
(1,'2015-12-31',10),
(1,'2016-12-31',05),
(1,'2017-12-31',10),
(2,'2015-12-31',20),
(2,'2016-12-31',25),
(2,'2017-12-31',30);

set @sql =
        (select group_concat(concat('max(case when year(dt) = ',dt ,' then salary else 0 end) as ', 'yr',dt))
        from
        (
        select distinct year(dt) dt from t
        ) s
        )
;
set @sql = (select concat('Select emp,',@sql,' from t group by emp'));

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

+------+--------+--------+--------+
| emp  | yr2015 | yr2016 | yr2017 |
+------+--------+--------+--------+
|    1 |     10 |      5 |     10 |
|    2 |     20 |     25 |     30 |
+------+--------+--------+--------+
2 rows in set (0.00 sec)

Beachten Sie, dass dadurch das MAX-Gehalt in einem bestimmten Jahr zurückgegeben wird.

0
P.Salmon 19 Feb. 2020 im 11:27

Eine einfache Möglichkeit sind Unterabfragen, obwohl sie nicht sehr elegant sind und sich über viele Jahre nicht gut skalieren lassen:

SELECT 
e.emp_no,
e.first_name,
e.last_name,
(SELECT s.salary FROM salaries s WHERE s.emp_no = e.emp_no AND from_date BETWEEN '1990-01-01' AND '1990-12-31') Salary_for_1990,
(SELECT s.salary FROM salaries s WHERE s.emp_no = e.emp_no AND from_date BETWEEN '1991-01-01' AND '1991-12-31') Salary_for_1991
...
0
moritz.vieli 19 Feb. 2020 im 12:51