【求助】mysql四表统计数量:统计中国各个省份安装企业站点数量

企业官方账号

2021-05-12 03:22:37

## 表结构

```
# 地区表
CREATE TABLE `sys_region` (
`id` bigint(20) unsigned NOT NULL COMMENT 'id 主键区划代码',
`name` varchar(32) DEFAULT NULL COMMENT '名称,如北京,上海,广州',
`ad_name` varchar(32) DEFAULT NULL COMMENT '环保行政单位名称,如广东环保厅,广州环保局',
`level` int(3) unsigned DEFAULT NULL COMMENT '等级级别 1：省份 2：城市 3：区域',
`parent_id` bigint(20) unsigned DEFAULT NULL COMMENT '上级id',
`gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行政区划表';

# 企业表
CREATE TABLE `cus_enterprise` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键主键id',
`region_id` bigint(20) unsigned DEFAULT NULL COMMENT '区划id 对应sys_region主键id',
`industry_id` bigint(20) unsigned DEFAULT NULL COMMENT '对应sys_industry主键id',
`name` varchar(32) NOT NULL COMMENT '名称企业名称',
`code` varchar(64) DEFAULT NULL COMMENT '企业信用代码统一社会信用代码',
`environment_principal` varchar(32) DEFAULT NULL COMMENT '负责人环保负责人',
`phone` varchar(32) DEFAULT NULL COMMENT '联系电话',
`control_level` tinyint(3) unsigned DEFAULT NULL COMMENT '控制级别控制级别：1.国控，2.省控，3.市控，4.县控对应数据字典code=CUS_ENTERPRISE_CONTROL_LEVEL',
`address` varchar(255) DEFAULT NULL COMMENT '地址企业地址',
`introduction` varchar(1024) DEFAULT NULL COMMENT '简介企业简介',
`is_deleted` tinyint(3) unsigned DEFAULT '0' COMMENT '是否删除 0否1是',
`gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
`gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
`tel` varchar(14) DEFAULT NULL COMMENT '手机号码',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='一企一档';

# 站点表
CREATE TABLE `cus_point` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键主键id',
`enterprise_id` bigint(20) unsigned DEFAULT NULL COMMENT '对应cus_enterprise主键id',
`group_id` bigint(20) unsigned DEFAULT NULL COMMENT '运维小组id 参见sys_group主键id',
`user_id` bigint(20) unsigned DEFAULT NULL COMMENT '负责人id 对应sys_user主键id',
`name` varchar(32) NOT NULL COMMENT '名称站点名称',
`mn` varchar(32) NOT NULL COMMENT 'MN号站点MN号',
`type` tinyint(3) DEFAULT NULL COMMENT '监测类型31气 32水',
`pass` varchar(32) DEFAULT '123456' COMMENT '站点密码',
`num` varchar(32) DEFAULT NULL COMMENT '编号站点编号',
`protocol_type` char(3) DEFAULT NULL COMMENT '协议类型 0=扩展协议、05=05协议、17=17协议',
`transfer_type` tinyint(3) unsigned DEFAULT NULL COMMENT '传输类型 1:无线传输、2:有线传输',
`address` varchar(250) DEFAULT NULL COMMENT '地址站点地址',
`longitude` decimal(64,10) DEFAULT NULL COMMENT '经度站点经度',
`latitude` decimal(64,10) DEFAULT NULL COMMENT '纬度站点纬度',
`remark` varchar(1024) DEFAULT NULL COMMENT '站点说明',
`divisor_count` int(11) unsigned DEFAULT '0' COMMENT '监测因子数量',
`is_deleted` tinyint(3) unsigned DEFAULT '0' COMMENT '是否删除 0否1是',
`gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
`gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
`gmt_stop` datetime DEFAULT NULL,
`stop_reason` varchar(1024) DEFAULT NULL,
`is_started` tinyint(11) unsigned NOT NULL DEFAULT '1' COMMENT '是否启用 1:启用 0:停用默认值为1',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='站点管理表';

```

数据

```
INSERT INTO `cus_enterprise` VALUES ('51', '110102', '2', '测试0319', 'SHBYKJ199D', '小吴', '18117541115', '2', '上海市奉贤区南桥镇沪杭公路1539号', '无', '0', '2021-03-19 14:44:20', null, '021-8888888');

