数据表两层分组拼串
【问题】
I have one table called’activity_data’,table contain following fields
id int(11),
sports_name varchar(20),
activity_date datetime
information ofactivity_datatable
id sports_name activity_date
1 Cricket 2015-05-12
2 Football 2015-05-12
3 Cricket 2015-06-10
4 Basketball 2015-06-08
5 Khokho 2015-06-02
6 Kubbdi 2015-06-04
7 Cricket 2015-05-12
8 Kubbdi 2015-05-12
9 Cricket 2015-06-09
10 Football 2015-06-03
now I’m execute following queries
SELECT GROUP\_CONCAT(id) id ,GROUP\_CONCAT(DISTINCT(sports\_name)) sports\_name,activity_date
FROM filter\_activity\_data
WHERE activity_date BETWEEN '2015-05-12' AND '2015-06-04'
GROUP BY activity_date
ORDER BY activity_date ASC
while executing this:output is getting like this
id sports_name activity_date
1,2,7,8 Cricket,Football,Kubbdi 2015-05-12
5 Khokho 2015-06-02
10 Football 2015-06-03
6 Kubbdi 2015-06-04
… …
I want to get output total count of eachGROUP_CONCAT sports_name. expected out put like this:
id sports_name activity_date
2,1,1 Cricket,Football,Kubbdi 2015-05-12
1 Khokho 2015-06-02
1 Football 2015-06-03
1 Kubbdi 2015-06-04
… …
【回答】
SQL 写法:
select GROUP_CONCAT(t.name1) as name2 , GROUP_CONCAT(t.count) as counts , t.activity_date from
(SELECT GROUP\_CONCAT(id) id ,GROUP\_CONCAT(DISTINCT(sports_name)) as name1 , count(*) as count , sports\_name,activity\_date
FROM activity_data
WHERE activity_date BETWEEN '2015-05-12' AND '2015-06-04'
GROUP BY activity\_date, sports\_name
ORDER BY activity_date ASC) as t
group by t.activity_date
除了可以通过上面的方法实现,还可以使用 SPL 实现,并且 SPL 相较 SQL 更加简洁,实现思路与 SQL 是一样的,写法如下:
A |
|
1 |
$select * from activity_data |
2 |
=A1.groups(activity_date,sports_name;count(~):c) |
3 |
=A2.group@o(activity_date).new(~.(c).concat@c():id,~.(sports_name).concat@c():sports_name,activity_date) |
A1:执行 SQL 取数
A2:对序列成员进行分组计数
A3:将分组后的序列进行归并运算,A3 执行结果如下: