MySQL合并脚本

Question

我想问一下我有没有像下面这样的桌子

Date & Hour               Phone           Status
2012-03-06 16:33:12     0292561234  Congestion
2012-03-06 16:33:12     0292561234  Congestion
2012-03-06 16:34:45     0292561234  Congestion
2012-03-06 16:46:50     0292561234  Congestion
2012-03-06 17:17:01     0292561234  Dial
2012-03-07 12:01:39     500         Queue
2012-03-07 12:02:10     500         Queue
2012-03-07 12:03:06     500         Queue

我需要像这样加入他们

Date & Hour               Phone           Status
2012-03-06 16       0292561234  Congestion,Congestion,Congestion
2012-03-06 17       0292561234  Dial
2012-03-07 12       500         Queue,Queue,Queue

所以我在中使用了CONCAT条件

SELECT calldate, dst, GROUP_CONCAT( lastapp )
FROM cdr
GROUP BY hour( calldate ) ;

但它显示的是

Date & Hour               Phone           Status
2012-03-06 16:33:12     0292561234  Congestion,Congestion,Congestion,Dial  
2012-03-06 17:17:01     0292561234  Dial
2012-03-07 12:01:39     500         Queue,Queue,Queue

我是不是用错了脚本？

请注意。

谢谢,

Answer 1

尝试此查询

SELECT DATE(calldate), dst, GROUP_CONCAT(lastapp)
FROM tbl 
GROUP BY dst, DATE(calldate);

参考http://www.mysqlperformanceblog.com/2006/09/04/group_concat-useful-group-by-extension/

Answer 2

请在下面进行尝试：

SELECT SUBSTR(calldate,1,13) as date_hour, dst, GROUP_CONCAT( lastapp )
FROM table
GROUP BY date_hour

Answer 3

这也是可行的。

SELECT DATE_FORMAT(calldate,"%Y-%m-%d %h"), dst, GROUP_CONCAT( lastapp )
FROM cdr
GROUP BY hour( calldate ) ;