INSERT INTO `cus_point` VALUES ('65', '51', null, null, '宝英测试站点排放口', '12345mn1', '31', null, null, '17', '1', null, '121.6336300000', '31.1658110000', null, '2', '0', '2021-04-01 13:39:50', '2021-04-01 13:41:06', null, null, '1');
INSERT INTO `sys_region` VALUES ('310000', '上海市', null, '1', null, '2020-08-11 09:30:40');
INSERT INTO `sys_region` VALUES ('310120', '奉贤区', null, '3', '310100', '2020-08-11 09:30:40');
INSERT INTO `sys_region` VALUES ('310100', '市辖区', null, '2', '310000', '2020-08-11 09:30:40');
```

## 怎么才能得到？？？？如下
![在这里插入图片描述](https://img-blog.csdnimg.cn/20210511144442680.png)

区域 | 安装数量

上海 123

北京 235

湖南 212

......

...全文

235 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

maradona1984 2021-05-13

打赏
举报

还有无论sql还是代码,都是复杂问题分解成简单问题,所以你这个sql,只需要找到公司与省份的关系,所以先把region_id跟省份id关联 select id province_id,id from sys_region where `level` =1 union all select parent_id province_id,id from sys_region where `level` =2 union all select c.parent_id province_id,r.id from sys_region c,sys_region r where r.`level` =3 and r.parent_id =c.id 这样公司表的region_id再跟这个里面的id关联,就能得到公司与省份id的关联 select ce.id,region.province_id from cus_enterprise ce,(select id province_id,id from sys_region where `level` =1 union all select parent_id province_id,id from sys_region where `level` =2 union all select c.parent_id province_id,r.id from sys_region c,sys_region r where r.`level` =3 and r.parent_id =c.id) region where ce.region_id =region.id 最后跟站点关联即可 select count(1),region.province_id from cus_enterprise ce,cus_point cp ,(select id province_id,id from sys_region where `level` =1 union all select parent_id province_id,id from sys_region where `level` =2 union all select c.parent_id province_id,r.id from sys_region c,sys_region r where r.`level` =3 and r.parent_id =c.id) region where ce.region_id =region.id and cp.enterprise_id =ce.id group by region.province_id 最后省份id跟区域表关联就好了有可能有错,这个你可以按照这个思路去解决问题

nayi_224 2021-05-13

打赏
举报

引用 4 楼尽力漂亮的回复:

[quote=引用 1 楼 nayi_224 的回复:]

with recursive t1(name, id, parent_id, _connect_by_root) as(
select t0.name, t0.id, t0.parent_id, t0.id from sys_region t0 where t0.parent_id is null
union all
select t2.name, t2.id, t2.parent_id, t1._connect_by_root
  from sys_region t2,
	   t1 t1
 where t2.parent_id = t1.id
)
select t1._connect_by_root,
	   count(1) cot
  from t1 t1,
	   cus_enterprise t2,
	   cus_point t3
 where t1.id = t2.region_id
   and t2.id = t3.enterprise_id
 group by t1._connect_by_root

我使用的是mysql ，你是写的oracle写法么[/quote] 8的语法，之前的版本没有替代，除非用函数。但是建议你对sys_region表进行一下处理，把根节点写到每一行后面，这个用java写个递归很简单

maradona1984 2021-05-13

打赏
举报

楼上用了with as,5.x的版本应该是不支持的,8.x的mysql貌似支持你完全可以替换得到符合你数据库语法的sql

尽力漂亮 2021-05-13

打赏
举报

引用 1 楼 nayi_224 的回复:

with recursive t1(name, id, parent_id, _connect_by_root) as(
select t0.name, t0.id, t0.parent_id, t0.id from sys_region t0 where t0.parent_id is null
union all
select t2.name, t2.id, t2.parent_id, t1._connect_by_root
  from sys_region t2,
	   t1 t1
 where t2.parent_id = t1.id
)
select t1._connect_by_root,
	   count(1) cot
  from t1 t1,
	   cus_enterprise t2,
	   cus_point t3
 where t1.id = t2.region_id
   and t2.id = t3.enterprise_id
 group by t1._connect_by_root

我使用的是mysql ，你是写的oracle写法么

尽力漂亮 2021-05-13

打赏
举报

引用 1 楼 nayi_224 的回复:

with recursive t1(name, id, parent_id, _connect_by_root) as(
select t0.name, t0.id, t0.parent_id, t0.id from sys_region t0 where t0.parent_id is null
union all
select t2.name, t2.id, t2.parent_id, t1._connect_by_root
  from sys_region t2,
	   t1 t1
 where t2.parent_id = t1.id
)
select t1._connect_by_root,
	   count(1) cot
  from t1 t1,
	   cus_enterprise t2,
	   cus_point t3
 where t1.id = t2.region_id
   and t2.id = t3.enterprise_id
 group by t1._connect_by_root

本人这边执行报错了，还不懂存储过程 [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'with recursive t1(name, id, parent_id, _connect_by_root) as( select t0.name, t0' at line 1

尽力漂亮 2021-05-13

打赏
举报

nayi_224 2021-05-13

打赏
举报

楼上那个是写死的方法，level多了语句会很变态，但是行政代码只有三级，也是个不错的写法。不过单纯考虑你这个实际问题的话，行政代码是有规律的，它的前两位就是代表一级行政区

select t2.name,
	   t1.cot
  from (
select substr(t1.id, 1, 2) iid,
	   count(1) cot
  from sys_region t1,
	   cus_enterprise t2,
	   cus_point t3
 where t1.id = t2.region_id
   and t2.id = t3.enterprise_id
 group by substr(t1.id, 1, 2)
) t1,
(select substr(t1.id, 1, 2) iid, t1.name from sys_region t1 where t1.level = 1) t2
 where t1.iid = t2.iid

但是这个写法也不推荐，缺少通用性。行政编码可以认为是不变的，直接刷表才是正常方法


create table sys_region_ as
select id root_id,t1.* from sys_region t1 where `level` =1
union all
select parent_id province_id, t1.* from sys_region t1 where `level` =2
union all
select c.parent_id province_id,r.* from sys_region c,sys_region r
where r.`level` =3 and r.parent_id =c.id
;

nayi_224 2021-05-12

打赏
举报

with recursive t1(name, id, parent_id, _connect_by_root) as(
select t0.name, t0.id, t0.parent_id, t0.id from sys_region t0 where t0.parent_id is null
union all
select t2.name, t2.id, t2.parent_id, t1._connect_by_root
  from sys_region t2,
	   t1 t1
 where t2.parent_id = t1.id
)
select t1._connect_by_root,
	   count(1) cot
  from t1 t1,
	   cus_enterprise t2,
	   cus_point t3
 where t1.id = t2.region_id
   and t2.id = t3.enterprise_id
 group by t1._connect_by_root