本人使用MySQL資料庫，
<1> 練習group By
首先建立2個表
create table department(department_id int,department_name varchar(10)) ;
create table staff(id int,name varchar(20),sex varchar(20),department_id int)
插入資料
insert into department select 1,'d1';
insert into department select 2,'d2' ;
insert into department select 3,'d3' ;
insert into department select 4,'d4' ;
insert into staff select 1,'qjy1','m',1;
insert into staff select 2,'qjy2' ,'m',1 ;
insert into staff select 3,'qjy3','m' ,1;
insert into staff select 4,'qjy4' ,'m' ,1;
insert into staff select 5,'qjy1','m',1;
insert into staff select 6,'qjy2' ,'m',1 ;
insert into staff select 7,'qjy3','m' ,1;
insert into staff select 8,'qjy4' ,'m' ,1;
insert into staff select 9,'qjy1','m',2;
insert into staff select 10,'qjy2' ,'m',2 ;
insert into staff select 11,'qjy3','m' ,2;
insert into staff select 12,'qjy4' ,'m' ,4;
insert into staff select 13,'qjy1','m',4;
insert into staff select 14,'qjy2' ,'m',4 ;
insert into staff select 15,'qjy3','m' ,4;
insert into staff select 16,'qjy4' ,'m' ,4;
需求：查出 部門資訊 ，要求查詢每個部門的員工數，並按照統計的員工數倒序排列，不用select 子查詢
select d.*,count(*) as staffCount from staff as s join department as d on s.department_id = d.department_id group by d.department_id having count(*)>1 order by staffCount DESC
查詢結果：
query result(3 records)
| department_id | department_name | staffCount |
| 1 | d1 | 8 |
| 4 | d4 | 5 |
| 2 | d2 | 3 |
聯絡目的 ：
1.熟悉group by ..having
2.table,列 取別名 ，以及別名的用處 ：order by staffCount DESC
3.升序 ASC ，倒序是 DESC
<2> avg函式 ，及
| id | sname | smoney | sprovince |
| 1 | zhangsan | 2098 | A |
| 2 | lisi | 3000 | B |
| 3 | wangwu | 6789 | C |
| 4 | liumazi | 4587 | C |
| 5 | dongjiu | 3298 | B |
| 6 | shiga | 4567 | A |
id:合同id sname:姓名 smoney :業績 sprovince:地區
第一道：顯示出 業績 大於同一地區平均值的 合同id 姓名 地區 業績
第二道：把同一地區的 平均業績 地區 插入到新表中 （新表只包含兩個欄位即：平均業績 地區）
1. select t1.* ,avgTemp from mytable as t1 join ( select avg(m.smoney) as avgTemp, m.sprovince from mytable as m group by m.sprovince) as t2 where t1.smoney > avgTemp and t1.sprovince = t2.sprovince; 2. create table newTable (avgMoney int ,sprovince varchar(20)); insert into newTable select avg(m.smoney) , m.sprovince from mytable as m group by m.sprovince