使用带有内部连接的min()函数

pgvzfuti 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(385)

我有个小问题。我正在coursera上一门数据科学sql课程，有以下任务：
问题7：使用内部连接，找到困难社区地区的最低“平均学生出勤率”为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 BY 以及 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解决这个问题？
非常感谢你。

sql mysql Min inner-join

来源：https://stackoverflow.com/questions/53974679/using-the-min-function-with-inner-join

2条答案

按热度按时间

lp0sw83n1#

你的方法是正确的。但是，您可以使用 group_concat() / substring_index() 如果你真的想玩这个把戏：

SELECT MAX(S.AVERAGE_STUDENT_ATTENDANCE),
       SUBSTRING_INDEX(GROUP_CONCAT(S.COMMUNITY_AREA_NAME ORDER BY S.AVERAGE_STUDENT_ATTENDANCE ASC), ',', 1),
       SUBSTRING_INDEX(GROUP_CONCAT(C.HARDSHIP_INDEX ORDER BY S.AVERAGE_STUDENT_ATTENDANCE ASC), ',', 1)
FROM CHICAGO_PUBLIC_SCHOOLS S INNER JOIN
     CENSUS_DATA  C
     ON C.COMMUNITY_AREA_NUMBER = S.COMMUNITY_AREA_NUMBER
WHERE C.HARDSHIP_INDEX = 96;

赞(0）回复(0）举报 2021-06-18

6pp0gazn2#

使用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() i、 e.over子句中没有任何内容，不允许在所有数据库中使用，它可能需要类似 over(partition by C.HARDSHIP_INDEX) 相反。

赞(0）回复(0）举报 2021-06-17

我来回答

使用带有内部连接的min()函数

2条答案

相关问题

热门标签

最新问答