mysql - 将多行数据放入mysql中的列中

Question

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total), 
  GROUP_CONCAT(SP_ID)
FROM
(
    SELECT 
      DATE_FORMAT(l.act_date, '%d') AS DAY, 
      DATE_FORMAT(l.act_date, '%M') AS MONTH,
      EXTRACT(YEAR FROM l.act_date) AS YEAR, 
      COUNT(*) as total,l.sp_id 
    FROM lead_activity2 as l
    right outer join salesperson as s on l.sp_id=s.sp_id
    WHERE l.act_name='scb' 
      AND ((l.act_date>='2012-09-07 13:03:27' ) 
      AND (l.act_date<= '2012-11-07 13:03:27')) 
    GROUP BY MONTH, YEAR, DAY, l.sp_id 
    ORDER BY YEAR DESC, MONTH DESC, DAY DESC, l.sp_id DESC
) t GROUP BY day, month, year

http://sqlfiddle.com/#!2/1514d/3 - 您可以查看方案和查询，

我想得到的是

18 | october | 2012 | 0,0,1,1 | 6,5,4,3

spid 6 和 spid 5 没有 10 月 18 日的数据，但仍应显示尝试执行右连接和右外连接似乎都不起作用...

Answer 1

使用GROUP_CONCAT像这样:

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total), 
  GROUP_CONCAT(SP_ID)
FROM
(
    SELECT 
      DATE_FORMAT(l.act_date, '%d') AS DAY, 
      DATE_FORMAT(l.act_date, '%M') AS MONTH,
      EXTRACT(YEAR FROM l.act_date) AS YEAR, 
      COUNT(*) as total,l.sp_id 
    FROM lead_activity2 as l 
    WHERE l.act_name='scb' 
      AND ((l.act_date>='2012-09-07 13:03:27' ) 
      AND (l.act_date<= '2012-11-07 13:03:27')) 
    GROUP BY MONTH, YEAR, DAY, l.sp_id 
    ORDER BY YEAR DESC, MONTH DESC, DAY DESC, l.sp_id DESC
) t GROUP BY day, month, year

Updated SQL Fiddle

更新:是的，您可以这样做，但使用LEFT JOIN 来包含不匹配的sp_id。这些不匹配的 ID 将具有 NULL 的值，使用 IFNULL 将其显示为零，如下所示:

SELECT 
  Day, 
  month, 
  year,
  GROUP_CONCAT(total) Total, 
  GROUP_CONCAT(SP_ID) 'List of sp_ids'
FROM
(
  SELECT
    DATE_FORMAT(l.act_date, '%d') AS DAY, 
    DATE_FORMAT(l.act_date, '%M') AS MONTH, 
    EXTRACT(YEAR FROM l.act_date) AS YEAR, 
    COUNT(*) as total,
    IFNULL(s.sp_id , 0) sp_id
  FROM lead_activity2 as l 
  LEFT JOIN salesperson s ON l.sp_id = s.sp_id
  WHERE l.act_name='scb' 
    AND ((l.act_date>='2012-09-07 13:03:27' )
    AND (l.act_date<= '2012-11-07 13:03:27')) 
  GROUP BY MONTH, YEAR,DAY,s.sp_id 
) t
ORDER BY YEAR DESC, 
         MONTH DESC, 
         DAY DESC, 
         sp_id DESC

Updates SQL Fiddle Demo