分享
三行代码  ›  专栏  ›  技术社区  ›  David542

mysql JSON_ARRAYAGG或GROUP_CONCAT with limit

  •  0
  • David542  · 技术社区  · 2 天前

    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

    1 回复  |  直到 2 天前
        1
  •  1
  •   Gordon Linoff    2 天前

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

    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