笔试题-sql语句

今天遇到了不熟练(不会)的查询题目

  • 回来自己又做了一下,如下

建表语句


-- Table structure for score


DROP TABLE IF EXISTS score;
CREATE TABLE score (
id int(11) DEFAULT NULL,
sno int(11) DEFAULT NULL,
name varchar(255) DEFAULT NULL,
subject varchar(255) DEFAULT NULL,
score varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of score


INSERT INTO score VALUES ('1', '201201', '小明', 'math', '90');
INSERT INTO score VALUES ('2', '201202', '小李', 'math', '99');
INSERT INTO score VALUES ('3', '201203', '小红', 'math', '43');
INSERT INTO score VALUES ('4', '201201', '小明', 'en', '66');
INSERT INTO score VALUES ('5', '201202', '小李', 'en', '56');
INSERT INTO score VALUES ('6', '201203', '小红', 'en', '88');
INSERT INTO score VALUES ('7', '201201', '小明', 'computer', '99');
INSERT INTO score VALUES ('8', '201202', '小李', 'computer', '65');
INSERT INTO score VALUES ('9', '201203', '小红', 'computer', '67');
INSERT INTO score VALUES ('11', '201204', '小白', 'sss', '99');
INSERT INTO score VALUES ('11111', '201203', '小红', 'computer', '67');

  • 题目1:找到所有分数都及格(>=60)的学生。

    1. 查询有科目分数小于 60 的, 然后 not in 即可
      select name from score where sno not in
      (select s.sno from score s where s.score < 60) GROUP BY sno

    2. not exists 写法
      select name from score s1 where not exists
      (select 1 from score s where s.score < 60 and s1.sno = s.sno) GROUP BY s1.sno

    3. having 筛选
      select * from score s GROUP BY s.sno having min(s.score) >= 60

  • 题目2:表中有冗余记录,如何删除?如下图:

分析:找到该数据对应的id,根据id删除!

select s.id from score s group by s.sno,s.name,s.subject,s.score -- 此结果不包含需要删除的id
本以为是:delete from score where id not in (select s.id from score s group by s.sno,s.name,s.subject,s.score )

  • 结果毫不犹豫的抛出错误:

  • 查明原因,mysql认为delete/update 跟 子句里面select到的不能是一张表

  • 做出改进(表起别名,按照需要删掉新插入的或者后插入的)

delete from score where id not in (select id from (select min(id) id from score s group by sno,name,subject,score )t)

posted @ 2018-03-08 20:52  kangkaii  阅读(896)  评论(0编辑  收藏  举报