一道MySQL面试题

这是一道面试题,解决办法整合网上方案而来

/*
 Navicat Premium Data Transfer

 Source Server         : vagrant-lamp
 Source Server Type    : MySQL
 Source Server Version : 100208
 Source Host           : 192.168.33.11:3306
 Source Schema         : test

 Target Server Type    : MySQL
 Target Server Version : 100208
 File Encoding         : 65001

 Date: 24/09/2018 19:57:29
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student_scores
-- ----------------------------
DROP TABLE IF EXISTS `student_scores`;
CREATE TABLE `student_scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `class` varchar(255) NOT NULL,
  `score` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student_scores
-- ----------------------------
BEGIN;
INSERT INTO `student_scores` VALUES (1, '张三', '语文', 81);
INSERT INTO `student_scores` VALUES (2, '张三', '数学', 75);
INSERT INTO `student_scores` VALUES (3, '李四', '语文', 76);
INSERT INTO `student_scores` VALUES (4, '李四', '数学', 90);
INSERT INTO `student_scores` VALUES (5, '王五', '语文', 81);
INSERT INTO `student_scores` VALUES (6, '王五', '数学', 100);
INSERT INTO `student_scores` VALUES (7, '王五', '英语', 90);
INSERT INTO `student_scores` VALUES (8, '李四', '化学', 90);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询三科成绩在70分以上的学生的总成绩

SELECT
    sum( r1.score ),
    r1.NAME
FROM
    student_scores r1,
    (
    SELECT
        count( * ) AS num,
    NAME 
    FROM
        student_scores
    WHERE
        score > 60
    GROUP BY
    NAME 
    HAVING
        num >= 3
    ) r2
WHERE
    r1.NAME = r2.NAME
GROUP  BY
r1.NAME;

查询有3科成绩大于70的学生

SELECT COUNT(*) as num,name FROM grade WHERE score>70 GROUP BY NAME HAVING num>=3;

查询每门课都大于80分的同学的姓名

SELECT DISTINCT NAME 
FROM
    student_scores 
WHERE
    NAME NOT IN ( SELECT DISTINCT NAME FROM student_scores WHERE  score <=80 );
  • 或者:
SELECT name FROM student_scores GROUP BY name HAVING MIN(score) > 80;

查询平均分大于80的学生的姓名:

SELECT NAME 
FROM
    (
    SELECT
        COUNT( * ) AS t,
        SUM( score ) AS num,
    NAME 
    FROM
        `student_scores` 
    GROUP BY
    NAME 
    ) AS a 
WHERE
    a.num > 80 * t;
  • 更简单的:
SELECT NAME
    ,
    avg( score ) AS sc 
FROM
    student_scores g1 
GROUP BY
NAME 
HAVING
    avg( score ) > 80;

发表评论

电子邮件地址不会被公开。 必填项已用*标注