56,679
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `recharge` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) DEFAULT NULL,
`dt` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of recharge
-- ----------------------------
INSERT INTO `recharge` VALUES ('1', '10', '2017-03-20');
INSERT INTO `recharge` VALUES ('2', '11', '2017-03-20');
INSERT INTO `recharge` VALUES ('3', '10', '2017-03-21');
INSERT INTO `recharge` VALUES ('12', '10', '2017-03-21');
INSERT INTO `recharge` VALUES ('4', '12', '2017-03-21');
INSERT INTO `recharge` VALUES ('10', '13', '2017-03-21');
INSERT INTO `recharge` VALUES ('11', '14', '2017-03-21');
INSERT INTO `recharge` VALUES ('9', '15', '2017-03-21');
INSERT INTO `recharge` VALUES ('5', '10', '2017-03-22');
INSERT INTO `recharge` VALUES ('6', '14', '2017-03-22');
INSERT INTO `recharge` VALUES ('7', '15', '2017-03-22');
INSERT INTO `recharge` VALUES ('8', '11', '2017-03-22');
SELECT t1.dt,
(select COUNT(1) as 人数 from (
select uid,dt,COUNT(DISTINCT id) as 人数
from recharge GROUP BY uid,dt )t
where t.人数 = 2 and t.dt = t1.dt)as 人数
FROM (SELECT DISTINCT dt from recharge)t1
select uid,dt,COUNT(DISTINCT uid) as num from recharge group by uid,dt #t1代表 每天谁去过
select DISTINCT dt from recharge #t2代表 一共多少天
#简化了点
select dt,(SELECT COUNT(DISTINCT uid) from (select t1.uid,t2.dt,COUNT(1) as 人数 from (
select uid,dt,COUNT(DISTINCT uid) as 人数 from recharge GROUP BY uid,dt
)t1 join (select DISTINCT dt from recharge)t2 on t1.dt<=t2.dt
GROUP BY t1.uid,t2.dt)t4 where t4.dt = t3.dt and t4.人数=2) as 人数 from (select DISTINCT dt from recharge)t3
select dt,(SELECT COUNT(DISTINCT uid) from (select t1.uid,t2.dt,COUNT(1) as 人数 from (SELECT * from(
select uid,dt,COUNT(DISTINCT uid) as 人数
from recharge GROUP BY uid,dt)t
)t1 join (select DISTINCT dt from recharge)t2 on t1.dt<=t2.dt
GROUP BY t1.uid,t2.dt)t4 where t4.dt = t3.dt and t4.人数=2) as 人数 from (select DISTINCT dt from recharge)t3