SQL Interview Prep · บทเรียน

ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n

กรณีขอบที่ผู้สัมภาษณ์ชื่นชอบ: จัดการอย่างเหมาะสมเมื่อมีแถวน้อยเกินไป

ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

กรณีพิเศษที่ผู้สัมภาษณ์ชอบถาม

หลังจากทำคำสั่งหาค่าที่สูงเป็นอันดับที่ N ได้แล้ว ผู้สัมภาษณ์อาจถามต่อว่า: "ถ้าตารางมีเงินเดือนแบบ DISTINCT น้อยกว่า N ค่า จะทำอย่างไร หากต้องการ NULL เพียงหนึ่งค่าแทนที่จะเป็นผลลัพธ์ว่างเปล่า"

คำถามนี้แยกผู้สมัครที่ท่องจำคำสั่งได้ออกจากผู้ที่เข้าใจพฤติกรรมของชุดผลลัพธ์ หลายวิธีคืนศูนย์แถวโดยไม่แจ้ง แทนที่จะคืนหนึ่งแถวที่มีค่า NULL

บทเรียนนี้มุ่งเน้นการบังคับให้มีแถวผลลัพธ์เพียงหนึ่งแถวเสมอ โดยให้ค่าเป็น NULL เมื่อไม่มีค่าลำดับที่ N

เหตุใด DENSE_RANK เพียงอย่างเดียวจึงไม่คืนแถว

ลองนึกถึงคำสั่งมาตรฐานสำหรับหาค่าที่สูงเป็นอันดับที่ N หากมีเงินเดือนที่แตกต่างกันเพียงสองค่า แต่ขออันดับที่ 3 WHERE rnk = 3 จะไม่ตรงกับสิ่งใด ดังนั้นคำสั่งจึงคืน ชุดผลลัพธ์ว่างเปล่า หรือศูนย์แถว

ชุดผลลัพธ์ว่างเปล่าไม่เหมือนกับแถวที่มีค่า NULL หากข้อกำหนดระบุว่าให้คืนค่า "NULL" ผลลัพธ์ว่างจะไม่ผ่านการทดสอบ แม้ตรรกะพื้นฐานจะถูกต้องก็ตาม

SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) t
WHERE rnk = 3;  -- returns NO rows if fewer than 3 distinct salaries

วิธีแก้ที่ 1: ครอบด้วย SELECT ด้านนอก

วิธีแก้ที่ง่ายและเชื่อถือได้ที่สุดคือครอบคำสั่งทั้งหมดสำหรับหาค่าที่สูงเป็นอันดับที่ N ไว้เป็นคำสั่งย่อยแบบสเกลาร์ภายใน SELECT เดียว คำสั่งย่อยแบบสเกลาร์ที่ไม่พบแถวจะประเมินค่าเป็น NULL และ SELECT ด้านนอกจะสร้างแถวออกมาเพียงหนึ่งแถวเสมอ

นี่คือคำตอบมาตรฐานสำหรับรูปแบบแบบ LeetCode ที่ให้คืนค่า "NULL" และใช้ได้กับระบบ SQL ทุกแบบ

SELECT (
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2   -- N = 3
) AS third_highest;

เหตุใดเทคนิคคำสั่งย่อยแบบสเกลาร์จึงใช้ได้

กฎสองข้อนี้ทำงานร่วมกันเพื่อให้ได้พฤติกรรมที่ต้องการ:

คำสั่งย่อยแบบสเกลาร์ต้องคืนค่าได้ไม่เกินหนึ่งค่า หากไม่คืนแถว ระบบจะใช้ NULL แทน
SELECT ด้านนอกที่ไม่มี FROM หรือใช้แหล่งข้อมูลที่มีแถวเดียว จะส่งออกมาหนึ่งแถวเสมอ

ดังนั้น เมื่อคำสั่งด้านในพบค่าลำดับที่ N คุณก็จะได้ค่านั้น แต่เมื่อไม่พบสิ่งใด คุณจะได้หนึ่งแถวที่มีค่า NULL ซึ่งตรงตามข้อกำหนดที่ผู้สัมภาษณ์ระบุ

