# # table structure
` ` `
# area table
The CREATE TABLE ` sys_region ` (
` id ` bigint (20) unsigned NOT NULL COMMENT 'id primary key division code,
` name ` varchar (32) DEFAULT NULL COMMENT 'names, such as Beijing, Shanghai, guangzhou,
` ad_name ` varchar (32) DEFAULT NULL COMMENT 'environmental administrative unit name, such as the department of environmental protection of guangdong province, guangzhou environmental protection bureau',
Unsigned ` level ` int (3) the DEFAULT NULL COMMENT 'rank level 1:2 provinces: city 3: area',
` parent_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'superior id',
` gmt_create ` datetime DEFAULT NULL COMMENT 'creation time,
The PRIMARY KEY (` id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='administrative division table;
Table # enterprise
The CREATE TABLE ` cus_enterprise ` (
` id ` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key primary key id,
` region_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'divisions corresponding sys_region id primary key id,
` industry_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'corresponding sys_industry primary key id,
` name ` varchar (32) NOT NULL COMMENT 'name enterprise name,
` code ` varchar (64) the DEFAULT NULL COMMENT 'enterprise credit code unified social credit,
` environment_principal ` varchar (32) DEFAULT NULL COMMENT 'head, head of the environmental protection,
` phone ` varchar (32) DEFAULT NULL COMMENT 'contact number',
` control_level ` tinyint (3) the unsigned DEFAULT NULL COMMENT 'the state-controlled, control level control level: 1. 2. The province point, (3) city, 4. Code=CUS_ENTERPRISE_CONTROL_LEVEL county charged with corresponding data dictionary',
` address ` varchar (255) the DEFAULT NULL COMMENT 'address companies',
`; ` varchar (1024) the DEFAULT NULL COMMENT 'introduction enterprise introduction,
Unsigned ` is_deleted ` tinyint (3) the DEFAULT '0' COMMENT 'whether delete is 0 or 1,
` gmt_create ` datetime DEFAULT NULL COMMENT 'creation time,
` gmt_modified ` datetime DEFAULT NULL COMMENT 'update time,
` tel ` varchar (14) DEFAULT NULL COMMENT 'mobile number',
PRIMARY KEY (` id `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='a' a ';
# site table
The CREATE TABLE ` cus_point ` (
` id ` bigint (20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key primary key id,
` enterprise_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'corresponding cus_enterprise primary key id,
` group_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'operations group id see sys_group primary key id',
` user_id ` bigint (20) unsigned DEFAULT NULL COMMENT 'id corresponding sys_user, head of the primary key id,
` name ` varchar (32) NOT NULL COMMENT 'name site name,
` mn ` varchar (32) NOT NULL COMMENT 'site mn, mn on'
` type ` tinyint (3) the DEFAULT NULL COMMENT 'monitoring water type gas 31 32',
` pass ` varchar (32) DEFAULT '123456' COMMENT 'site code,
` num ` varchar (32) DEFAULT NULL COMMENT 'number site number,
` protocol_type ` char (3) the DEFAULT NULL COMMENT 'protocol type 0=extension, 5=5 agreement, 17=17 agreement',
Unsigned ` transfer_type ` tinyint (3) the DEFAULT NULL COMMENT 'transport type 1: the wireless transmission, 2: cable transmission',
` address ` varchar (250) the DEFAULT NULL COMMENT 'address site,
` longitude ` decimal (64, 10) DEFAULT NULL COMMENT 'longitude site longitude,
` latitude ` decimal (64, 10) DEFAULT NULL COMMENT 'site latitude latitude,
` remark ` varchar (1024) the DEFAULT NULL COMMENT 'site instructions,
` divisor_count ` int (11) unsigned DEFAULT '0' COMMENT 'monitoring factor number',
Unsigned ` is_deleted ` tinyint (3) the DEFAULT '0' COMMENT 'whether delete is 0 or 1,
` gmt_create ` datetime DEFAULT NULL COMMENT 'creation time,
` gmt_modified ` datetime DEFAULT NULL COMMENT 'update time,
` gmt_stop ` datetime DEFAULT NULL,
` stop_reason ` varchar (1024) the DEFAULT NULL,
Unsigned ` is_started ` tinyint (11) the NOT NULL DEFAULT '1' COMMENT 'whether to enable 1: enabling 0: stop using the DEFAULT value is 1',
PRIMARY KEY (` id `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='site management table;
` ` `
Data
` ` `
INSERT INTO ` cus_enterprise ` VALUES (' 51 ', '110102', '2', '0319', 'SHBYKJ199D', 'xiao wu', '18117541115', '2', 'Shanghai fengxian town of occurrence of the shanghai-hangzhou highway no. 1539', 'no', '0', '2021-03-19 14:44:20, null,' 021-8888888 ');
INSERT INTO ` cus_point ` VALUES (' 65 ', '51, null, null,' treasure the test site outfalls', '12345 mn1', '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 ', 'Shanghai', null, '1', null, 'the 2020-08-11 09:30:40');
INSERT INTO ` sys_region ` VALUES (' 310120 ', 'district, null,' 3 ', '310100', '2020-08-11 09:30:40');
INSERT INTO ` sys_region ` VALUES (' 310100 ', 'municipal district, null,' 2 ', '310000', '2020-08-11 09:30:40');
` ` `
# # how can I get?????? The following
! [insert picture description here] (https://img-blog.csdnimg.cn/20210511144442680.png)
| installation quantity
Shanghai 123
Beijing 235
Hunan 212
.
CodePudding user response:
with recursive t1 (name, id, parent_id _connect_by_root) as (
Select t0. Name, t0. Id, t0 the parent_id, t0. Id from sys_region t0 the where t0. The parent_id is null
Union all
Select t2. The name, t2. Id, t2. The parent_id, t1. _connect_by_root
The from sys_region t2,
T1 t1
Where t2. The parent_id=t1. Id
)
Select a t1 _connect_by_root,
Count (1) the cot
The from t1 t1,
Cus_enterprise t2,
Cus_point t3
Where a t1. Id=t2. Region_id
And t2. Id=t3. Enterprise_id
null