mysql sum() sum(条件表达式) count() count(条件表达式) 总结

很经典的一道mysql题,这道题考查的就是sum和count的用法

Sum()函数里面的参数是列名的时候,是计算列名的值的相加,而不是有值项的总数。
SUM(条件表达式),如果记录满足条件表达式就加1,统计满足条件的行数
count()函数里面的参数是列名的的时候,那么会计算有值项的次数。 (NULL 不计入, 但是’’值计入)
COUNT(*)可以计算出行数,包括null
COUNT(1)也可以计算出行数,1在这里代表一行
count(column)对特定的列的值具有的行数进行计算,不包含NULL值
COUNT(条件表达式),不管记录是否满足条件表达式,只要非NULL就加1
例子:
首先创建个表说明问题
CREATE TABLE result (
name varchar(20) default NULL,
subject varchar(20) default NULL,
score tinyint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
插入一些数据,
insert into result values
(‘张三’,’数学’,90),
(‘张三’,’语文’,50),
(‘张三’,’地理’,40),
(‘李四’,’语文’,55),
(‘李四’,’政治’,45),
(‘王五’,’政治’,30),
(‘赵六’,’语文’,100),
(‘赵六’,’数学’,99),
(‘赵六’,’品德’,98);
解题思路:

## 一种错误做法
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三     | 3 |    60.0000 |
| 李四     | 2 |    50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三     | 3 |    60.0000 |
| 李四     | 2 |    50.0000 |
| 王五     | 1 |    30.0000 |
+------+---+------------+
3 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三     | 3 |    60.0000 |
| 李四     | 2 |    50.0000 |
+------+---+------------+
2 rows in set (0.00 sec)

#加上赵六后错误暴露
mysql> insert into stu 
    -> values 
    -> ('赵六','A',100),
    -> ('赵六','B',99),
    -> ('赵六','C',98);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

#错误显现
mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;
+------+---+------------+
| name | k | avg(score) |
+------+---+------------+
| 张三 | 3 |    60.0000 |
| 李四 | 2 |    50.0000 |
| 赵六 | 3 |    99.0000 |
+------+---+------------+
3 rows in set (0.00 sec)

#正确思路,先查看每个人的平均成绩
mysql> select name,avg(score) from stu group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 |    60.0000 |
| 李四 |    50.0000 |
| 王五 |    30.0000 |
| 赵六 |    99.0000 |
+------+------------+
4 rows in set (0.00 sec)

mysql> # 看每个人挂科情况
mysql> select name,score < 60 from stu;
+------+------------+
| name | score < 60 |
+------+------------+
| 张三 |          0 |
| 张三 |          1 |
| 张三 |          1 |
| 李四 |          1 |
| 李四 |          1 |
| 王五 |          1 |
| 赵六 |          0 |
| 赵六 |          0 |
| 赵六 |          0 |
+------+------------+
9 rows in set (0.00 sec)

mysql> #计算每个人的挂科科目
mysql> select name,sum(score < 60) from stu group by name;
+------+-----------------+
| name | sum(score < 60) |
+------+-----------------+
| 张三 |               2 |
| 李四 |               2 |
| 王五 |               1 |
| 赵六 |               0 |
+------+-----------------+
4 rows in set (0.00 sec)

#同时计算每人的平均分
mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;
+------+-----------------+---------+
| name | sum(score < 60) | pj      |
+------+-----------------+---------+
| 张三 |               2 | 60.0000 |
| 李四 |               2 | 50.0000 |
| 王五 |               1 | 30.0000 |
| 赵六 |               0 | 99.0000 |
+------+-----------------+---------+
4 rows in set (0.00 sec)

#利用having筛选挂科2门以上的.
mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2; 
+------+------+---------+
| name | gk   | pj      |
+------+------+---------+
| 张三 |    2 | 60.0000 |
| 李四 |    2 | 50.0000 |
+------+------+---------+
2 rows in set (0.00 sec)

要求:查询出2门及2门以上不及格者的平均成绩。
经常会用两种查询语句有两种:第1个正确,第2个错误

  1. select name,sum(score < 60) ,avg(score) from result group by name having sum(score<60) >=2;
  2. select name ,count((score<60)!=0) as a,avg(score) from result group by name having a >=2;

mysql统计行数优化:blog.csdn.net/coslay/article/detail...
性能问题
1.任何情况下SELECT COUNT() FROM tablename是最优选择;
2.尽量减少SELECT COUNT(
) FROM tablename WHERE COL = ‘value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
如果表没有主键,那么count(1)比count()快。
如果有主键,那么count(主键,联合主键)比count(
)快。
如果表只有一个字段,count()最快。
count(1)跟count(主键)一样,只扫描主键。count(
)跟count(非主键)一样,扫描整个表。明显前者更快一些。
————————————————
版权声明:本文为CSDN博主「笑-对-人-生」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:blog.csdn.net/a237969287/article/d...

本作品采用《CC 协议》,转载必须注明作者和本文链接
zhaozhangxiao
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!