MySQL队列查询结果结构

Question

我有以下 MySQL 查询:

SELECT  results.YEAR ,  results.cohort_date ,  results.actives AS     
active_users ,  user_totals.total AS  cohort_size 
FROM (SELECT YEAR(  user.date_added) AS  cohort_date , 
COUNT(DISTINCT  user_log.user_id) AS  actives, 
   ROUND( DATEDIFF( user_log.login_date ,user.date_added)) AS YEAR
FROM  user
JOIN  user_log ON  user_log.user_id = user.id
GROUP BY  cohort_date, YEAR) AS  results
JOIN (SELECT YEAR( user.date_added) AS cohort_date, COUNT( user.id) AS total
FROM user
GROUP BY  cohort_date) AS  user_totals
ON  user_totals.cohort_date =  results.cohort_date

给出以下结构的结果:

  YEAR cohort_date cohort_size  active_users
  [1,  1973,        1000,         750],
  [2,  1973,        1000,         300],
  [3,  1973,        1000,         400],
  [1,  1268,        549,          336],
  [2,  1268,        549,          221],

如何重写查询以使其返回这种格式的数据？

 [1973,  1000,   750,300,400],
 [1268,  549,    336,221],

Answer 1

使用group_concat()函数按组生成逗号分隔的值列表:

SELECT  results.cohort_date ,  user_totals.total AS  cohort_size,  group_concat(results.actives) AS     
active_users  
FROM (SELECT YEAR(  user.date_added) AS  cohort_date , 
COUNT(DISTINCT  user_log.user_id) AS  actives, 
   ROUND( DATEDIFF( user_log.login_date ,user.date_added)) AS YEAR
FROM  user
JOIN  user_log ON  user_log.user_id = user.id
GROUP BY  cohort_date, YEAR) AS  results
JOIN (SELECT YEAR( user.date_added) AS cohort_date, COUNT( user.id) AS total
FROM user
GROUP BY  cohort_date) AS  user_totals
ON  user_totals.cohort_date =  results.cohort_date
GROUP BY results.cohort_date ,  user_totals.total