作者:LAMP小白 点击:1986 发布日期:2012-10-18 22:58:00 返回列表
1.去掉重复值
DISTINCT能够把查询结果中的重复数据去掉
mysql> SELECT DISTINCT birth FROM president ORDER BY birth;
+------------+
| birth |
+------------+
| 2012-09-02 |
| 2012-09-03 |
| 2012-09-10 |
| 2012-09-11 |
| 2012-10-01 |
+------------+
5 rows in set (0.00 sec)
2.COUNT统计
COUNT只统计全体非NULL值的个数,所以我们可以利用它来统计有效数量
mysql> SELECT COUNT(*), COUNT(birth), COUNT(death) FROM president;
+----------+--------------+--------------+
| COUNT(*) | COUNT(birth) | COUNT(death) |
+----------+--------------+--------------+
| 6 | 6 | 3 |
+----------+--------------+--------------+
1 row in set (0.00 sec)
COUNT和GROUP一起用可以方便的统计某个字段统一类型的数量
mysql> SELECT city, COUNT(*) FROM president GROUP BY city;
+--------+----------+
| city | COUNT(*) |
+--------+----------+
| CN | 1 |
| EU | 1 |
| JP | 2 |
| TOYOTA | 1 |
| US | 1 |
+--------+----------+
5 rows in set (0.01 sec)
HAVING字句特别适合用来查找在某个数据列里重复出现的值或不重复出现的值
mysql> SELECT city, COUNT(*) AS count FROM president GROUP BY city HAVING count=1;
+--------+-------+
| city | count |
+--------+-------+
| CN | 1 |
| EU | 1 |
| TOYOTA | 1 |
| US | 1 |
+--------+-------+
4 rows in set (0.01 sec)
除了COUNT之外,还有MIN() MAX() SUN() AVG()用来得出最小值,最大值,总和和平均值
WITH ROLLUP将使MYSQL对数据行分组统计结果进行进一步的统计而得到一个超级聚合值
mysql> SELECT city, COUNT(*) FROM president GROUP BY city WITH ROLLUP;
+--------+----------+
| city | COUNT(*) |
+--------+----------+
| CN | 1 |
| EU | 1 |
| JP | 2 |
| TOYOTA | 1 |
| US | 1 |
| NULL | 6 |
+--------+----------+
6 rows in set (0.00 sec)
NULL表明与它同在一行的数值是他前面那些分组统计结果的汇总统计值
上一篇:excel分页导出 下一篇:快递查询API