【新手提问】关于查询最大最小值的问题

香香王子
  • 1
新手上路,请多包涵

图片描述

有这么两张表(其实表a没用),现在我想查出当 uid IN (1, 3, 4) 且 sort 最小时,最小 sort 关联哪行的字段 anything 的值,sql该怎么写?

SELECT uid,    anything, MIN( sort ) AS sort FROM table_b WHERE uid IN (1, 3, 4) GROUP BY uid;

这种写法只能取出 sort 的最小值,但是取出的 anything 的值并不是 sort 最小值关联的那一行的数据

回复
阅读 1.3k
4 个回答

取得b表id,再join一下

select min (sort) , anything from table_Name where uid in (1,3,4);

大树
  • 388

第一步,分组并查询最小值sort,建立临时表t,将会得到我们想要的sort,但是没有其他字段;

SELECT
    sort
FROM
    (
        SELECT
            MIN(sort) as sort,
            uid
        FROM
            table_b
        GROUP BY
            uid
    )t

第二步,查询所有sort等于临时表t的sort的记录,此时就包含了所有字段。

SELECT
    *
FROM
    table_b
WHERE
    sort in (
          t
    )

第三步, 把第一步和第二部合并得到最终结果:

SELECT
    *
FROM
    table_b
WHERE
    sort in (
        SELECT
            sort
        FROM
            (
                SELECT
                    MIN(sort) as sort,
                    uid
                FROM
                    table_b
                GROUP BY
                    uid
            )t
    )

如果:同一个uid 有重复的 sort, 可以在最后加上 GROUP BY sort 去重。

SELECT
    *
FROM
    table_b
WHERE
    sort in (
        SELECT
            sort
        FROM
            (
                SELECT
                    MIN(sort) as sort,
                    uid
                FROM
                    table_b
                GROUP BY
                    uid
            )t
    )
GROUP BY sort

这题标准答案是使用窗口函数, 用来处理分组中每组的TOPN的问题

SELECT *, RANK() OVER (PARTITION BY uid ORDER BY sort ASC) as r
FROM table_b WHERE uid IN (1, 3, 4);

你可以运行一下这个的结果, 会发现r的值是是当前值在同一组中的次序

由于窗口函数不能进行where, 所以在外层再包裹一次取值就可以了,

原题是取最小值, 即组内次序为1筛选出即可

SELECT * FROM (
    SELECT *, RANK() OVER (PARTITION BY uid ORDER BY sort ASC) as r
    FROM table_b WHERE uid IN (1, 3, 4)
) st WHERE st.r = 1;
宣传栏