SQL面试题（三）

表结构如下：

     

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)