mysql - 如何按列值分组？

假设我有这两张桌子
单位

CompanyId
Name
State
Country

跟踪

Id
CompanyId
Activity
hitDate

现在我可以得到这样的数据了
公司表格

1 Company1 AK USA
2 Company2 TX USA

跟踪表

1 1 'First Page'  2018-01-01
2 1 'Second Page' 2018-01-01
3 2 'First Page'  2018-01-01

现在我想按州和国家把这些桌子连在一起

 SELECT
      Count(*) as TotalCount,
      company.State as State,
      company.Country as Country,
      tracking.Activity as Activity
 FROM tracking
 INNER JOIN companies
    ON tracking.CompanyId=company.CompanyId
   AND tracking.Activity IN ('First Page', 'Second Page')
   AND companies.Country = 'USA'
   AND DATE(tracking.dDateTime) BETWEEN '2018-02-27' AND '2018-02-28'
 GROUP BY  companies.State, companies.Country
 ORDER BY companies.State

所以我写了一个类似于上面的查询，结果如下

Total Count - 1
State - AK
Country - USA
Activity - 'First Page'

Total Count - 1
State - AK
Country - USA
Activity - 'Second Page'

Total Count - 1
State - Tx
Country - USA
Activity - 'First Page'

但是我想得到这样的结果

Total Count - 2
State - AK
Country - USA
Activity - 'First Page, Second Page'

Total Count - 1
State - Tx
Country - USA
Activity - 'First Page'

我需要某种子查询来实现这一点吗？

最佳答案

你要找的是group_concat：

select ...  GROUP_CONCAT(tracking.Activity) as Activity

还可以指定活动内的顺序和分隔符：

GROUP_CONCAT(tracking.Activity ORDER BY tracking.Activity SEPARATOR ', ')

关于mysql - 如何按列值分组？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/49038024/