如何比较SQL查询结果？如何从查询结果中获取[serving-num]的最小值？

1. How to Compare Results from SQL Queries? #

It depends on what kind of comparison you need—either comparing rows within the same result set, or comparing two separate result sets:

• Comparing rows within a single result set
  Use window functions like LAG() or LEAD() to compare a row with its previous/next row in the set. For example, to check if an order total increased from the prior row:

  SELECT
    order_id,
    order_total,
    LAG(order_total) OVER (ORDER BY order_date) AS previous_order_total,
    CASE
      WHEN order_total > LAG(order_total) OVER (ORDER BY order_date) THEN 'Total increased'
      WHEN order_total < LAG(order_total) OVER (ORDER BY order_date) THEN 'Total decreased'
      ELSE 'Total stayed the same'
    END AS comparison
  FROM orders;
  

• Comparing two separate result sets
  You can use operators like EXCEPT (find rows in the first set not in the second), INTERSECT (find common rows), or join the sets to compare specific columns:

  -- Find US customers who didn't sign up in 2023
  SELECT * FROM (SELECT id, name FROM customers WHERE country = 'USA') AS usa_customers
  EXCEPT
  SELECT * FROM (SELECT id, name FROM customers WHERE signup_date > '2023-01-01') AS recent_customers;
  
  -- Compare test score improvements between two tests
  SELECT a.id, a.score AS old_score, b.score AS new_score
  FROM (SELECT id, score FROM test_scores WHERE test_id = 1) AS a
  JOIN (SELECT id, score FROM test_scores WHERE test_id = 2) AS b ON a.id = b.id
  WHERE b.score > a.score;
  

2. How to Get the Minimum Value from Query Results? #

Use the MIN() aggregate function—you can apply it directly to a column in your query, or wrap a subquery to get the min from a filtered result set:

• Min from a direct query

  SELECT MIN(price) AS lowest_book_price
  FROM products
  WHERE category = 'Books';
  

• Min from a pre-filtered result set
  If you already have a query that returns the data you care about, wrap it in a subquery and apply MIN():

  SELECT MIN(order_total) AS smallest_yearly_order
  FROM (
    SELECT order_total FROM orders
    WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
  ) AS yearly_orders;
  

• Grouped minimum values
  To get the min per group (e.g., lowest price per product category), pair MIN() with GROUP BY:

  SELECT category, MIN(price) AS lowest_price_in_category
  FROM products
  GROUP BY category;
  

3. Can I Get the Minimum Value of the [serving-num] Field from Query Results? #

Absolutely! As long as [serving-num] is a numeric data type (integer, decimal, etc.), you can use the same MIN() approach as above. Note that since the field name contains a hyphen, you'll need to wrap it in brackets (for SQL Server) or backticks (for MySQL) to avoid syntax errors:

• Example with a direct table query:

  -- SQL Server syntax
  SELECT MIN([serving-num]) AS minimum_serving_number
  FROM menu_items
  WHERE cuisine = 'Italian';
  
  -- MySQL syntax
  SELECT MIN(`serving-num`) AS minimum_serving_number
  FROM menu_items
  WHERE cuisine = 'Italian';
  

• Example with a subquery result:

  SELECT MIN([serving-num]) AS min_servings
  FROM (
    SELECT [serving-num], item_name FROM menu_items WHERE calories < 500
  ) AS low_calorie_items;
  

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