假设我有这两张桌子
公司
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'我需要一些子查询来实现这一点吗?
发布于 2018-03-01 04:40:41
你在找group_concat
select ... GROUP_CONCAT(tracking.Activity) as Activity您还可以指定活动中的顺序和分隔符:
GROUP_CONCAT(tracking.Activity ORDER BY tracking.Activity SEPARATOR ', ')https://stackoverflow.com/questions/49038024
复制相似问题