SQLite MIN()函数 MAX()函数 AVG()函数

sqlite min() 函数

SQLite MIN()函数用于获取表达式或给定列的最小值。

语法

SELECT MIN(aggregate_expression)  
FROM tables  
[WHERE conditions];

在GROUP BY子句中使用Min()函数时的语法：

SELECT expression1, expression2, ... expression_n  
MIN(aggregate_expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;

示例1：

假设有一个名为student的表，具有以下数据：

从student表中检索学生的最低费用(fees)：

SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT; 
-- 最小年龄
SELECT MIN(age) AS "Lowest age"  FROM STUDENT;

执行上面查询代码，得到以下结果 -

sqlite> SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT;
2000.0
sqlite>
sqlite> SELECT MIN(FEES) AS "Lowest Fees"  FROM STUDENT;
2000.0
sqlite>
sqlite> -- 最小年龄
sqlite> SELECT MIN(age) AS "Lowest age"  FROM STUDENT;
21
sqlite>

示例2：

使用具有的GROUP BY子句的MIN()函数：

从student表中检索NAME和MIN FEES，并按NAME的数据排序：

SELECT NAME, MIN(FEES) AS "Lowest Fees"

FROM STUDENT

WHERE ID <= 8

GROUP BY NAME;

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, MIN(FEES) AS "Lowest Fees"
...> FROM STUDENT
...> WHERE ID <= 8
...> GROUP BY NAME;
Avgsu|2000.0
Javasu|18000.0
Linsu|10000.0
Maxsu|20000.0
Minsu|5000.0
Sqlsu|25000.0
sqlite>

sqlite max()函数

SQLite MAX()函数用于获取表达式或给定列的最大值。

语法

SELECT MAX(aggregate_expression)

FROM tables

[WHERE conditions];

在GROUP BY子句中使用Max()函数时的语法：

SELECT expression1, expression2, ... expression_n

MAX(aggregate_expression)

FROM tables

[WHERE conditions]

GROUP BY expression1, expression2, ... expression_n;

示例1：

从student表中检索学生的最高费用(fees)：

SELECT MAX(FEES) AS "Highest Fees"  FROM STUDENT;
-- 最大年龄
SELECT MAX(age) AS "Highest age"  FROM STUDENT;

执行上面查询代码，得到以下结果 -

sqlite> SELECT MAX(FEES) AS "Highest Fees"  FROM STUDENT;
65000.0
sqlite>
sqlite> -- 最大年龄
sqlite> SELECT MAX(age) AS "Highest age"  FROM STUDENT;
27
sqlite>

示例2：

使用具有的GROUP BY子句的MAX()函数：

从student表中检索NAME,ADDRESS和MAX(FEES)，并按ADDRESS的数据排序：

SELECT NAME, ADDRESS, MAX(FEES) AS "Highest FEES"

FROM STUDENT

WHERE ID <= 8

GROUP BY ADDRESS;

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, ADDRESS, MAX(FEES) AS "Highest FEES"
...> FROM STUDENT
...> WHERE ID <= 8
...> GROUP BY ADDRESS;
Minsu|Beijing|15000.0
Linsu|Guangzhou|65000.0
Sqlsu|Haikou|25000.0
Avgsu|Shanghai|2000.0
Maxsu|Shengzhen|20000.0
sqlite>

sqlite avg() 函数

SQLite AVG()函数用于检索表达式或给定列的平均值。

语法

SELECT AVG(aggregate_expression)

FROM tables

[WHERE conditions];

在GROUP BY子句中使用AVG()函数时的语法：

SELECT expression1, expression2, ... expression_n

AVG(aggregate_expression)

FROM tables

[WHERE conditions]

GROUP BY expression1, expression2, ... expression_n;

示例1：

从student表中检索学生的平均费用(fees)：

SELECT AVG(FEES) AS "Avg Fees"

FROM STUDENT

WHERE ID > 0;

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(FEES) AS "Avg Fees"
...> FROM STUDENT
...> WHERE ID > 0;
18777.7777777778
sqlite>

示例2：

使用带有DISTINCT子句的AVG()函数，从student表中获取FEES大于10000并且平均不同费用。

SELECT AVG(DISTINCT  FEES) AS "Avg Fees"

FROM STUDENT

WHERE FEES > 10000;

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(DISTINCT  FEES) AS "Avg Fees"
...> FROM STUDENT
...> WHERE FEES > 10000;
28600.0
sqlite>

示例3：

使用数学公式在AVG()函数中计算求平均值。

可以使用数学公式根据您的要求检索平均值，如下求每个月的平均值 -

SELECT AVG(FEES / 12) AS "Average Monthly Fees"

FROM STUDENT;

执行上面查询代码，得到以下结果 -

sqlite> SELECT AVG(FEES / 12) AS "Average Monthly Fees"
...> FROM STUDENT;
1564.81481481481
sqlite>

示例4：

使用具有的GROUP BY子句的AVG()函数：

从student表中检索NAME和FEES，并按AGE分组数据：

SELECT NAME, SUM(FEES) AS "Avg Fees by Name"

FROM STUDENT

GROUP BY AGE;

执行上面代码，得到以下结果 -

sqlite> SELECT NAME, SUM(FEES) AS "Avg Fees by Name"
...> FROM STUDENT
...> GROUP BY AGE;
Javasu|18000.0
Maxsu|16000.0
Linsu|80000.0
Sqlsu|25000.0
Linsu|30000.0
sqlite>

catroom

SQLite MIN()函数 MAX()函数 AVG()函数