表
date flag
2015-12-12 23:48:54.000 1
2015-12-12 23:51:02.000 2
2015-12-12 23:52:16.000 4
2015-12-13 00:18:24.000 6
2015-12-13 00:21:16.000 7
要达到的效果如下:
2015-12-12 1,2,4
2015-12-13 6,7
=====================
假定flag字段是数字类型
select
CONVERT(VARCHAR(100), date, 23),
flags=stuff((select ','+convert(varchar(10),flag) from test t where CONVERT(VARCHAR(100), date, 23)=CONVERT(VARCHAR(100),test.date,23) for xml path('')), 1, 1, '')
from test
group by CONVERT(VARCHAR(100), date, 23)
SELECT DATE_FORMAT(date,'%Y-%m-%d'), GROUP_CONCAT(flag) FROM test GROUP BY DATE_FORMAT(date,'%Y-%m-%d')
不同的数据库sql可能不一样,这是mysql的