我想做mysql分组查询,但是报错了。大佬请帮忙解答一下吧

MySQL5.7

/*
Navicat Premium Data Transfer

Source Server : 10.0.1.51
Source Server Type : MySQL
Source Server Version : 50736
Source Host : 10.0.1.51:3306
Source Schema : Student

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

Date: 22/02/2022 20:00:55
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- Table structure for course


DROP TABLE IF EXISTScourse;
CREATE TABLEcourse(
cidvarchar(10) DEFAULT NULL,
cnamevarchar(10) DEFAULT NULL,
tidvarchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of course


BEGIN;
INSERT INTOcourseVALUES ('01', '语文', '02');
INSERT INTOcourseVALUES ('02', '数学', '01');
INSERT INTOcourseVALUES ('03', '英语', '03');
COMMIT;


-- Table structure for sc


DROP TABLE IF EXISTSsc;
CREATE TABLEsc(
sidvarchar(10) DEFAULT NULL,
cidvarchar(10) DEFAULT NULL,
scoredecimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of sc


BEGIN;
INSERT INTOscVALUES ('01', '01', 80.0);
INSERT INTOscVALUES ('01', '02', 90.0);
INSERT INTOscVALUES ('01', '03', 99.0);
INSERT INTOscVALUES ('02', '01', 70.0);
INSERT INTOscVALUES ('02', '02', 60.0);
INSERT INTOscVALUES ('02', '03', 80.0);
INSERT INTOscVALUES ('03', '01', 80.0);
INSERT INTOscVALUES ('03', '02', 80.0);
INSERT INTOscVALUES ('03', '03', 80.0);
INSERT INTOscVALUES ('04', '01', 50.0);
INSERT INTOscVALUES ('04', '02', 30.0);
INSERT INTOscVALUES ('04', '03', 20.0);
INSERT INTOscVALUES ('05', '01', 76.0);
INSERT INTOscVALUES ('05', '02', 87.0);
INSERT INTOscVALUES ('06', '01', 31.0);
INSERT INTOscVALUES ('06', '03', 34.0);
INSERT INTOscVALUES ('07', '02', 89.0);
INSERT INTOscVALUES ('07', '03', 98.0);
COMMIT;


-- Table structure for student


DROP TABLE IF EXISTSstudent;
CREATE TABLEstudent(
sidvarchar(10) DEFAULT NULL,
snamevarchar(10) DEFAULT NULL,
sagedatetime DEFAULT NULL,
ssexvarchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of student


BEGIN;
INSERT INTOstudentVALUES ('01', '赵雷', '1990-01-01 00:00:00', '男');
INSERT INTOstudentVALUES ('02', '钱电', '1990-12-21 00:00:00', '男');
INSERT INTOstudentVALUES ('03', '孙风', '1990-05-20 00:00:00', '男');
INSERT INTOstudentVALUES ('04', '李云', '1990-08-06 00:00:00', '男');
INSERT INTOstudentVALUES ('05', '周梅', '1991-12-01 00:00:00', '女');
INSERT INTOstudentVALUES ('06', '吴兰', '1992-03-01 00:00:00', '女');
INSERT INTOstudentVALUES ('07', '郑竹', '1989-07-01 00:00:00', '女');
INSERT INTOstudentVALUES ('09', '张三', '2017-12-20 00:00:00', '女');
INSERT INTOstudentVALUES ('10', '李四1', '2017-12-25 00:00:00', '女');
INSERT INTOstudentVALUES ('11', '李四2', '2017-12-30 00:00:00', '女');
INSERT INTOstudentVALUES ('12', '赵六', '2017-01-01 00:00:00', '女');
INSERT INTOstudentVALUES ('13', '孙七', '2018-01-01 00:00:00', '女');
COMMIT;


-- Table structure for teacher


DROP TABLE IF EXISTSteacher;
CREATE TABLEteacher(
tidvarchar(10) DEFAULT NULL,
tnamevarchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Records of teacher


BEGIN;
INSERT INTOteacherVALUES ('01', '张三');
INSERT INTOteacherVALUES ('02', '李四');
INSERT INTOteacherVALUES ('03', '王五');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

查询各科成绩前三名的记录

SELECT *
FROM sc
GROUP BY cid #我想在这里分组,但是报错 ERROR:

# 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

# 'Student.sc.sid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with

# sql_mode=only_full_group_by, Time: 0.000000s

ORDER BY cid,score DESC
LIMIT 0,3;

应该怎么写才是正确的呢?

回答

这个目标,使用分组的思路是错误的。

应该使用子查询的思路。与当前行的科目相同,同时成绩大于当前行的条目总数小于3。这样应该就能查出来了,当然,这是考虑到前3中分数值都不一样的情况。

如果还有分数值相同的情况,也是在子查询中,在统计条目数时,对分数使用 distinct 去重。这样应该就没问题了。