Basierend auf dem, was ich von Freunden und anderen Online-Benutzern gehört habe, und aus meiner eigenen persönlichen Erfahrung ist die häufigste Frage für ein Vorstellungsgespräch für Datenbankprogrammierer "Wie finden Sie das höchste Gehalt in einem Mitarbeitertisch?"
Diese Frage testet das Wissen eines Kandidaten über Ranglistenfunktionen, Unterabfragen, Common Table Expression (CTE) und grundlegende SQL.
Wir werden die Antwort anhand einer Reihe von Szenarien und Fragen untersuchen, die in Bezug auf die Ermittlung des höchsten, niedrigsten und n-ten Gehalts gestellt werden.
Erstellen Sie eine Mitarbeiter-Tabelle und füllen Sie sie mit einigen Testdaten.
WENN OBJECT_ID (N'Employee ', N'U') NICHT NULL DROP TABLE-Mitarbeiter ist; CREATE TABLE-Mitarbeiter (EmployeeId INT PRIMARY KEY, Gehaltsnummer (18,2)); Einfügen in Mitarbeiterwerte (101,20000,00); Einfügen in Mitarbeiterwerte (102,25000,00); Einfügen in Mitarbeiterwerte (103.30000.00); Einfügen in Mitarbeiterwerte (104,35000,00); Einfügen in Mitarbeiterwerte (105,35000,00); Einfügen in Mitarbeiterwerte (106,45000,00); SELECT * FROM Mitarbeiter
Abfrage 1.1 Finden Sie den Mitarbeiter mit dem höchsten Gehalt
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, ROW_NUMBER () OVER (Sortieren nach Gehaltsabrechnung) als Gehaltsbestellung vom Mitarbeiter aus) DT WHERE DT. Salary_Order = 1;
Die obige Abfrage verwendet das abgeleitete Tabellenkonzept, bei dem die Unterabfrage mit der Rangfolgenfunktion row_number einem Gehalt eine eindeutige fortlaufende Nummer (1,2,3… bis N) zuweist, die in absteigender Reihenfolge (höchste bis niedrigste) geordnet ist. So wird 1 dem höchsten Gehalt zugewiesen, 2 dem zweithöchsten Gehalt usw. und verwendet die abgeleitete Tabelle, um die Zeile mit der Zeilennummer als 1 abzurufen.
Abfrage 1.2 Finden Sie den Mitarbeiter mit dem höchsten Gehalt, wenn es eine Unentschieden gibt (zwei Mitarbeiter haben das höchste Gehalt und die Anzahl ist gleich)
Ich füge einen weiteren Mitarbeiter ein, dessen Gehalt dem höchsten mit Query 1.1 abgerufenen Gehalt entspricht, um dieses Beispiel zu demonstrieren.
Einfügen in Mitarbeiterwerte (107,45000,00); SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, DENSE_RANK () OVER (Sortieren nach Gehaltsabrechnung) als Gehaltsauftrag vom Mitarbeiter aus) DT WHERE DT. Salary_Order = 1;
Hier löschen wir die Mitarbeiter-ID 107, die wir für die Query 1.2-Demonstration eingefügt haben.
Vom Mitarbeiter löschen, wobei EmployeeId = 107;
In der obigen Abfrage weist die Dense_rank-Funktion bei einem Gleichstand die gleiche fortlaufende Rangnummer zu. Daher weist es den beiden höchsten Löhnen (45.000) die Nummer 1 zu, und beide werden mithilfe der abgeleiteten Tabellenabfrage mit dem Filter salary_order = 1 zurückgegeben.
Abfrage 1.3 Mitarbeiter mit dem zweithöchsten Gehalt finden
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, ROW_NUMBER () OVER (Sortieren nach Gehaltsabrechnung) als Gehaltsbestellung vom Mitarbeiter aus) DT WHERE DT. Salary_Order = 2;
Hier verwenden wir dieselbe Logik, die in Query 1.1 mit der Funktion ROW_NUMBER () verwendet wurde, aber wir verwenden Salary_order = 2, um das zweite höchste Gehalt abzurufen.
Abfrage 1.4 Mitarbeiter mit dem n-ten höchsten Gehalt finden
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, ROW_NUMBER () OVER (Sortieren nach Gehaltsabrechnung) als Gehaltsbestellung vom Mitarbeiter aus) DT WHERE DT. Salary_Order = 4;
Hier verwenden wir dieselbe Logik, die in Query 1.1 und Query 1.3 verwendet wird. N-te bedeutet, dass Sie eine beliebige Nummer angeben können. Die Abfrage ruft das Gehalt bei der N-ten Nummer ab.
Abfrage 1.5 Mitarbeiter mit dem niedrigsten Gehalt finden
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, ROW_NUMBER () OVER (Sortieren nach Gehaltserhöhung) als Salary_Order von Employee aus) DT WHERE DT. Salary_Order = 1;
Um das niedrigste Gehalt zu ermitteln, verwenden wir Auftrag nach Gehalt in aufsteigender Reihenfolge. Das Ergebnis wird also in aufsteigender Reihenfolge nach Gehalt sortiert (niedrigster bis höchster). Daher wird das niedrigste Gehalt row_number = 1 usw. erhalten. Wir verwenden den Filter Salary_Order = 1, um das erste niedrigste Gehalt in der Employee-Tabelle abzurufen.
Abfrage 1.6 Finden des Angestellten mit dem niedrigsten Gehalt bei Unentschieden (zwei Angestellte haben das niedrigste Gehalt und sind gleich)
Ich füge einen weiteren Angestellten ein, dessen Gehalt dem niedrigsten Gehalt entspricht, der mithilfe der obigen Abfrage abgerufen wurde, um dieses Beispiel zu veranschaulichen.
Einfügen in Mitarbeiterwerte (109.200.000,00);
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, DENSE_RANK () OVER (Sortieren nach Gehaltserhöhung) als Salary_Order von Employee aus) DT WHERE DT. Salary_Order = 1;
Hier löschen wir den Mitarbeiter mit 108 ID, der eingefügt wurde, um die obige Abfrage zu veranschaulichen.
von Mitarbeiter löschen, wobei Mitarbeiter-ID = 109;
Um das niedrigste Gehalt mit Bindungen zu finden, verwenden wir die Funktion "dense_rank", die dieselbe ist wie die Abfrage 1.2. Die Funktion "dense_rank" weist fortlaufende Nummern zu, wenn es ein doppeltes Gehalt gibt. Für das niedrigste Gehalt (20000.00) wird beiden Gehältern die Nummer 1 zugewiesen. Mit dem Filter Salary_Order = 1 können wir das niedrigste Gehalt bei Gleichstand mit der Funktion "dense_rank" abrufen.
Abfrage 1.7 Mitarbeiter mit dem zweitniedrigsten Gehalt finden
SELECT EmployeeId, Salary FROM (Wählen Sie EmployeeId, Salary, ROW_NUMBER () OVER (Sortieren nach Gehaltserhöhung) als Salary_Order von Employee aus) DT WHERE DT. Salary_Order = 2;
Hier verwenden wir dieselbe Logik, die in Query 1.3 mit der ROW_NUMBER () - Funktion verwendet wurde, aber wir verwenden Salary_order = 2, um das zweitniedrigste Gehalt abzurufen.