Is there a way in mysql8.0 to do a limit within JSON_ARRAYAGG or GROUP_CONCAT? For example:
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;
For example, how would I only get the first 5 wins in the above two fields? For example I'm looking to do something like you can do within BigQuery STRING_AGG, where it accepts a LIMIT.
# Answer 1
The simplest method is window functions:
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
where seqnum <= 5
group by team
Is there a way in mysql8.0 to do a limit within JSON_ARRAYAGG or GROUP_CONCAT? For example:WITH season AS (select 'DAL' as team, 4 as wins, 2000 as season UNIONselect 'DAL' as team, 10 as wins, 2001...
文章目录第六章 SQL聚合函数
JSON
_
ARRAY
AGG
大纲参数描述包含转义字符的数据值最大
JSON
数组大小
JSON
_
ARRAY
AGG
和 %SelectMode
JSON
_
ARRAY
AGG
和ORDER BY相关的聚合函数示例
第六章 SQL聚合函数
JSON
_
ARRAY
AGG
创建
JSON
格式值数组的聚合函数。
注：IRIS可用，IRIS之前版本不可用。
JSON
_
ARRAY
AGG
([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-l
mysql
的
json
Array
应用
1.本次项目用到了
mysql
json
Array
用法但是出了一些问题,记录一下,以备日后查看
2.
JSON
ARRAY
相关函数
2.1
JSON
_
ARRAY
函数
生成
json
数组
SELECT
JSON
_
ARRAY
(1, "abc", NULL);
+---------------------------------------------+
|
JSON
_
ARRAY
(1, "abc", NULL) |
+------------------------------