วิธีแก้ที่ 1 ด้วยรูปแบบ DENSE_RANK

การครอบแบบเดียวกันนี้ใช้กับวิธีแก้ด้วยฟังก์ชันหน้าต่างได้เช่นกัน ให้วางคำสั่งที่จัดอันดับแล้วไว้ในคำสั่งย่อยแบบสเกลาร์ หากไม่มีแถวใดมีอันดับเป็น N คำสั่งย่อยจะให้ค่า NULL และ SELECT ด้านนอกก็ยังคืนหนึ่งแถว

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = 3
) AS third_highest;

วิธีแก้ที่ 2: MAX คืนค่า NULL ให้โดยอัตโนมัติ

ลองนึกถึงแนวคิด MAX ซ้อน MAX จากบทเรียนที่ 1 การใช้ฟังก์ชันรวมกับศูนย์แถวจะคืนค่า NULL และยังคงสร้างหนึ่งแถว สำหรับค่าที่สูงเป็นอันดับสอง วิธีนี้เป็นคำสั่งบรรทัดเดียวที่กระชับและตรงตามข้อกำหนดเรื่อง NULL อยู่แล้ว

ข้อจำกัดคือการซ้อน MAX ล้วน ๆ เพื่อขยายไปยังอันดับ N ที่กำหนดโดยพลการจะดูยุ่งยาก ดังนั้นจึงเหมาะที่สุดสำหรับกรณีอันดับสองโดยเฉพาะ

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

วิธีแก้ที่ 3: COALESCE พร้อมค่าทดแทน

หากสภาพแวดล้อมของคุณรับประกันว่าจะมีหนึ่งแถว แต่ค่าอาจหายไปด้วยสาเหตุอื่น คุณสามารถครอบผลลัพธ์ด้วย COALESCE เพื่อใส่ค่าเริ่มต้นที่ระบุชัดเจนได้

โปรดทราบว่า COALESCE ช่วยได้ก็ต่อเมื่อมีแถวอยู่แล้วเท่านั้น มันไม่เปลี่ยนชุดผลลัพธ์ว่างให้กลายเป็นหนึ่งแถว ดังนั้นให้ใช้ร่วมกับตัวครอบคำสั่งย่อยแบบสเกลาร์ ซึ่งรับประกันว่าจะมีแถว แล้วจึงใช้ COALESCE กับค่า หากต้องการค่าอื่นแทน NULL เช่น 0

SELECT COALESCE((
  SELECT DISTINCT salary
  FROM employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2
), 0) AS third_highest_or_zero;

สิ่งที่ NOT แก้ปัญหาไม่ได้

ระวังวิธีแก้ที่ดูเหมือนถูกต้องแต่ใช้ไม่ได้:

การเพิ่ม COALESCE ครอบคำสั่งที่คืนศูนย์แถวไม่ช่วยอะไร เพราะไม่มีแถวให้ COALESCE ทำงานด้วย
IFNULL / ISNULL มีข้อจำกัดเดียวกับ COALESCE
การเพิ่ม LIMIT 1 ไม่ได้สร้างแถวขึ้นมาเมื่อไม่มีแถวใดผ่านเงื่อนไข

ปัญหาจำนวนแถวต้องแก้ด้วยตัวครอบคำสั่งย่อยแบบสเกลาร์หรือฟังก์ชันรวม ไม่ใช่ใช้ฟังก์ชันแทนค่า NULL เพียงอย่างเดียว

ตัวอย่างที่ทำให้ดู: ขออันดับที่ 3 จากเงินเดือนเพียงสองค่า

เงินเดือนคือ 500, 500, 300 เงินเดือนแบบ DISTINCT มีเพียง 500 และ 300 ดังนั้นจึงไม่มีค่าที่สูงเป็นอันดับ 3

