mysql统计数据条数

mysql 统计数据条数

CREATE TABLE `c_family_reunion_dinner_activity_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间(创建时间)',
  `province` varchar(50) DEFAULT NULL COMMENT '省份',
  `city` varchar(50) DEFAULT NULL COMMENT '城市',
  `create_id` varchar(64) DEFAULT NULL COMMENT '创建人',
  `type` varchar(50) DEFAULT NULL COMMENT '1:点赞 2:上传作品 3:抽奖 4:中奖',
  `channel` varchar(50) DEFAULT NULL COMMENT '',
  `exet` varchar(50) DEFAULT NULL COMMENT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COMMENT='数据统计表';

	SELECT
	province as province,
	city as city,
	count(1) as pv,
	count(DISTINCT(create_id)) AS uv,
	DATE_FORMAT(create_time,'%Y-%m-%d') as createTime,
	if(type=1,type,0 ) as thumbsUpPer,
	if(type=2,type,0 ) as upWorksPer,
	if(type=3,type,0 ) as luchDrawPer,
	if(type=4,type,0 ) as winThePrizePer,
	channel as channel,
	exet as exet
	FROM
	c_family_reunion_dinner_activity_count
	
	WHERE create_time between '2022-01-19 00:00:00' AND '2022-01-19 23:59:59'
	GROUP BY city,createTime
	ORDER BY create_time DESC