Mysql按时间日期统计

最新推荐文章于 2023-06-15 16:18:28 发布

咖啡掠地狮

最新推荐文章于 2023-06-15 16:18:28 发布

阅读量458

点赞数

本文链接：https://blog.csdn.net/andwey/article/details/106834854

版权

一、建表语句

CREATE TABLE `house` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '房产名称',
  `type` int(10) NOT NULL DEFAULT '0' COMMENT '1:销售 2:出租',
  `price` int(11) NOT NULL DEFAULT '0' COMMENT '单位:元',
  `images` varchar(1024) NOT NULL DEFAULT '' COMMENT '图片地址',
  `area` int(11) NOT NULL DEFAULT '0',
  `beds` int(11) NOT NULL DEFAULT '0' COMMENT '卧室数量',
  `baths` int(11) NOT NULL DEFAULT '0' COMMENT '卫生间数量',
  `rating` double NOT NULL DEFAULT '0' COMMENT '评级',
  `remarks` varchar(512) NOT NULL DEFAULT '' COMMENT '房产描述',
  `properties` varchar(512) NOT NULL DEFAULT '' COMMENT '属性',
  `floor_plan` varchar(250) NOT NULL DEFAULT '' COMMENT '户型图',
  `tags` varchar(20) NOT NULL DEFAULT '' COMMENT '标签',
  `create_time` date NOT NULL COMMENT '创建时间',
  `city_id` int(11) NOT NULL DEFAULT '0' COMMENT '城市名称',
  `community_id` int(11) NOT NULL DEFAULT '0' COMMENT '小区名称',
  `address` varchar(200) NOT NULL DEFAULT '' COMMENT '房产地址',
  `state` tinyint(1) DEFAULT '1' COMMENT '1-上架 2-下架',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=306 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

二、插入一些数据：

