Persediaan Temu Duga SQL · Pelajaran

Gaji Kedua Tertinggi, Lima Cara

Perbandingan penyelesaian menggunakan subpertanyaan, LIMIT/OFFSET dan fungsi tetingkap

Pelajaran 1 daripada 413 langkah

Gaji Kedua Tertinggi, Lima Cara ialah pelajaran Persediaan Temu Duga SQL percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga SQL, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Soalan yang Ditanya kepada Semua Calon

"Cari gaji kedua tertinggi" ialah soalan temu duga SQL yang paling kerap ditanya. Penemuduga menyukainya kerana terdapat banyak jawapan yang betul serta beberapa perangkap halus.

Andaikan terdapat jadual employee dengan lajur id dan salary. Tugas anda ialah mengembalikan nilai gaji berbeza yang kedua tertinggi.

Jika gaji ialah 300, 200, 200, 100, jawapannya ialah 200, bukan baris kedua.
Jika tiada gaji berbeza yang kedua, jawapan yang biasanya dijangka ialah NULL.

Dalam bahagian seterusnya, kita akan menyelesaikannya dengan lima cara berbeza dan membincangkan kelebihan setiap cara.

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Cara 1: MAX bagi Nilai yang Lebih Rendah daripada MAX

Penyelesaian yang paling intuitif: gaji kedua tertinggi ialah gaji terbesar yang lebih kecil daripada nilai maksimum keseluruhan.

Penyelesaian ini hampir seperti ayat bahasa biasa dan berfungsi dalam setiap dialek SQL. Subpertanyaan dalaman mencari nilai tertinggi, manakala MAX luaran mencari nilai terbesar yang lebih rendah daripadanya.

Tambahan: jika tiada gaji berbeza yang kedua, MAX luaran mengagregatkan sifar baris dan mengembalikan NULL secara automatik. NULL yang diperoleh secara percuma itu tepat seperti yang dikehendaki oleh penemuduga.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Mengapa subpertanyaan menangani pendua

Perhatikan bahawa kita tidak pernah menggunakan DISTINCT dalam Cara 1, namun pendua masih dikendalikan dengan betul.

Jika tiga orang memperoleh 200 dan penerima gaji tertinggi memperoleh 300, pertanyaan dalaman mengembalikan 300. Penapis luar mengekalkan setiap baris yang kurang daripada 300, dan MAX bagi baris tersebut ialah 200 tanpa mengira jumlah nilai 200 yang wujud.

Inilah pemahaman penting: fungsi agregat mengecilkan pendua untuk Anda. Ramai calon mereka bentuk penyelesaian secara berlebihan dengan DISTINCT, sedangkan fungsi agregat sudah melakukan perkara yang betul.

Cara 2: LIMIT dengan OFFSET

Dalam MySQL dan PostgreSQL, Anda boleh mengisih gaji berbeza mengikut turutan menurun dan melangkau nilai pertama.

OFFSET 1 melangkau nilai tertinggi.
LIMIT 1 mengekalkan nilai seterusnya sahaja.

DISTINCT amat penting di sini. Jika tidak, pendua gaji tertinggi akan menyebabkan OFFSET 1 jatuh pada ulangan maksimum dan bukannya nilai kedua tertinggi yang sebenar.

Perangkap: jika tiada nilai berbeza kedua, ini mengembalikan sifar baris, bukannya NULL. Kita akan membaiki kes pinggir ini dalam pelajaran 4.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Cara 3: FETCH untuk SQL Server dan Oracle

SQL Server dan Oracle moden tidak menyokong LIMIT ... OFFSET. Sebaliknya, kedua-duanya menggunakan sintaks piawai ANSI OFFSET ... FETCH.

Logiknya sama seperti Cara 2: susun gaji berbeza dalam turutan menurun, langkau satu baris, kemudian ambil satu baris. Mengetahui bentuk sintaks merentas dialek ini menunjukkan pengalaman dunia sebenar kepada penemuduga.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Cara 4: Fungsi tetingkap DENSE_RANK

Pendekatan moden dan boleh diskala menggunakan fungsi tetingkap. DENSE_RANK memberikan kedudukan 1 kepada gaji tertinggi, kedudukan 2 kepada gaji berbeza seterusnya, dan memberikan kedudukan yang sama kepada gaji yang seri tanpa jurang.

Kita mengira kedudukan dalam subpertanyaan, kemudian menapis kedudukan 2 dalam pertanyaan luar. Ingat: Anda tidak boleh menapis fungsi tetingkap secara terus dalam WHERE, jadi pembungkus subpertanyaan adalah wajib.

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Mengapa DENSE_RANK, bukan RANK atau ROW_NUMBER

Pemilihan fungsi kedudukan penting untuk semantik "berbeza":

ROW_NUMBER memberikan nombor unik kepada setiap baris. Jadi, jika dua orang memperoleh 300, barisnya akan bernombor 1 dan 2, lalu kedudukan 2 menjadi ulangan gaji tertinggi. Salah.
RANK meninggalkan jurang selepas nilai yang seri: dua nilai 300 mendapat kedudukan 1, kemudian gaji seterusnya terus melompat ke kedudukan 3. Anda akan terlepas nilai itu pada kedudukan 2. Salah.
DENSE_RANK memberikan kedudukan yang sama kepada nilai yang seri tanpa jurang, jadi kedudukan 2 sentiasa merupakan gaji berbeza kedua. Betul.