แบบ DENSE_RANK ธรรมดาพร้อม WHERE rnk = 3: คืนศูนย์แถว ไม่ผ่านข้อกำหนด
ตัวครอบคำสั่งย่อยแบบสเกลาร์: คำสั่งด้านในไม่พบสิ่งใด ดังนั้น SELECT ด้านนอกจึงคืนหนึ่งแถวที่มีค่า NULL ผ่านข้อกำหนด
COALESCE(..., 0): คืนหนึ่งแถวที่มีค่า 0 หากต้องการค่าเริ่มต้นที่เป็นตัวเลข

อธิบายแนวคิดระหว่างการสัมภาษณ์

เพิ่มคะแนนด้วยการอธิบายเหตุผลดังนี้:

"คำสั่งแบบตรงไปตรงมาคืนชุดผลลัพธ์ว่าง ไม่ใช่ NULL ดังนั้นผมจะครอบด้วยคำสั่งย่อยแบบสเกลาร์เพื่อรับประกันว่ามีหนึ่งแถว"
"คำสั่งย่อยแบบสเกลาร์ที่ไม่พบแถวตรงกันจะประเมินค่าเป็น NULL ซึ่งตรงตามข้อกำหนดพอดี"
"หากต้องการค่าเริ่มต้นอย่าง 0 แทน NULL ผมจะเพิ่ม COALESCE ครอบคำสั่งย่อย"

ประเด็นสำคัญของคำถามนี้คือการแสดงให้เห็นว่าคุณเข้าใจความแตกต่างระหว่างความหมายของจำนวนแถวกับความหมายของค่า

รวมทุกอย่างเข้าด้วยกัน

วิธีหาค่าที่สูงเป็นอันดับที่ N หรือ NULL ซึ่งรองรับพารามิเตอร์ได้อย่างมั่นคงคือ จัดอันดับเงินเดือนแบบ DISTINCT กรองอันดับ N ภายในคำสั่งย่อยแบบสเกลาร์ แล้วให้ SELECT ด้านนอกรับประกันว่าจะมีหนึ่งแถว

คำสั่งเดียวนี้รองรับข้อมูลซ้ำด้วย DENSE_RANK ใช้ได้กับ N ทุกค่า และคืนค่า NULL อย่างเหมาะสมเมื่อ N มากกว่าจำนวนเงินเดือนแบบ DISTINCT

SELECT (
  SELECT salary
  FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
  ) t
  WHERE rnk = :n
  LIMIT 1
) AS nth_highest;

ตรวจสอบด่วน

พิจารณาความแตกต่างระหว่างจำนวนแถวกับค่า NULL

สรุป

เมื่อ N มากกว่าจำนวนเงินเดือนแบบ DISTINCT ที่มีอยู่ คำสั่งจัดอันดับแบบธรรมดาจะคืน ชุดผลลัพธ์ว่างเปล่า ไม่ใช่ NULL

ครอบคำสั่งหาค่าที่สูงเป็นอันดับที่ N ด้วยคำสั่งย่อยแบบสเกลาร์ภายใน SELECT ด้านนอก เพื่อให้มีหนึ่งแถวเสมอ และให้ค่าเป็น NULL เมื่อไม่มีค่าที่ตรงกัน
รูปแบบ MAX ซ้อน MAX คืนค่า NULL ให้โดยอัตโนมัติสำหรับกรณีค่าที่สูงเป็นอันดับสอง
COALESCE จะแทนที่ค่าได้ก็ต่อเมื่อมีแถวอยู่แล้ว ไม่สามารถเปลี่ยนศูนย์แถวให้เป็นหนึ่งแถวได้

เมื่อผู้สัมภาษณ์ถามเรื่องการจัดการ NULL อย่างเหมาะสม ให้แยกความแตกต่างระหว่างจำนวนแถวกับค่าออกจากกันเสมอ

คำถามที่พบบ่อย

บทเรียน “ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n”

กรณีขอบที่ผู้สัมภาษณ์ชื่นชอบ: จัดการอย่างเหมาะสมเมื่อมีแถวน้อยเกินไป คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 4 จากทั้งหมด 4 บทเรียน

บทเรียน “ส่งคืน NULL เมื่อไม่มีค่าอันดับที่ n” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

← กลับไปที่ SQL Interview Prep