SQL汇总显示各个省包所含的城市及县区

要点：1.pid和id的关系，省市之间的从属关系，可以引申为客户发展下线的统计（听着像传销）。

          2. MySQL和PostresSQL之间不能通用的部分

          3. group by 和 group_concat

          4.with RECURSIVE cte as 的理解

SQL交流群里面有个人提了个问题：

怎么查询会员表中 下级会员 个数大于10的会员？

会员表A
字段  userid(会员ID),NAME(会员名称),suID(会员上级ID)

同一个会员发展的下线会员有同样的suid，以上级的id分组，having出>10，内连接

子查询的suid就是下级会员数大于10的人的userid，为了找出这些人的name需要再连接原表

select users.userid, users.username, t.sumid
from (select suid ,count(suid) as sumid 
      from users  
      group by suid  
      having count(suid) >10) as t 
      INNER JOIN 
      users ON t.suid=users.userid

我又想到了我之前做的一个递归的查询，统计各省及其市和区。有类似的感觉，都是父系id关系。

with RECURSIVE cte as
(
select id,cast(name as varchar(100)) from tb  where id='002'
union all 
select k.id,cast(c.name||'>'||k.name as varchar(100)) as name  from tb k inner join cte c on c.id = k.pid
)select id,name from cte ;

首先梳理下我这个查询的意思。

扩展到我这个tb表，就是查询每个省下面有几个市？每个市有多少个区？

tb表中id是主键，pid是父id。省的pid=0，市的pid=所在省，区的pid=所在市。

每个省有多少个市？（原表是示例表，不全）

SELECT a.name, COUNT(tb.pid) as "城市"
FROM    (SELECT *
	FROM tb
	WHERE id IN ('001', '002')) a
	INNER JOIN 
	tb
	ON a.id = tb.pid
GROUP BY a.name
/*
name	城市
浙江省	11
广东省	2
*/

3. 想统计出来每个市有多少个区？

这里面应该需要用到递归，因为储存的机制是

SELECT *
FROM ((select id as ids,cast(name as varchar(100)) as names from tb  where id='002') a
		INNER JOIN
		tb ON tb.pid = a.ids) c
		INNER JOIN 
		tb as b ON b.pid = c.id

WITH ojbk as (
SELECT names as prov, c.name as city, b.name as qu 
FROM ((select id as ids,cast(name as varchar(100)) as names from tb  where id='002') a
			INNER JOIN
			tb ON tb.pid = a.ids) c
			INNER JOIN 
			tb as b ON b.pid = c.id
)
SELECT city, COUNT(qu) as numqu
FROM ojbk
GROUP BY city

这样就查询出来每个市有几个区。

之前接触过MySQL里面的一个函数，GROUP_CONCAT能一次性展示分组内的东西。可惜不是标准SQL的语法，所以在MySQL里面再来一次。

create table tb(id varchar(3) , pid varchar(3) , named varchar(10)); 

insert into tb values('002' , 0 , '浙江省'); 
insert into tb values('001' , 0 , '广东省'); 
insert into tb values('003' , '002' , '衢州市');  
insert into tb values('004' , '002' , '杭州市') ; 
insert into tb values('005' , '002' , '湖州市');  
insert into tb values('006' , '002' , '嘉兴市') ; 
insert into tb values('007' , '002' , '宁波市');  
insert into tb values('008' , '002' , '绍兴市') ; 
insert into tb values('009' , '002' , '台州市');  
insert into tb values('010' , '002' , '温州市') ; 
insert into tb values('011' , '002' , '丽水市');  
insert into tb values('012' , '002' , '金华市') ; 
insert into tb values('013' , '002' , '舟山市');  
insert into tb values('014' , '004' , '上城区') ; 
insert into tb values('015' , '004' , '下城区');  
insert into tb values('016' , '004' , '拱墅区') ; 
insert into tb values('017' , '004' , '余杭区') ; 
insert into tb values('018' , '011' , '金东区') ; 
insert into tb values('019' , '001' , '广州市') ; 
insert into tb values('020' , '001' , '深圳市') ;

