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 姓名 地区 业绩
第二道：把同一地区的 平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）
create table T1
(
id int primary key not null,
sname varchar(20) unique not null,
smoney int not null,
sprovince varchar(20) not null
);
K, 0 rows affected (0.08 sec)
insert into T1 (id,sname,smoney,sprovince)values
(2,'bb',2025,'A');
K, 1 row affected (0.03 sec)
insert into T1 (id,sname,smoney,sprovince)values
(3,'cc',3025,'B');
K, 1 row affected (0.01 sec)
insert into T1 (id,sname,smoney,sprovince)values
(4,'dd',3455,'C');
K, 1 row affected (0.02 sec)
insert into T1 (id,sname,smoney,sprovince)values
(5,'ee',7025,'B');
K, 1 row affected (0.01 sec)
insert into T1 (id,sname,smoney,sprovince)values
(6,'ff',3905,'A');
K, 1 row affected (0.02 sec)
insert into T1 (id,sname,smoney,sprovince)values
(7,'gg',6354,'d');
K, 1 row affected (0.01 sec)
select * from T1;
+----+-------+--------+-----------+
| id | sname | smoney | sprovince |
+----+-------+--------+-----------+
| 1 | aa | 1025 | A |
| 2 | bb | 2025 | A |
| 3 | cc | 3025 | B |
| 4 | dd | 3455 | C |
| 5 | ee | 7025 | B |
| 6 | ff | 3905 | A |
| 7 | gg | 6354 | d |
+----+-------+--------+-----------+
in set (0.00 sec)
mysql> select a.* from T1 a where a.smoney>
(select avg(b.smoney) from T1 b where b.sprovince=a.sprovince); 答案一：
+----+-------+--------+-----------+
| id | sname | smoney | sprovince |
+----+-------+--------+-----------+
| 5 | ee | 7025 | B |
| 6 | ff | 3905 | A |
+----+-------+--------+-----------+
mysql> create table T2 as(
-> select avg(smoney) avgmoney,sprovince from T1 group by sprovince);
Query OK, 4 rows affected, 1 warning (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql> select * from T2;
+-----------+-----------+
| avgmoney | sprovince |
+-----------+-----------+
| 2318.3333 | A |
| 5025.0000 | B |
| 3455.0000 | C |
| 6354.0000 | d |
+-----------+-----------+
4 rows in set (0.00 sec)
posted on