Cara 5: Kiraan subpertanyaan berkorelasi

Helah klasik sebelum kewujudan fungsi tetingkap: sesuatu gaji ialah gaji tertinggi ke-N jika terdapat tepat N-1 gaji berbeza yang lebih tinggi daripadanya.

Untuk gaji kedua tertinggi, kita mahukan tepat satu gaji berbeza yang lebih tinggi. Pendekatan ini elegan tetapi boleh menjadi perlahan pada jadual besar kerana kiraan dalaman dijalankan untuk setiap baris luar.

Pendekatan ini juga mudah digeneralisasikan kepada gaji tertinggi ke-N dengan menukar kiraan kepada N - 1, sebab itulah penemuduga suka melihatnya.

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

Contoh lengkap dari awal hingga akhir

Ambil gaji berikut: 500, 500, 350, 350, 100.

Cara 1: MAX ialah 500, dan nilai terbesar di bawah 500 ialah 350. Jawapannya 350.
Cara 4 (DENSE_RANK): 500 -> kedudukan 1, 350 -> kedudukan 2, 100 -> kedudukan 3. Kedudukan 2 ialah 350.
Cara 5: bagi gaji 350, terdapat tepat satu gaji berbeza (500) yang lebih tinggi. Sepadan. Jawapannya 350.

Kelima-lima kaedah memberikan jawapan yang sama: gaji berbeza kedua tertinggi ialah 350, walaupun terdapat pendua.

Yang manakah patut Anda pilih

Panduan temu duga:

Nyatakan soalan dahulu: "Adakah Anda mahukan gaji berbeza, dan NULL jika tiada yang wujud?" Penjelasan ini memberikan mata tambahan.
DENSE_RANK ialah jawapan lalai yang paling kukuh; pendekatan ini boleh digeneralisasikan dengan kemas kepada gaji tertinggi ke-N dan setiap kumpulan.
MAX di bawah MAX ialah satu baris terbaik dan mengembalikan NULL secara automatik.
LIMIT/OFFSET adalah ringkas tetapi khusus dialek dan mengembalikan tiada baris untuk kes pinggir.

Menyebut pertukaran kelebihan dan kekurangan dengan jelas ialah perkara yang membezakan jawapan peringkat pertengahan daripada jawapan pemula.

Kesilapan lazim yang perlu dielakkan

Perhatikan perangkap yang sengaja diletakkan oleh penemuduga:

Menggunakan ROW_NUMBER dan bukannya DENSE_RANK, lalu mendapat gaji tertinggi sebanyak dua kali.
Terlupa menggunakan DISTINCT dalam versi LIMIT/OFFSET apabila terdapat pendua nilai maksimum.
Menganggap ORDER BY salary DESC LIMIT 1,1 mengembalikan nilai berbeza; sebenarnya tidak.
Mengembalikan baris kedua dan bukannya nilai kedua.

Semakan Pantas

Uji pemahaman Anda tentang pemilihan fungsi kedudukan.

Jawab dalam pelajaran interaktif

Ringkasan

Sekarang Anda mempunyai lima cara untuk mencari gaji kedua tertinggi:

MAX di bawah MAX - serasi merentas sistem dan mengembalikan NULL secara automatik.
LIMIT/OFFSET dan OFFSET/FETCH - ringkas tetapi khusus dialek.
DENSE_RANK - pilihan lalai yang boleh diskala dan mengendalikan nilai seri dengan betul.
Kiraan berkorelasi - elegan dan boleh digeneralisasikan kepada gaji tertinggi ke-N.

Kesimpulan penting: tanyakan sama ada Anda memerlukan nilai berbeza, utamakan DENSE_RANK untuk nilai seri, dan ingat kaedah yang mengembalikan NULL berbanding tiada baris apabila nilai kedua tidak wujud.

Percuma untuk bermula

Pelajari SQL dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 30
Pelajaran: 120

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Gaji Kedua Tertinggi, Lima Cara” percuma?

Ya — teks penuh “Gaji Kedua Tertinggi, Lima Cara” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga SQL, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga SQL merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Gaji Kedua Tertinggi, Lima Cara”?

Perbandingan penyelesaian menggunakan subpertanyaan, LIMIT/OFFSET dan fungsi tetingkap Anda berlatih Persediaan Temu Duga SQL menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga SQL?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga SQL di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Gaji Kedua Tertinggi, Lima Cara” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga SQL ini?

Ya. Setiap pelajaran Persediaan Temu Duga SQL menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Gaji Kedua Tertinggi, Lima Cara
Nilai Tertinggi Ke-n Dengan DENSE_RANK
Penerima Pendapatan Tertinggi Setiap Jabatan
Mengembalikan NULL apabila Nilai Ke-n Tiada

← Kembali ke Persediaan Temu Duga SQL