但是可惜MySQL也不支持CTE公用表表达式。只能创建临时表

-- mysql不支持with，要创建临时表
DROP TEMPORARY TABLE IF EXISTS tmp_table;

CREATE TEMPORARY TABLE tmp_table (
     prov VARCHAR(10) NOT NULL,
     city VARCHAR(10) NOT NULL,
     qu VARCHAR(10) NOT NULL
  );
INSERT INTO tmp_table
(
SELECT namedd as prov, tb.named as city, b.named as qu 
FROM (select id as ids, named as namedd from tb  where id='002') a
		INNER JOIN
		tb ON tb.pid = a.ids
		INNER JOIN 
		tb as b ON b.pid = tb.id
);

SELECT city, GROUP_CONCAT(qu)
FROM tmp_table
GROUP BY city;

之前做的递归有些忘记了，这一次加深了下理解。

递归里面的union all 像俄罗斯方块一样垒起来。 

觉得总有些地方做的不够完善，想把全国的省市做一下。

============================================================================

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for ims_shop_region
-- ----------------------------
DROP TABLE IF EXISTS `ims_shop_region`;
CREATE TABLE `ims_shop_region` (
  `region_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '父级id',
  `region_name` varchar(120) NOT NULL DEFAULT '' COMMENT '地域名',
  `region_type` tinyint(1) NOT NULL DEFAULT '2' COMMENT '地域级别：1省，2市，3区',
  `agency_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`region_id`),
  KEY `parent_id` (`parent_id`) USING BTREE,
  KEY `region_type` (`region_type`) USING BTREE,
  KEY `agency_id` (`agency_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3409 DEFAULT CHARSET=utf8 COMMENT='全国地区表';

-- ----------------------------
-- Records of ims_shop_region
-- ----------------------------
INSERT INTO `ims_shop_region` VALUES ('1', '0', '中国', '0', '0');
INSERT INTO `ims_shop_region` VALUES ('2', '1', '北京', '1', '0');
INSERT INTO `ims_shop_region` VALUES ('3', '1', '安徽', '1', '0');
......

开始做的过程中遇到了一些问题，想根据ims_shop_region表复现轨迹 
但是存在一个问题就是创建ims_shop_region这个表的语法的mysql的，mysql中没有with
尝试做一下国家联动，省市联动，省市区联动。
这里面能做的很多，可以拿type来限制，省市区。

而且用两个group by 可以搞出来全表查询，因为只有三个等级划分。

-- 找到一个省的所有市

SELECT a.region_name as '省', b.region_name as '市'
FROM ims_shop_region a
		 LEFT JOIN 
		 ims_shop_region b
		 ON a.region_id = b.parent_id
WHERE a.region_name = '河南'

-- 找到每个省的所有市，两种表达方式，一种是竖着，一种是横着。

-- 横着GROUP_CONCAT(expr)

通过改变type可以变更查找省市关系还是市县关系
-- 竖着
-- 这里group by 用了两次，就好像一个星期中的每一天，要先定tyoe为1，这样等级才对啊，区就不展示了，用count显示下数值就行.注意要多引用一次原表

SELECT a.region_name as '省', b.region_name as '市', COUNT(*) as numqu
FROM ims_shop_region a
		 LEFT JOIN 
		 ims_shop_region b
		 ON a.region_id = b.parent_id
		 INNER JOIN
		 ims_shop_region c
		 ON b.region_id = c.parent_id
WHERE a.region_type = 1
GROUP BY a.region_name, b.region_name

-- 全部省市区
SELECT a.region_name as '省', b.region_name as '市', c.region_name as '区/县'
FROM ims_shop_region a
		 LEFT JOIN 
		 ims_shop_region b
		 ON a.region_id = b.parent_id
		 INNER JOIN
		 ims_shop_region c
		 ON b.region_id = c.parent_id
WHERE a.region_type = 1
GROUP BY a.region_name, b.region_name, c.region_name