Sql 关于连接、聚合函数的问题
score表
问题出现的语句
问题：框起来的那句话为啥执行后只返回一条数据？正常的话结果应该是三条
--PostgreSQL 中实现
CREATE TABLE score(sno integer,cno varchar,degree decimal);
INSERT INTO score
VALUES
(101,'3-105',64.0),
(101,'6-166',85.0),
(103,'3-105',92.0),
(103,'3-245',86.0),
(105,'3-105',88.0),
(105,'3-245',75.0),
(107,'3-105',91.0),
(107,'6-166',79.0),
(108,'3-105',78.0),
(108,'6-166',81.0),
(109,'3-105',76.0),
(109,'3-245',68.0);
--题目要求查询课程编号为3-105的课程，并且 degree(成绩)至少要高于课程编号为3-245的同学的信息，并按照成绩(degree)进行降序排列。
--分解1.查询课程编号为3-105的学生成绩信息
SELECT sno,cno,degree
FROM score
WHERE cno = '3-105';
--结果
sno | cno | degree
-----+-------+--------
101 | 3-105 | 64.0
103 | 3-105 | 92.0
105 | 3-105 | 88.0
107 | 3-105 | 91.0
108 | 3-105 | 78.0
109 | 3-105 | 76.0
--分解2.求课程 3-245 学生的最高成绩
SELECT max(degree) AS "3-245_max_score"
FROM score
WHERE cno = '3-245';
--结果
3-245_max_score
-----------------
86.0
--分解3.只要3-105课程的学生成绩大于分解2中的86分即可。
SELECT sno,cno,degree
FROM score
WHERE cno = '3-105'
AND degree > 86.0;
--结果
sno | cno | degree
-----+-------+--------
103 | 3-105 | 92.0
105 | 3-105 | 88.0
107 | 3-105 | 91.0
--以上为我们得到的最终结果
--下面为合并写法
SELECT a.sno,
a.cno,
a.degree
FROM score a
WHERE a.degree >
(SELECT max(degree)
FROM score
WHERE cno = '3-245')
AND a.cno = '3-105'
ORDER BY a.degree DESC;
--结果
sno | cno | degree
-----+-------+--------
103 | 3-105 | 92.0
107 | 3-105 | 91.0
105 | 3-105 | 88.0
--楼主的写法错误在于：
1.SELECT 列表中没有使用聚合函数的列一般要带有GROUP BY 子句，楼主的写法是MySQL中特有的写法，聚合函数在 ANSI SQL 中需要结合 GROUP BY 一起使用，当然，这个是没有问题的。
2.错句在于使用了，JOIN 的实质在于嵌套循环。
--以下是MySQL 中的写法：
CREATE TABLE score(sno integer,cno varchar(80),degree numeric(4,2));
INSERT INTO score
VALUES
(101,'3-105',64.0),
(101,'6-166',85.0),
(103,'3-105',92.0),
(103,'3-245',86.0),
(105,'3-105',88.0),
(105,'3-245',75.0),
(107,'3-105',91.0),
(107,'6-166',79.0),
(108,'3-105',78.0),
(108,'6-166',81.0),
(109,'3-105',76.0),
(109,'3-245',68.0);
--分解1.查询课程编号为3-105的学生成绩信息
+------+-------+--------+
| sno | cno | degree |
+------+-------+--------+
| 101 | 3-105 | 64.00 |
| 103 | 3-105 | 92.00 |
| 105 | 3-105 | 88.00 |
| 107 | 3-105 | 91.00 |
| 108 | 3-105 | 78.00 |
| 109 | 3-105 | 76.00 |
+------+-------+--------+
6 rows in set (0.00 sec)
--分解2.求课程 3-245 学生的最高成绩
SELECT max(degree) AS "3-245_max_score"
FROM score
WHERE cno = '3-245';
+-----------------+
| 3-245_max_score |
+-----------------+
| 86.00 |
+-----------------+
1 row in set (0.00 sec)
--下面为MySQL 中在聚合函数中带有 group by 和不带有 group by 的结果
SELECT sno,max(degree) as max
FROM score
WHERE cno = '3-245';
--按照上面写法将会抛出一个在聚合函数中没有使用 GROUP BY 子句的一个错误，与 SQL_MODE 有关
mysql>SELECT sno,max(degree) as max
->FROM score
->WHERE cno = '3-245';
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'db1.score.sno'; this is incompatible with sql_mode=only_full_group_by
--设置 SQL_MODE 模式可以在带有聚合函数的列中不使用 GROUP BY 子句
mysql> SET @@SQL_MODE='';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql> SELECT sno,max(degree) as max
-> FROM score
-> WHERE cno = '3-245';
+------+-------+
| sno | max |
+------+-------+
| 103 | 86.00 |
+------+-------+
1 row in set (0.00 sec)
--那么根据楼主的写法，并且使用了 LEFT JOIN 写法，那么意味着，
左边的表(即 cno = '3-105'的查询)，该查询将会查询出来 6 条记录，
而关联表(即 SQL 子查询)，查询出来一条记录，那么SQL执行的过程为
前面的6条记录和子查询的一条记录会根据条件逐行匹配，那么会返回6行值。
--如下,去掉 WHERE 条件
SELECT a.sno,a.cno,a.degree
FROM score a
LEFT JOIN
(SELECT sno,max(degree) AS max
FROM score WHERE cno = '3-245') b
ON a.sno = b.sno
WHERE a.cno = '3-105';
--结果
+------+-------+--------+
| sno | cno | degree |
+------+-------+--------+
| 103 | 3-105 | 92.00 |
| 101 | 3-105 | 64.00 |
| 105 | 3-105 | 88.00 |
| 107 | 3-105 | 91.00 |
| 108 | 3-105 | 78.00 |
| 109 | 3-105 | 76.00 |
+------+-------+--------+
6 rows in set (0.00 sec)
--上面的SQL实际上是
SELECT a.sno AS sno,
a.cno AS cno,
a.degree AS degree
FROM score a
LEFT JOIN (
SELECT score.sno AS sno,
max(score.degree) AS max
FROM score
WHERE (score.cno = '3-245')) b on((b.sno = a.sno))
WHERE (a.cno = '3-105')；
--而再加上条件则变成：
SELECT a.sno,a.cno,a.degree
FROM score a
LEFT JOIN
(SELECT sno,max(degree) AS max
FROM score WHERE cno = '3-245') b
ON a.sno = b.sno
WHERE a.cno = '3-105' AND a.degree > b.max;
相当于如下SQL:
SELECT a.sno AS sno,
a.cno AS cno,
a.degree AS degree
FROM score a
WHERE ((a.sno = '103')
AND (a.cno = '3-105')
AND (a.degree > '86.00')
AND (a.degree > '86.00'));
--因此楼主最终的结果只有一条。
--正确写法如下：
SELECT a.sno,
a.cno,
a.degree
FROM score a
WHERE a.degree >
(SELECT max(degree)
FROM score
WHERE cno = '3-245')
AND a.cno = '3-105'
ORDER BY a.degree DESC;
+------+-------+--------+
| sno | cno | degree |
+------+-------+--------+
| 103 | 3-105 | 92.00 |
| 107 | 3-105 | 91.00 |
| 105 | 3-105 | 88.00 |
+------+-------+--------+
3 rows in set (0.00 sec)