Sql 关于连接、聚合函数的问题

Question

score表

问题出现的语句
问题：框起来的那句话为啥执行后只返回一条数据？正常的话结果应该是三条

Answer 1

--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)