Czy istnieje sposób w MySQL 8.0, aby zrobić limit w JSON_ARRAYAGG i GROUP_CONCAT? Na przykład:

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;

Na przykład, w jaki sposób otrzymałem tylko pierwsze 5 wins w dwóch obszarach? Na przykład, chcąc zrobić coś takiego jak możesz zrobić w Bigquery STRING_AGG, gdzie akceptuje LIMIT.

0
David542 22 listopad 2020, 04:53

1 odpowiedź

Najlepsza odpowiedź

Najprostszą metodą jest funkcje okien:

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
1
Gordon Linoff 22 listopad 2020, 01:56