MySql 每组中第二小的元素

Question

我有一个类似下面的表格:

    date    |   expiry
-------------------------    
2010-01-01  | 2010-02-01
2010-01-01  | 2010-03-02
2010-01-01  | 2010-04-04
2010-02-01  | 2010-03-01
2010-02-01  | 2010-04-02

在表中，每个日期可能有多个'expiry'值。我需要一个返回每个日期中第 n 个最小到期时间的查询。例如，对于 n = 2，我希望:

     date    |   expiry
-------------------------       
2010-01-01  | 2010-03-02
2010-02-01  | 2010-04-02

我的问题是 AFAIK，没有返回第 n 个最大/最小元素的聚合函数，所以我不能使用“GROUP BY”。更具体地说，如果我有一个接受第二个参数“offset”的神奇 MIN() 聚合，我会写:

SELECT MIN(expiry, 1) FROM table WHERE date IN ('2010-01-01', '2010-02-01') GROUP BY date

有什么建议吗？

Answer 1

一个技巧是使用 group_concat。按日期分组并按升序连接到期日期，并使用 substring_index 函数获取第 n 个值。

mysql> select * from expiry;
+------------+------------+
| date       | expiry     |
+------------+------------+
| 2010-01-01 | 2010-02-01 |
| 2010-01-01 | 2010-03-02 |
| 2010-01-01 | 2010-04-04 |
| 2010-02-01 | 2010-03-01 |
| 2010-02-01 | 2010-04-02 |
+------------+------------+
5 rows in set (0.00 sec)

mysql> SELECT mdate,
       Substring_index(Substring_index(edate, ',', 2), ',', -1) AS exp_date
FROM   (SELECT `date`               AS mdate,
               GROUP_CONCAT(expiry order by expiry asc separator ",") AS edate
        FROM   expiry
        GROUP  BY mdate) e1;  
+------------+------------+
| mdate      | exp_date   |
+------------+------------+
| 2010-01-01 | 2010-03-02 |
| 2010-02-01 | 2010-04-02 |
+------------+------------+
2 rows in set (0.00 sec)

在此处的示例中，子查询给出以下输出:

+------------+----------------------------------+
| mdate      | edate                            |
+------------+----------------------------------+
| 2010-01-01 | 2010-02-01,2010-03-02,2010-04-04 |
| 2010-02-01 | 2010-03-01,2010-04-02            |
+------------+----------------------------------+

substring_index(edate,',',2) 向前移动 2 个元素(第 n 个元素用 n 替换 2)。

+------------+------------------------------+
| mdate      | substring_index(edate,',',2) |
+------------+------------------------------+
| 2010-01-01 | 2010-02-01,2010-03-02        |
| 2010-02-01 | 2010-03-01,2010-04-02        |
+------------+------------------------------+

我们使用 substring_index(substring_index(edate,',',2),',',-1) 对上述输出运行另一个 substring_index 以仅获取第二个元素(中间结果的最后一个元素)

+------------+------------------------------------------------------+
| mdate      | substring_index(substring_index(edate,',',2),',',-1) |
+------------+------------------------------------------------------+
| 2010-01-01 | 2010-03-02                                           |
| 2010-02-01 | 2010-04-02                                           |
+------------+------------------------------------------------------+

如果要连接的值太多，您可能会用完 group_concat_max_len 值(默认为 1024，但可以设置得更高)。

更新:上面给出的 SQL 将给出第 n 个元素，即使该组的 n 个元素较少。为了避免sql可以修改为:

SELECT mdate,
       IF(cnt >= 2,Substring_index(Substring_index(edate, ',', 2), ',', -1),NULL) AS exp_date
FROM   (SELECT `date`               AS mdate,
               count(expiry) as cnt,
               GROUP_CONCAT(expiry order by expiry asc separator ",") AS edate
        FROM   expiry
        GROUP  BY mdate) e1;