MySQL查询问题

Question

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

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;
Sno	CNo	Score
S1	C1	90.00
S1	C2	85.00
S2	C4	70.00
S2	C5	57.00
S2	C6	80.00
S2	C7	NULL
S3	C1	75.00
S3	C2	70.00
S3	C4	85.00
S4	C1	93.00
S4	C2	85.00
S4	C3	83.00
S5	C2	89.00

---

C表

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

---

S表

mysql> SELECT * FROM S;
Sno	Sn	Sex	Age	Birthdary	Dept
S1	赵亦	女	17	1986-09-13	计算机
S2	钱尔	男	18	1984-02-23	信息
S3	孙珊	女	20	1984-01-27	信息
S4	李思	男	21	1986-09-10	自动化
S5	周武	男	19	1984-02-20	计算机
S6	吴丽	女	20	1984-03-20	自动化

Answer 1

测试数据

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

测试结果

Answer 2

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

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

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

Answer 3

由于你给出了表结构和数据，我把答案做了修改，查询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;

查询结果

Sno	CNo	CN	SC.Score	T.Score
S4	C1	程序设计	93.00	87.000000
S1	C1	程序设计	90.00	87.500000
S3	C4	数据结构	85.00	76.666667
S2	C4	数据结构	70.00	51.750000
S2	C5	数据库原理	57.00	51.750000
S2	C6	编译原理	80.00	51.750000

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

Answer 4

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

Answer 5

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

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;

'''