SQL整理六
员工业绩表：inwork+----+------------+----------+------------+| id sname smoney sprovice+----+------------+----------+------------+| 1 zhangsan 2098 A+----+------------+----------+------------+| 2 lishi 3000 B+----+------------+----------+------------+| 3 wangwu 6789 C+----+------------+----------+------------+| 4 liumazi 4587 C+----+------------+----------+------------+| 5 dongjiu 3298 B+----+------------+----------+------------+| 6 shiga 4567 A+----+------------+----------+------------+| 7 heiqi 4007 A+----+------------+----------+------------+| 8 diuba 3500 B+----+------------+----------+------------+| 9 behkanm 2900 C+----+------------+----------+------------+SQL:+-------------------------------------------------------+CREATE TABLE IF NOT EXISTS `inwork` (`id` int(11) NOT NULL auto_increment,`sname` varchar(20) default NULL,`smoney` int(11) default NULL,`sprovice` varchar(12) default NULL,PRIMARY KEY(`id`)) ENGINE=MyISAMDEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;INSERT INTO `inwork` VALUES (1, 'zhangsan', 2098, 'A');INSERT INTO `inwork` VALUES (2, 'lishi', 3000, 'B');INSERT INTO `inwork` VALUES (3, 'wangwu', 6789, 'C');INSERT INTO `inwork` VALUES (4, 'liumazi', 4587, 'C');INSERT INTO `inwork` VALUES (5, 'dongjiu', 3298, 'B');INSERT INTO `inwork` VALUES (6, 'shiga', 4567, 'A');INSERT INTO `inwork` VALUES (7, 'heiqi', 4007, 'A');INSERT INTO `inwork` VALUES (8, 'diuba', 3500, 'B');INSERT INTO `inwork` VALUES (9, 'behkanm', 2900, 'C');+-------------------------------------------------------+要求：1.显示出业绩大于同一地区业绩值最大的合同id ，姓名，地区，业绩+-------------------------------------------+|SELECT a.id, a.sname, a.smoney, a.sprovice ||FROM inwork a, ( || SELECT max( smoney ) AS maxmoney, sprovice|| FROM inwork || GROUP BY sprovice ||)b ||WHERE a.smoney = b.maxmoney ||AND a.sprovice = b.sprovice |+-------------------------------------------+2.显示出业绩大于同一地区平均值的合同id ，姓名，地区，业绩+-------------------------------------------+|SELECT a.id, a.sname, a.smoney, a.sprovice ||FROM inwork a, ( || SELECT avg( smoney ) AS avgmoney, sprovice|| FROM inwork || GROUP BY sprovice ||)b ||WHERE a.smoney > b.avgmoney ||AND a.sprovice = b.sprovice |+-------------------------------------------+3.把同一地区的平均业绩，地区插入到新表中（新表只包含两个字段即：平均业绩和地区）+-------------------------------------------+|create table t1 as( || select avg(smoney) AS avgmoneys, sprovice | | from inwork || group by sprovice) ; |+-------------------------------------------+
页:
[1]