表结构如下:
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 |
创建表:
DROP TABLE IF EXISTS `workshow`;
CREATE TABLE `workshow` (
`Id` int(11) NOT NULL auto_increment,
`name` varchar(12) default NULL,
`moneys` int(11) default NULL,
`province` varchar(12) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
#
# Dumping data for table workshow
#
/*!40101 SET NAMES utf8 */;
INSERT INTO `workshow` VALUES (1,'zhang',2098,'hebei');
INSERT INTO `workshow` VALUES (2,'wang',3000,'beijing');
INSERT INTO `workshow` VALUES (3,'li',4200,'shanghai');
INSERT INTO `workshow` VALUES (4,'zhao',3687,'beijing');
INSERT INTO `workshow` VALUES (5,'liu',5120,'shanghai');
INSERT INTO `workshow` VALUES (6,'xu',4567,'beijing');
INSERT INTO `workshow` VALUES (7,'sun',3000,'henan');
id:合同id sname:姓名 smoney :业绩 sprovince:地区
问题1:
显示出业绩大于同一地区平均值的合同id ,姓名,地区,业绩
SELECT * FROM workshow;
SELECT Id, name, moneys, a.province AS province FROM workshow AS a,
(SELECT province, (moneys) AS avgmoneys FROM workshow GROUP BY province) AS b
WHERE a.province = b.province AND a.moneys > b.avgmoneys;
问题2:
把同一地区的平均业绩,地区插入到新表中(新表只包含两个字段即:平均业绩和地区)
create table test1 as (select avg(moneys) AS avgmoneys, province from workshow group by province)