亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

為了賬號安全,請及時綁定郵箱和手機立即綁定

MySQL MIN函數

標簽:
MySQL

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 SUMAVG and COUNT.

MySQL MIN function examples

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

products table

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 buyprice

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);

MySQL MIN cheapest product

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

點擊查看更多內容
TA 點贊

若覺得本文不錯,就分享一下吧!

評論

作者其他優質文章

正在加載中
  • 推薦
  • 評論
  • 收藏
  • 共同學習,寫下你的評論
感謝您的支持,我會繼續努力的~
掃碼打賞,你說多少就多少
贊賞金額會直接到老師賬戶
支付方式
打開微信掃一掃,即可進行掃碼打賞哦
今天注冊有機會得

100積分直接送

付費專欄免費學

大額優惠券免費領

立即參與 放棄機會
微信客服

購課補貼
聯系客服咨詢優惠詳情

幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

公眾號

掃描二維碼
關注慕課網微信公眾號

舉報

0/150
提交
取消