INSERT INTO `house` VALUES ('2', '2西山华府', '1', '600', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200609/1591689017872_139.jpg', '交通方便', '2020-01-12', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '0');
INSERT INTO `house` VALUES ('3', '西山华府3', '1', '700', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200609/1591689053145_677.jpg', '交通方便', '2020-01-23', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('4', '西山华府4', '1', '800', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-01-16', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '0');
INSERT INTO `house` VALUES ('5', '西山华府5', '1', '900', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-01-01', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('6', '西山华府6', '2', '1000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-02-20', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '0');
INSERT INTO `house` VALUES ('7', '西山华7', '1', '1200', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-02-01', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('8', '西山华府8', '1', '1300', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-02-22', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '0');
INSERT INTO `house` VALUES ('9', '西山华9', '2', '5000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-03-12', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('10', '西山华府10', '1', '8000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-03-12', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '0');
INSERT INTO `house` VALUES ('11', '西山华府11', '1', '400', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-04-23', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('12', '西山华府12', '1', '1990', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-05-12', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('13', '西山华府13', '2', '860', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '4', '3', '0', '江景房，依山傍水江景房，依山傍水', '性价比高,靠近地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-05-12', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('19', '骏景花园-倒叙流年', '2', '800', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '170', '3', '2', '0', '时光匆匆，房子', '满两年,高楼层,楼龄新', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-05-28', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('20', '骏景花园-水光失色', '1', '7000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '170', '4', '3', '3', '傲踞山水间,尽享繁华里', '满两年,高楼层,楼龄新,得房率高,户型好', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-05-25', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('21', '车陂飞珑小区', '1', '400', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '100', '3', '2', '0', '靠近地铁站，交通方便，理市中心近', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-06-28', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('22', '车陂飞珑小区', '1', '400', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '100', '3', '2', '0', '靠近地铁站，交通方便，理市中心近', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-06-28', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('23', '甜蜜小区', '1', '1000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '170', '3', '4', '0', '傲踞山水间，享尽繁华里', '满五年,采光好,价格合理,税少,学区房,次顶层', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便,商铺多', '2020-07-28', '1', '3', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('24', '天堂度假村', '1', '1000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '170', '4', '4', '4.7666015625', '热带雨林中心', '满两年,南北通透,高楼层,环境好,楼龄新,带阳台,得房率高,临地铁,户型好,没有遮挡,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-07-29', '1', '3', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('26', '天堂岛', '1', '1800', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '200', '5', '4', '5', '天堂岛的戴安娜', '满五年,满两年,南北通透,高楼层,环境好,楼龄新,带阳台,得房率高,临地铁', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-07-29', '1', '2', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('27', '天堂度假村A区', '1', '3000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '180', '5', '3', '5', '有钱人住的地方', '满两年', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-07-29', '1', '2', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('28', '天堂度假村B区', '1', '4000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '200', '5', '4', '0', '马云住的地方', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-07-29', '1', '2', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('29', '天堂度假村C区', '1', '3000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '190', '5', '4', '0', '马化腾老婆住的地方', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-07-29', '1', '3', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('30', '天堂度假村D区', '1', '5000', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '180', '4', '3', '0', '广州最美的地方', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便', '2020-08-29', '1', '3', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('31', '金碧天御', '2', '1', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '150', '5', '4', '0', '金碧天域城，靠近地铁，商铺环绕', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,楼龄新,带阳台,税少,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '交通方便,美女如云', '2020-09-29', '1', '1', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('32', '裕达华庭', '2', '50', 'http://localhost:8092/upload/imgs/20200608/1591578888615_149.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_308.jpg,http://localhost:8092/upload/imgs/20200608/1591578888619_924.jpg,http://localhost:8092/upload/imgs/20200608/1591578888615_894.jpg,http://localhost:8092/upload/imgs/20200608/1591578888622_390.jpg', '200', '4', '3', '0', '测试', '满五年,得房率高', 'http://localhost:8092/upload/imgs/20200608/1591578916708_736.jpg', '热卖,急售,推荐', '2020-10-08', '0', '0', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('33', '测试赛', '1', '400', 'http://localhost:8092/upload/imgs/20200609/1591670458956_421.jpg,http://localhost:8092/upload/imgs/20200609/1591670459182_473.jpg,http://localhost:8092/upload/imgs/20200609/1591670459177_973.jpg', '130', '4', '2', '0', '测试', '满五年,满两年,得房率高,临地铁', 'http://localhost:8092/upload/imgs/20200609/1591670475531_792.jpg', '急售,转租', '2020-11-09', '0', '0', '新疆维吾尔族自治区,哈密市,巴里坤哈萨克自治县,八婆大道江滨路52号', '1');
INSERT INTO `house` VALUES ('34', '爱情公寓', '1', '4000', 'http://localhost:8092/upload/imgs/20200609/1591688224407_518.jpg,http://localhost:8092/upload/imgs/20200609/1591688224374_102.jpg,http://localhost:8092/upload/imgs/20200609/1591688224407_53.jpg', '200', '4', '2', '0', '我是你永远也得不到的爸爸', '满五年,满两年,南北通透,采光好,高楼层,环境好,价格合理,得房率高,临地铁,学区房,户型好,没有遮挡,次顶层,落地窗,精装修', 'http://localhost:8092/upload/imgs/20200609/1591687297009_152.jpg', '热卖,急售,推荐', '2020-12-09', '0', '0', '广东省,广州市,天河区,车陂南鬼灵路10号', '0');

三、使用navicat进行查询

1、按日查询

SELECT COUNT(*),DATE(create_time) FROM house WHERE YEAR(create_time)='2020' GROUP BY DAY(create_time)

2、按周查询：

SELECT COUNT(*),WEEK(create_time) FROM house WHERE MONTH(create_time) = '1' GROUP BY WEEK(create_time)

周一到周日查询

-- 周一到周日统计
SELECT COUNT(*),DAYNAME(create_time) FROM house WHERE YEAR(create_time) = '2020' GROUP BY DAYNAME(create_time)

查询本周

-- 本周的统计
SELECT COUNT(*) FROM house WHERE MONTH(create_time) =MONTH(CURDATE()) AND WEEK(create_time) = WEEK(CURDATE())

3、按月、年查询：

-- 按月统计
SELECT COUNT(*),MONTH(create_time) FROM house WHERE YEAR(create_time) = '2020' GROUP BY MONTH(create_time) 

-- 统计本月
SELECT COUNT(*) FROM house WHERE MONTH(create_time) =MONTH(CURDATE()) AND YEAR(create_time) = YEAR(CURDATE())

-- 按季度统计
SELECT COUNT(*),QUARTER(create_time) FROM house WHERE YEAR(create_time) = '2020' GROUP BY QUARTER(create_time) 

-- 按年统计

SELECT COUNT(*),YEAR(create_time) FROM house  GROUP BY YEAR(create_time)

按月统计的另一种方式：

SELECT
	count(*),
	DATE_FORMAT(create_time, '%Y-%m')
FROM
	house
GROUP BY
	DATE_FORMAT(create_time, '%Y-%m');

记录一下，有需要的时候过来搬运！

咖啡掠地狮

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql按时间日期统计

一、建表语句CREATE TABLE `house` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '房产名称', `type` int(10) NOT NULL DEFAULT '0' COMMENT '1:销售 2:出租', `price` int(11) NOT NULL DEFAULT '0' COMMENT '单位
复制链接

扫一扫