CREATE TABLE `tt` (
`id` bigint(20) NOT NULL DEFAULT '0',
`userid` varchar(10) DEFAULT NULL,
`orderid` varchar(10) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tt` VALUES ('1', '11sjd83', '22', '2017-06-13 13:33:02');
INSERT INTO `tt` VALUES ('2', '11sjd83', '33', '2017-06-09 13:33:26');
INSERT INTO `tt` VALUES ('3', '11sjd83', '44', '2017-06-23 13:33:34');
INSERT INTO `tt` VALUES ('4', '23sdfsd', '66', '2017-06-09 16:47:09');
INSERT INTO `tt` VALUES ('5', '23sdfsd', '88', '2017-06-09 16:47:09');

 

如下图所示效果:(当前时间是2017年7月)

 

需求如下:

1,查询出上月有过重复购买动作的用户id?(得到的效果是用户id对应一个orderid数组和时间time数组)

SELECT
userid,
group_concat
(orderid), group_concat(time) FROM `tt` WHERE time >='2017-04-01 00:00:00' and time<'2017-07-01 00:00:00' GROUP BY userid HAVING COUNT(userid) > 1

2,效果图