在MySql(8.0.13)中,不使用ORDERBY和limit子句,按列分组,查找max和second max之间的差异

在MySQL 8.0.2+中,最简单的 而且可能是最有效的 方法是使用 Window Functions .

我们将使用 Row_Number() ID 和 ad_id . 行号将按降序排列 amount_time 价值所以最高的 时间量 值的行号为1,第二高的行号为2。

现在,我们将使用此结果集作为 Derived Table ,及合计( GROUP BY )在 身份证件 和 . 我们可以使用条件语句 CASE .. WHEN 表达式,以确定每个组中最高值和第二高值之间的差异。

SELECT
  dt.ID, 
  dt.ad_id, 
  (MAX(CASE WHEN dt.row_no = 1 THEN dt.amount_time END) - 
  MAX(CASE WHEN dt.row_no = 2 THEN dt.amount_time END)) AS diff_amount_time
FROM 
(
 SELECT 
   ID, 
   ad_id, 
   amount_time, 
   ROW_NUMBER() OVER (PARTITION BY CONCAT(ID, '-', ad_id)
                      ORDER BY amount_desc) AS row_no
 FROM ads 
) AS dt 
GROUP BY dt.ID, dt.ad_id 
-- to remove cases where there is no second highest
-- when there is no second highest amount, then the difference will be null
-- because 5 - null = null
HAVING diff_amount_time IS NOT NULL