SQL整理六

最新推荐文章于 2022-02-25 20:00:37 发布

beyond1993

最新推荐文章于 2022-02-25 20:00:37 发布

阅读量103

点赞数

分类专栏： SQL 文章标签： SQL C C++ C#

本文链接：https://blog.csdn.net/beyond1993/article/details/83427113

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏


员工业绩表：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=MyISAM  DEFAULT 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) ;                      |
+-------------------------------------------+