时间戳

首页 > 解决方案 > 将 MIN() 函数与 INNER JOIN 一起使用

mysql - 将 MIN() 函数与 INNER JOIN 一起使用

问题描述

我有一个小问题。我正在 Coursera 上进行数据科学 SQL 课程，并且有以下任务：

问题 7：使用 INNER JOIN，找到困难为 96 的社区区域的最低“学生平均出勤率”。

我解决了这样的问题：

SELECT S.AVERAGE_STUDENT_ATTENDANCE, S.COMMUNITY_AREA_NAME, C.HARDSHIP_INDEX
FROM CHICAGO_PUBLIC_SCHOOLS as S
INNER JOIN CENSUS_DATA as C
ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
WHERE C.HARDSHIP_INDEX = 96
ORDER BY S.AVERAGE_STUDENT_ATTENDANCE ASC
LIMIT 1;

但是，最初我想在没有ORDER BYand的情况下优雅地解决它LIMIT。该MIN(S.AVERAGE_STUDENT_ATTENDANCE)功能对我不起作用 - 我收到一个错误：

An expression starting with "HARDSHIP_INDEX" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified.. SQLCODE=-119, SQLSTATE=42803, DRIVER=4.24.92

有没有办法用MIN解决这个问题？

非常感谢。

标签： mysqlsqlinner-joinmin

解决方案

一种使用 min 的方法是这样的：

SELECT
    S.AVERAGE_STUDENT_ATTENDANCE
  , S.COMMUNITY_AREA_NAME
  , C.HARDSHIP_INDEX
FROM CHICAGO_PUBLIC_SCHOOLS AS S
INNER JOIN CENSUS_DATA AS C ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
WHERE C.HARDSHIP_INDEX = 96
AND S.AVERAGE_STUDENT_ATTENDANCE = (
    SELECT
        MIN( S.AVERAGE_STUDENT_ATTENDANCE )
    FROM CHICAGO_PUBLIC_SCHOOLS AS S
    INNER JOIN CENSUS_DATA AS C ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
    WHERE C.HARDSHIP_INDEX = 96
)

根据您使用的数据库，如果它支持公用表表达式，您可以这样做：

WITH cte
AS (
    SELECT
        S.AVERAGE_STUDENT_ATTENDANCE
      , S.COMMUNITY_AREA_NAME
      , C.HARDSHIP_INDEX
    FROM CHICAGO_PUBLIC_SCHOOLS AS S
    INNER JOIN CENSUS_DATA AS C ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
    WHERE C.HARDSHIP_INDEX = 96
)
SELECT
    *
FROM cte
WHERE AVERAGE_STUDENT_ATTENDANCE = (
    SELECT
        MIN( AVERAGE_STUDENT_ATTENDANCE )
    FROM cte
)

如果您的 MySQL 版本支持窗口函数（v8 或更高版本），那么类似这样的东西也可以工作：

SELECT
    AVERAGE_STUDENT_ATTENDANCE
  , COMMUNITY_AREA_NAME
  , HARDSHIP_INDEX
FROM (
    SELECT
        S.AVERAGE_STUDENT_ATTENDANCE
      , S.COMMUNITY_AREA_NAME
      , C.HARDSHIP_INDEX
      , MIN( S.AVERAGE_STUDENT_ATTENDANCE ) OVER () AS min_att
    FROM CHICAGO_PUBLIC_SCHOOLS AS S
    INNER JOIN CENSUS_DATA AS C ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
    WHERE C.HARDSHIP_INDEX = 96
) sq
WHERE AVERAGE_STUDENT_ATTENDANCE = min_att

请注意OVER()，over 子句中的任何内容都不允许在所有数据库中使用，它可能需要类似的东西over(partition by C.HARDSHIP_INDEX)。

推荐阅读