SQL基础（一九）--- HAVING与LIMIT

查询各个年龄段人数大于1的年龄段：

mysql> select fage,count(*) from t_employee
    -> group by fage
    -> having count(*)>1;
+------+----------+
| fage | count(*) |
+------+----------+
|   23 |        2 |
|   25 |        2 |
|   28 |        3 |
+------+----------+
3 rows in set

查询各个年龄段人数为1和3的年龄段：

mysql> select fage,count(*) from t_employee
    -> group by fage
    -> having count(*) in(1,3);
+------+----------+
| fage | count(*) |
+------+----------+
|   22 |        1 |
|   27 |        1 |
|   28 |        3 |
+------+----------+
3 rows in set

-------------------------------------------------------------------------------------------------------------------------

按照工资降序排列后，从第二行开始的最多5条信息：

mysql> select * from t_employee
    -> order by fsalary desc
    -> limit 2,5;
+----------+-------+------+---------+-------------+---------------+
| fnumber  | fname | fage | fsalary | fsubcompany | fdepartment   |
+----------+-------+------+---------+-------------+---------------+
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
| SALES001 | John  |   23 | 5000    | Beijing     | Sales         |
| IT001    | Smith |   28 | 3900    | Beijing     | InfoTech      |
| IT002    | NULL  |   27 | 2800    | ShenZhen    | InfoTech      |
| DEV002   | Jerry |   28 | 2300.8  | ShenZhen    | Development   |
+----------+-------+------+---------+-------------+---------------+
5 rows in set

按照工资降序排列后，最多获取前5条信息：

mysql> select * from t_employee
    -> order by fsalary desc
    -> limit 0,5;
+----------+-------+------+---------+-------------+---------------+
| fnumber  | fname | fage | fsalary | fsubcompany | fdepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   25 | 8300    | Beijing     | Development   |
| SALES002 | Kerry |   28 | 6200    | Beijing     | Sales         |
| HR002    | Tina  |   25 | 5200.36 | Beijing     | HumanResource |
| SALES001 | John  |   23 | 5000    | Beijing     | Sales         |
| IT001    | Smith |   28 | 3900    | Beijing     | InfoTech      |
+----------+-------+------+---------+-------------+---------------+
5 rows in set