MariaDB -组中的聚合计数

8fsztsew 于 6个月前发布在其他

关注(0)|答案(1)|浏览(52)

假设有一张table：

first   second  third
1       1       1
1       1       1
1       2       2
1       1       2
2       3       1

字符串
通过查询：

SELECT first, count(*) AS cnt
FROM table
GROUP BY first

型
我们得到的结果是：

first   cnt
1       4
2       1

型
如何计算组内另一列（列）的计数？预期结果：

first   cnt aggregated_count_by_second  aggregated_count_by_third
1       4   {1: 3, 2: 1}                {1: 2, 2: 2}
2       1   {3: 1}                      {1: 1}

型
数据库版本：10.3.39-MariaDB-0+ deb 10 u1

mariadb

来源：https://stackoverflow.com/questions/77432458/mariadb-aggregated-count-in-group

1条答案

按热度按时间

ldfqzlk81#

如果你改变了，请再问一个问题。
对于原来的问题，你可以这样做：

select 
  first, 
  sum(c) as cnt, 
  concat('{', group_concat(concat(second ,':', c )), '}') as aggregated_count_by_second
from (
  select first, second, count(second) as c
  from tableA
  group by first, second
) AS q
group by first;

字符串
参见dbfiddle。
第二个快速版本：

select first, cnt, max(aggregated_count_by_second), max(aggregated_count_by_third)
from (
select 
  first, 
  sum(s) as cnt, 
  concat('{', group_concat(concat(second ,':', s )), '}') as aggregated_count_by_second,
  null as aggregated_count_by_third
from (
  select first, second, count(second) as s
  from tableA
  group by first, second
) AS q
group by first
union
select 
  first, 
  sum(t) as cnt, 
  null,
  concat('{', group_concat(concat(third ,':', t )), '}') 
from (
  select first, third, count(third) as t
  from tableA
  group by first, third
) AS q
group by first
) as q1
group by first, cnt

型
参见dbfiddle

赞(0）回复(0）举报 6个月前

我来回答

MariaDB -组中的聚合计数

1条答案

相关问题

热门标签

最新问答