使用MAX计算子查询列最大值，查询第二多风格的乐队信息

嘿，别慌！你已经迈出了关键一步——排除了风格数量最多的乐队，接下来只需要从剩下的结果里锁定“次最大值”，再匹配对应的乐队就搞定了。下面给你两种可行的方案，适配不同的数据库环境：

方案一：用窗口函数（推荐，简洁高效） #

如果你的数据库支持窗口函数（比如PostgreSQL、MySQL 8.0+、SQL Server等），用DENSE_RANK()可以轻松处理并列排名的情况，比如多个乐队并列第一时，能准确找到第二梯队的乐队：

-- 先计算每个乐队的不同风格数量
WITH band_style_counts AS (
    SELECT band_id, COUNT(DISTINCT style) AS num_styles
    FROM band_style
    GROUP BY band_id
),
-- 给每个乐队按风格数量降序排名
ranked_bands AS (
    SELECT 
        band_id, 
        num_styles,
        DENSE_RANK() OVER (ORDER BY num_styles DESC) AS style_rank
    FROM band_style_counts
)
-- 筛选出排名为2的乐队
SELECT band_id, num_styles AS NUM
FROM ranked_bands
WHERE style_rank = 2;

为什么用DENSE_RANK()而不是RANK()？举个例子：如果有3个乐队都是最多的风格数（排名1），DENSE_RANK()会把下一个数量的乐队直接标为排名2，而RANK()会跳成排名4，显然前者更符合我们要找“第二多”的需求。

方案二：子查询嵌套（兼容老版本数据库） #

如果你的数据库不支持CTE和窗口函数（比如MySQL 5.x及以下），可以用多层子查询来实现：

步骤1：找到所有乐队的风格数量里的最大值 #

SELECT MAX(num_styles) AS max_style
FROM (
    SELECT COUNT(DISTINCT style) AS num_styles
    FROM band_style
    GROUP BY band_id
) AS style_counts;

步骤2：找到小于最大值的所有数量里的最大值（也就是第二大值） #

SELECT MAX(num_styles) AS second_max_style
FROM (
    SELECT COUNT(DISTINCT style) AS num_styles
    FROM band_style
    GROUP BY band_id
) AS style_counts
WHERE num_styles < (
    SELECT MAX(num_styles)
    FROM (
        SELECT COUNT(DISTINCT style) AS num_styles
        FROM band_style
        GROUP BY band_id
    ) AS inner_counts
);

步骤3：匹配对应乐队 #

把上面的子查询整合起来，最终得到结果：

SELECT band_id, COUNT(DISTINCT style) AS NUM
FROM band_style
GROUP BY band_id
HAVING COUNT(DISTINCT style) = (
    SELECT MAX(num_styles) AS second_max_style
    FROM (
        SELECT COUNT(DISTINCT style) AS num_styles
        FROM band_style
        GROUP BY band_id
    ) AS style_counts
    WHERE num_styles < (
        SELECT MAX(num_styles)
        FROM (
            SELECT COUNT(DISTINCT style) AS num_styles
            FROM band_style
            GROUP BY band_id
        ) AS inner_counts
    )
);

这两种方案都能处理各种边界情况：比如所有乐队风格数相同（此时第二多就是这个相同的数）、只有一个乐队是最多的（剩下的都是第二多）等等。

内容的提问来源于stack exchange，提问作者rubyquartz