带有limit的mysql JSON_ARRAYAGG或GROUP_CONCAT

Question

在mysql8.0中有没有在JSON_ARRAYAGG或GROUP_CONCAT中进行限制的方法？例如：

WITH season AS (
    select 'DAL'  as team, 4 as wins, 2000 as season UNION
    select 'DAL'  as team, 10 as wins, 2001 as season UNION
    select 'DAL'  as team, 9 as wins, 2002 as season UNION
    select 'GB'  as team, 2 as wins, 2000 as season UNION
    select 'GB'  as team, 3 as wins, 2001 as season UNION
    select 'GB'  as team, 4 as wins, 2002 as season
) SELECT
    team,
    GROUP_CONCAT(wins order by season desc separator '+') wins_str,
    JSON_ARRAYAGG(wins) wins_arr
FROM season
GROUP BY team;

例如，如何才能仅获取上述两个字段中的前5个wins？例如，我希望在BigQuery STRING_AGG中做一些您可以做的事情，它接受一个LIMIT。

Answer 1

最简单的方法是窗口函数：

SELET team,
      GROUP_CONCAT(wins order by season desc separator '+') wins,
      JSON_ARRAYAGG(wins)
from (select s.*,
             row_number() over (partition by team order by seasons desc) as seqnum
      from seasons s
     ) s
where seqnum <= 5
group by team