答：SELECT NAME FROM student_info GROUP BY NAME HAVING MIN(fenshu)>80 这里面的关键点是如果select是姓名，那么要group by姓名，那么一个人的下面有多个学科，那么我们可以对这些学科进行聚合操作，就是说可以求sum,min,max等，那么最小值都要大于80分的留下即可。
打印结果： )]
学生表自动编号 学号 姓名 课程编号 课程名称 分数
删除除了自动编号不同, 其他都相同的学生冗余信息
-- 建表语句 -- ---------------------------- -- Table structure for studentcourse -- ---------------------------- DROP TABLE IF EXISTS `studentcourse`; CREATE TABLE `studentcourse` ( `id` bigint(20) NULL DEFAULT NULL, `number` bigint(20) NULL DEFAULT NULL, `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `courseid` bigint(20) NULL DEFAULT NULL, `coursename` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `score` int(11) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of studentcourse -- ---------------------------- INSERT INTO `studentcourse` VALUES (1, 2005001, '张三', 1, '数学', 69); INSERT INTO `studentcourse` VALUES (2, 2005002, '李四', 1, '数学', 89); INSERT INTO `studentcourse` VALUES (3, 2005001, '张三', 1, '数学', 69);答:
delete from tablename where id not in (select min(id) from tablename group by number, NAME, courseid coursename, score)
这种方法是会报错，需要将子查询的结果保存到一张临时表中，然后查询其ID，然后进行删除。
报错信息如下:
You can't specify target table 'studentcourse' for update in FROM clause
正确的写法如下:
DELETE FROM studentcourse WHERE id NOT IN( SELECT * FROM (SELECT MIN(id) FROM studentcourse GROUP BY number,NAME,courseid,coursename,score) tmp)
关键点是将除了ID的字段进行分组，然后每一组只取一个ID，比如最小的ID，这样就能得到一个表中所有不重复数据的ID，那么使用where id not in 就可以得到重复数据所在行的信息，这个时候直接使用delete from 就可以进行删除操作了。
执行后表中的数据如下：
一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合 -- ---------------------------- -- Table structure for team -- ---------------------------- DROP TABLE IF EXISTS `team`; CREATE TABLE `team` ( `id` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of team -- ---------------------------- INSERT INTO `team` VALUES ('a'); INSERT INTO `team` VALUES ('b'); INSERT INTO `team` VALUES ('c'); INSERT INTO `team` VALUES ('d');答：解这道题需要技巧。
select a.id 队伍一, b.id 队伍二 from team a, team b where a.id < b.id
打印结果：
面试题：怎么把这样一个转化成这样一个结果
建表语句
-- ---------------------------- -- Table structure for birthday -- ---------------------------- DROP TABLE IF EXISTS `birthday`; CREATE TABLE `birthday` ( `year` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `month` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `day` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of birthday -- ---------------------------- INSERT INTO `birthday` VALUES ('1991', '1', '1.1'); INSERT INTO `birthday` VALUES ('1991', '2', '1.2'); INSERT INTO `birthday` VALUES ('1991', '3', '1.3'); INSERT INTO `birthday` VALUES ('1991', '4', '1.4'); INSERT INTO `birthday` VALUES ('1992', '1', '2.1'); INSERT INTO `birthday` VALUES ('1992', '2', '2.2'); INSERT INTO `birthday` VALUES ('1992', '3', '2.3'); INSERT INTO `birthday` VALUES ('1992', '4', '2.4');答案：
核心是行转列
select year, max(case month when '1' then day end) m1, max(case month when '2' then day end) m2, max(case month when '3' then day end) m3, max(case month when '4' then day end) m4 from birthday group by year 说明：复制表(只复制结构,源表名：a新表名：b)答: create table b select * from a where 1 <> 1
原表:------------------------------------- 为了便于阅读,查询此表后的结果显式如下(及格分数为60):
---------------------------------------------------
-- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `courseid` int(11) NOT NULL, `coursename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`courseid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, 'java', '70'); INSERT INTO `course` VALUES (2, 'oracle', '90'); INSERT INTO `course` VALUES (3, 'xml', '40'); INSERT INTO `course` VALUES (4, 'jsp', '30'); INSERT INTO `course` VALUES (5, 'serverlet', '80');答：
select courseid, coursename ,score ,if(score>=60, "pass","fail") as mark from course
select *, case when score >= 60 then "pass" else "fail" end mark from course8
表名：购物信息所有购入商品种类为两种或两种以上的购物人记录
-- ---------------------------- -- Table structure for 购物信息 -- ---------------------------- DROP TABLE IF EXISTS `购物信息`; CREATE TABLE `购物信息` ( `购物人` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `商品名称` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `数量` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of 购物信息 -- ---------------------------- INSERT INTO `购物信息` VALUES ('A', '甲', '2'); INSERT INTO `购物信息` VALUES ('B', '乙', '4'); INSERT INTO `购物信息` VALUES ('C', '丙', '1'); INSERT INTO `购物信息` VALUES ('A', '丁', '2'); INSERT INTO `购物信息` VALUES ('B', '丙', '5'); INSERT INTO `购物信息` VALUES ('C', '丙', '2');答：给出所有购入商品种类为两种或两种以上的购物人记录，注意是购物记录，而不是购物人的是谁。
select * from 购物信息 where 购物人 in (SELECT 购物人 FROM `购物信息` group by 购物人 having count(distinct 商品名称) >= 2)结果打印：
8. info 表
如果要生成下列结果, 该如何写sql语句?
建表语句
-- ---------------------------- -- Table structure for info -- ---------------------------- DROP TABLE IF EXISTS `info`; CREATE TABLE `info` ( `date` datetime NULL DEFAULT NULL, `result` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of info -- ---------------------------- INSERT INTO `info` VALUES ('2005-05-09 00:00:00', 'win'); INSERT INTO `info` VALUES ('2005-05-09 00:00:00', 'lose'); INSERT INTO `info` VALUES ('2005-05-09 00:00:00', 'lose'); INSERT INTO `info` VALUES ('2005-05-09 00:00:00', 'lose'); INSERT INTO `info` VALUES ('2005-05-10 00:00:00', 'win'); INSERT INTO `info` VALUES ('2005-05-10 00:00:00', 'lose'); INSERT INTO `info` VALUES ('2005-05-10 00:00:00', 'lose');答案：
select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;