进阶的清欢
2021-08-21 14:59
采纳率: 66.7%
浏览 69

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

score表
img

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

img

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • Sandata! 2021-08-25 09:17
    已采纳
    
    --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)
    
    
    评论
    解决 1 无用
    打赏 举报

相关推荐 更多相似问题