类目中城市，省份可多选情况下数据库设计

需求背景：

在很多场景中常见的情况：选择一些城市，城市是多选，省份是多选，且城市和省份是有层级关系。主要需求是，当选择一个城市范围，获取与该范围有交集的所有返回对象。

例子：选择江苏，得查询数据库总江苏-（南京、扬州）、江苏、江苏-（南京）等。

这个需求中层级只有两层、存在一对多的关系、需要根据ID去查询一棵树。

解决方案：

在sql反模式书中概括了常用解决该方案方法解决：

参考：http://www.cnblogs.com/kissdodog/p/3297894.html

https://www.cnblogs.com/mfrank/p/7992709.html

根据上述需求叙述，该几张方案都可以实现，但相对来说邻接表相对较容易：

数据库表设计：

CREATE TABLE `tree` (
  `id` int(11) unsigned NOT NULL COMMENT 'id',
  `text` varchar(11) DEFAULT NULL COMMENT '地址信息',
  `textId` int(11) DEFAULT NULL COMMENT '地址Id',
  `parentId` int(11) DEFAULT NULL COMMENT '父ID',
  `levels` int(11) DEFAULT NULL COMMENT '国家为1，省份2，城市为3',
  `class` int(11) DEFAULT NULL COMMENT '分类',
  `haveChildren` tinyint(1) DEFAULT NULL COMMENT '是否有子节点',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

简单创建数据：

1.存储的话按照树的层次遍历存储。

2.业务查询时注意带入地址层级：

1输入国内查询

sql：select class from tree where textId = 1 

分别查询1和2和3的类别

结果为：国内、国内-北京、国内-江苏-（南京、扬州）

输入查询：国内-江苏

查询：level 为2 为江苏的 ，level 为1 的国内，且没有孩子的时候

select * from tree join (select class from tree where (levels = 2 and textId = 3) or (levels = 1 and textId = 1 and haveChildren = 0)) as pp on pp.class = tree.class

输入查询为：国内-江苏-南京

转化为：国内，国内-江苏，国内-江苏-南京

select * from tree join
 (select class from tree where (levels = 2 and textId = 3 and haveChildren = 0) 
 or (levels = 1 and textId = 1 and haveChildren = 0)
 or (levels = 3 and textId = 4)
 ) as pp 
 on
 pp.class = tree.class

结果为：

本次解决方案的实现中的解决方案中有一个限制条件：

树的层次不高，所以可以增加一个havechildren来判断是否有孩子。

总结：本次解决方案的实行，主要是为了解决地址类目查询问题，当层级比较深时，这种做法基本很难实现。在不同业务场景中，尽量选择相对简单满足需求的方法解决。缺陷在于无法很快的判断量层级之间的关系。本文实现的方法适用于层级较低的情况。由于本人用的数据库是mysql，在递归查询没有sqlServer方便，所以一般尽量避免递归查询。