查询各个年龄段人数大于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