MySQL MIN函数

Summary: in this tutorial, you will learn how to use the MySQL MIN function to find the minimum value in a set of values.

Introduction to MySQL MIN function

The MIN function returns the minimum value in a set of values. The MIN function is very useful in some scenarios such as finding the smallest number, selecting the least expensive product, getting the lowest credit limit, etc.

The following illustrates the syntax of the MIN function:

MIN(DISTINCT expression)

If you specify the DISTINCT operator, the MIN function returns the minimum value of distinct values, which is the same as omitting the DISTINCT. In other words, DISTINCT operator does not have any effect to the MIN function. It is just for ISO compatibility.

Notice that the DISTINCT operator takes effect in other aggregate functions such as SUM, AVG and COUNT.

MySQL MIN function examples

Let’s take a look at the products table in the sample database.

To get the cheapest product in the products table, which is the product that has the lowest buy price, you use the following query:

SELECT MIN(buyPrice) FROM products;

MySQL MIN with subquery

To select not only the price but also other product’s information such as product code and product name, you use the MIN function in a subquery as follows:

SELECT productCode,        productName,        buyPrice FROM products WHERE  buyPrice = (                 SELECT MIN(buyPrice)                 FROM products);

How it works.

• The subquery returns the lowest buy price product in the products table.

• The outer query selects the product whose buy price is equal to the lowest price returned from the subquery.

MySQL MIN with GROUP BY

When you combine the MIN function with the GROUP BY clause in a SELECT statement, you can get the minimum values for every group.

For example, to get the lowest buy price product for each product line, you use the following statement:

SELECT productline, MIN(buyprice) FROM products GROUP BY productline;

If you want to select not only the product line but also other columns in the products table such as product code and product name, you need to use a correlated subquery.

MySQL MIN with correlated subquery

The following query selects the lowest price product in every product line by combining the MIN function with a correlated subquery:

SELECT productline,        productCode,        productName,        buyprice FROM products a WHERE buyprice = (                 SELECT MIN(buyprice)                 FROM products b                 WHERE b.productline = a.productline)

For each product line from the outer query, the correlated subquery selects the lowest price product in the product line and returns the lowest price. The returned lowest price is then used as the input for the outer query to select the related product data including product line, product code, product name and buy price.

If you want to achieve the same result without using the MIN function and a subquery, you can use a self join with a LEFT JOIN clause as the following query:

SELECT a.productline,        a.productCode,        a.productName,        a.buyprice FROM products a LEFT JOIN products b   ON a.productline = b.productline   AND b.buyprice < a.buyprice WHERE b.productcode IS NULL;

In this tutorial, you have learned how to use the MySQL MIN function to find the minimum value in a set of values.

原文链接：http://outofmemory.cn/mysql/function/mysql-min