SQL汇总显示各个省包所含的城市及县区 |
您所在的位置：网站首页 › 厦门各区病例数量排名查询 › SQL汇总显示各个省包所含的城市及县区 |
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这样就查询出来每个市有几个区。 之前接触过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 |
【本文地址】
今日新闻 |
推荐新闻 |