sql：建表，插入，查询语句

建表

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");