如何在SQL中使用GREATEST()与LEAST()函数？Oracle函数逻辑复现咨询

Absolutely! Let's walk through how to replicate your Oracle logic in standard SQL, plus break down how GREATEST() and LEAST() work across most databases.

Replicating Your Oracle Query #

First, the good news: most modern SQL databases (PostgreSQL, MySQL, SQL Server 2022+, etc.) natively support the GREATEST() function with the exact same syntax as Oracle. So your query can be reused directly:

SELECT GREATEST(date1, date2, date3) FROM myDate;

If you're working with an older SQL dialect that doesn't support GREATEST() (like pre-2022 SQL Server), you can replicate the logic using a CASE statement:

SELECT 
  CASE 
    WHEN date1 >= date2 AND date1 >= date3 THEN date1
    WHEN date2 >= date1 AND date2 >= date3 THEN date2
    ELSE date3
  END AS max_date
FROM myDate;

This manually compares each date to find the largest one, mimicking what GREATEST() does under the hood.

Using GREATEST() and LEAST() Functions #

These functions are incredibly useful for finding min/max values across multiple columns (not just a single column's rows). Here's a breakdown of their behavior:

GREATEST() #

• Purpose: Returns the largest value from a list of input expressions (numbers, strings, dates, etc.)
• Key Rules:
  • All inputs must be of the same data type (or implicitly convertible to the same type) — mixing incompatible types (like a string and a date) will throw an error.
  • If any input is NULL, the function returns NULL (most databases follow this rule, including Oracle, PostgreSQL, MySQL).
  • You can pass 2 or more arguments — no upper limit (within reason!).
• Examples:

  -- Numeric values: returns 25
  SELECT GREATEST(10, 25, 5, 18);
  
  -- Strings (lexicographical order): returns "orange"
  SELECT GREATEST('apple', 'banana', 'orange');
  
  -- Dates: returns the most recent date
  SELECT GREATEST('2023-01-01', '2023-06-15', '2022-12-31');
  

LEAST() #

• Purpose: The inverse of GREATEST() — returns the smallest value from a list of input expressions.
• Key Rules: Same as GREATEST() (type consistency, NULL handling, multiple arguments allowed).
• Examples:

  -- Numeric values: returns 5
  SELECT LEAST(10, 25, 5, 18);
  
  -- Strings: returns "apple"
  SELECT LEAST('apple', 'banana', 'orange');
  
  -- Dates: returns the oldest date
  SELECT LEAST('2023-01-01', '2023-06-15', '2022-12-31');
  

内容的提问来源于stack exchange，提问作者user3024635