MySQL查询问题

Lince
  • 10

题目:查询每个学生选修课程超过他所选修所有课程平均成绩的课程,最后显示的字段为学号、课程号、课程名

SELECT SC.Sno, SC.CNo, C.CN FROM
SC JOIN C ON SC.CNo=C.CNo
JOIN S ON S.Sno=SC.Sno
LEFT JOIN(
    SELECT Sno,avg(Score) FROM SC
        WHERE (SELECT Score FROM SC) > (SELECT avg(Score) FROM SC GROUP BY Sno)
) AS C ON C.Sno=SC.Sno;

报错:Subquery returns more than 1 row

SC表

mysql> SELECT * FROM SC;
SnoCNoScore
S1C190.00
S1C285.00
S2C470.00
S2C557.00
S2C680.00
S2C7NULL
S3C175.00
S3C270.00
S3C485.00
S4C193.00
S4C285.00
S4C383.00
S5C289.00

C表

mysql> select * from c;
CNoCNCT
C1程序设计60
C2微机原理80
C3数字逻辑60
C4数据结构80
C5数据库原理60
C6编译原理54
C7操作系统60

S表

mysql> SELECT * FROM S;
SnoSnSexAgeBirthdaryDept
S1赵亦171986-09-13计算机
S2钱尔181984-02-23信息
S3孙珊201984-01-27信息
S4李思211986-09-10自动化
S5周武191984-02-20计算机
S6吴丽201984-03-20自动化
回复
阅读 1.1k
5 个回答
✓ 已被采纳
测试数据
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for c
-- ----------------------------
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `CNo` varchar(11) NOT NULL DEFAULT '',
  `CN` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of c
-- ----------------------------
INSERT INTO `c` VALUES ('C1', '程序设计');
INSERT INTO `c` VALUES ('C2', '微机原理');
INSERT INTO `c` VALUES ('C3', '数字逻辑');
INSERT INTO `c` VALUES ('C4', '数据结构');
INSERT INTO `c` VALUES ('C5', '数据库原理');
INSERT INTO `c` VALUES ('C6', '编译原理');
INSERT INTO `c` VALUES ('C7', '操作系统');

-- ----------------------------
-- Table structure for s
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `Sno` varchar(255) NOT NULL DEFAULT '',
  `Sn` varchar(255) DEFAULT NULL,
  `Sex` varchar(255) DEFAULT NULL,
  `Age` int(11) DEFAULT NULL,
  `Birthdary` date DEFAULT NULL,
  `Dept` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('S1', '赵亦', '女', '17', '1986-09-13', '计算机');
INSERT INTO `s` VALUES ('S2', '钱尔', '男', '18', '1984-02-23', '信息');
INSERT INTO `s` VALUES ('S3', '孙珊', '女', '20', '1984-01-27', '信息');
INSERT INTO `s` VALUES ('S4', '李思', '男', '21', '1986-09-10', '自动化');
INSERT INTO `s` VALUES ('S5', '周武', '男', '19', '1984-02-20', '计算机');
INSERT INTO `s` VALUES ('S6', '吴丽', '女', '20', '1984-03-20', '自动化');

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `Sno` varchar(11) NOT NULL DEFAULT '',
  `CNo` varchar(255) DEFAULT NULL,
  `Score` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('S1', 'C1', '90.00');
INSERT INTO `sc` VALUES ('S1', 'C2', '85.00');
INSERT INTO `sc` VALUES ('S2', 'C4', '70.00');
INSERT INTO `sc` VALUES ('S2', 'C5', '57.00');
INSERT INTO `sc` VALUES ('S2', 'C6', '80.00');
INSERT INTO `sc` VALUES ('S2', 'C7', null);
INSERT INTO `sc` VALUES ('S3', 'C1', '75.00');
INSERT INTO `sc` VALUES ('S3', 'C2', '70.00');
INSERT INTO `sc` VALUES ('S3', 'C4', '85.00');
INSERT INTO `sc` VALUES ('S4', 'C1', '93.00');
INSERT INTO `sc` VALUES ('S4', 'C2', '85.00');
INSERT INTO `sc` VALUES ('S4', 'C3', '83.00');
INSERT INTO `sc` VALUES ('S5', 'C2', '89.00');
测试sql
SELECT
    a.Sno,
    a.CNo,
        c.CN,
        a.Score,
        b.avgScore
FROM
    SC AS a
LEFT JOIN (
    SELECT
        Sno,
        Avg(Score) AS avgScore
    FROM
        SC
    GROUP BY
        Sno
) AS b ON a.Sno = b.Sno
left join c as c
on a.CNo = c.CNo
WHERE
    a.Score > b.avgScore
测试结果

image.png

错误信息不是已经写得很详细了吗?子查询返回了大于一条记录。我猜测是因为这里:

(SELECT Score FROM SC) > (SELECT avg(Score) FROM SC GROUP BY Sno)

其中(SELECT Score FROM SC)SELECT avg(Score) FROM SC GROUP BY Sno都必须要返回一条记录才能用于比较,你返回多条记录的话是无法比较的。多条记录是一个集合,而一条记录可以认为是一个标量,只有标量才能在">"中进行比较

由于你给出了表结构和数据,我把答案做了修改,查询SQL如下:

SELECT SC.Sno, SC.CNo, C.CN, SC.Score, T.Score
FROM SC
         RIGHT JOIN S ON SC.Sno = S.Sno
         LEFT JOIN
     (
         SELECT avg(SC.Score) Score, SC.Sno
         FROM SC
                  RIGHT JOIN S ON SC.Sno = S.Sno
         GROUP BY SC.Sno
     ) T ON SC.Score > T.Score AND SC.Sno = T.Sno
         RIGHT JOIN C ON C.Cno = SC.CNo
WHERE T.Score IS NOT NULL;

查询结果

SnoCNoCNSC.ScoreT.Score
S4C1程序设计93.0087.000000
S1C1程序设计90.0087.500000
S3C4数据结构85.0076.666667
S2C4数据结构70.0051.750000
S2C5数据库原理57.0051.750000
S2C6编译原理80.0051.750000

为了方便对比,我把每个人的平均分T.Score打印出来了.
另外我看你SC里的Score有为NULL的情况,这种是不对的,应该为0,需要处理一下,不然增加查询复杂度,需要增加额外判断。

你的子查询返回了多行数据
最好提供一下表结构 只看sql很难帮你

有两种解决的办法:
解决办法一、

SELECT SC.Sno, SC.CNo, C.CN, SC.Score, T.Score
FROM SC
         RIGHT JOIN S ON SC.Sno = S.Sno
         LEFT JOIN
     (
         SELECT avg(SC.Score) Score, SC.Sno
         FROM SC
                  RIGHT JOIN S ON SC.Sno = S.Sno
         GROUP BY SC.Sno
     ) T ON SC.Score > T.Score AND SC.Sno = T.Sno
         RIGHT JOIN C ON C.Cno = SC.CNo
WHERE T.Score IS NOT NULL;

这种方法比较复杂建议可以参考下面的方法
两种方法都可以 看个人的操作习惯问题


'''

SELECT X.Sno,X.CNo,C.CN FROM SC AS X,C,(SELECT Sno,AVG(Score) AS AVG FROM SC GROUP BY SNO)AS Y
WHERE X.CNo=C.CNo and X.Sno=Y.Sno and X.Score > Y.AVG;

'''

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