SQLite COUNT()函数

本篇文章帮大家学习SQLite COUNT()函数,包含了SQLite COUNT()函数使用方法、操作技巧、实例演示和注意事项,有一定的学习价值,大家可以用来参考。

SQLite COUNT()函数用于检索表达式或给定列的行数。

语法

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

GROUP BY子句中使用COUNT()函数时的语法:

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

示例1:

假设有一个名为student的表,具有以下数据:

student表中检索AGE大于22的学生人数:

SELECT COUNT(*) AS "Number of students"  
FROM STUDENT  
WHERE AGE > 22;

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

sqlite> SELECT COUNT(*) AS "Number of students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22;
8
sqlite>

示例2:

计算AGE大于22岁的学生人数,并按学生名字分组。

SELECT NAME, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
WHERE AGE > 22  
GROUP BY NAME;

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

sqlite> SELECT NAME, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> WHERE AGE > 22
   ...> GROUP BY NAME;
Avgsu|1
Linsu|2
Maxsu|2
Minsu|2
Sqlsu|1
sqlite>

示例3:

计算每个地址的学生总人数,按学生地址(Address)分组。

SELECT ADDRESS, COUNT(*) AS "Number Of Students"  
FROM STUDENT  
GROUP BY ADDRESS;

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

sqlite> SELECT ADDRESS, COUNT(*) AS "Number Of Students"
   ...> FROM STUDENT
   ...> GROUP BY ADDRESS;
Beijing|1
Guangzhou|2
Haikou|2
Shanghai|1
Shengzhen|2
Shenzhen|1
sqlite>
    

站长公告

极客大全致力于分享java、c#、php、python等编程语言教程,帮助广大程序员解决问题。

热门标签