建表
CREATE TABLE IF NOT EXISTS `player` (
`userID` int(10) NOT NULL COMMENT '用户ID',
`createAreaID` int(10) NOT NULL COMMENT '创建区ID',
`playerID` int(10) NOT NULL COMMENT '角色ID',
`uid` varchar(30) NOT NULL COMMENT '平台账号ID',
`platform` varchar(30) NOT NULL COMMENT '平台',
`name` varchar(20) NOT NULL COMMENT '名字',
`loginData` blob NOT NULL COMMENT '登陆数据',
`data` blob NOT NULL COMMENT '主数据',
PRIMARY KEY (`playerID`),
KEY `name` (`name`),
KEY `userID` (`userID`),
KEY `createAreaID` (`createAreaID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';
角色登陆表
DROP TABLE IF EXISTS `PlayerLogin`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `PlayerLogin` (
`iEventId` bigint(20) DEFAULT NULL,
`dtEventTime` datetime DEFAULT NULL,
`iOpenId` varchar(128) DEFAULT NULL,
`iPId` varchar(128) DEFAULT NULL,
`iClientIp` varchar(48) DEFAULT NULL,
`iWorldId` varchar(20) DEFAULT NULL,
`iDid` varchar(128) DEFAULT NULL,
`iShareId` varchar(128) DEFAULT NULL,
`iLoginState` varchar(10) DEFAULT NULL,
KEY `PlayerLogin_iDid` (`iDid`),
KEY `PlayerLogin_iOpenId` (`iOpenId`),
KEY `PlayerLogin_dtEventTime` (`dtEventTime`),
KEY `PlayerLogin_iPId` (`iPId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
创建角色表
DROP TABLE IF EXISTS `CreatePlayer`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CreatePlayer` (
`iEventId` bigint(20) DEFAULT NULL,
`dtEventTime` datetime DEFAULT NULL,
`iOpenId` varchar(128) DEFAULT NULL,
`iPId` varchar(48) DEFAULT NULL,
`iClientIp` varchar(48) DEFAULT NULL,
`iWorldId` varchar(20) DEFAULT NULL,
`iDid` varchar(128) DEFAULT NULL,
`iShareId` varchar(128) DEFAULT NULL,
`iLoginState` varchar(10) DEFAULT NULL,
KEY `CreatePlayer_iOpenId` (`iOpenId`),
KEY `CreatePlayer_iPId` (`iPId`),
KEY `CreatePlayer_iDid` (`iDid`),
KEY `CreatePlayer_dtEventTime` (`dtEventTime`),
KEY `PlayerLogin_iWorldId` (`iWorldId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
插入数据语句
OCK TABLES `CreatePlayer` WRITE;
/*!40000 ALTER TABLE `CreatePlayer` DISABLE KEYS */;
INSERT INTO `CreatePlayer` VALUES (1489564431386,'2017-03-15 03:53:51','','guest_android','116.104.101.113','2','24a87cd8757ae1dcfb1922c02a5ee48d','2a6fe936-2edc-46c4-a0b6-5743489095f3','0')
/*!40000 ALTER TABLE `CreatePlayer` ENABLE KEYS */;
UNLOCK TABLES;
查询
select count(distinct iDid) as didNum from PlayerLogin
where dtEventTime>="2017-06-06 00:00:00.0" and dtEventTime <"2017-06-07 00:00:00.0"
and iDid in (select distinct iDid from CreatePlayer
where dtEventTime >="2017-06-01 13:00:00.0" and dtEventTime< "2017-06-02 00:00:00.0");