本文共 7087 字，大约阅读时间需要 23 分钟。
一个很常见的场景，一个文章信息，具有一个地域信息，比如北京，比如上海，比如长沙，有时候，地域信息则更为细致，比如昌平，比如浦东，比如集安，而我们需要统计所有指定地域的信息，比如广东，比如河北，比如四川
而现在，我们有两种处理方式
方法一：物理存储
在我们插入或修改信息时，生成对应信息的冗余数据，比如存储的信息时通州，那么我们在冗余表里记录上通州的ID，同时记录出北京的ID
这个好处是效率较高
方法二：递归生成ID列
比如我们查北京地区，那么不能只查北京ID的，还要把大兴、密云什么的ID也列出来然后去in也好，inner join也好，cross也好，都可以实现，但是相对来说效率较低
假设有地域表cities
city_id city_name city_parent----------- -------------------------------------------------- -----------1 北京 02 天津 03 上海 04 重庆 05 河北 06 山西 07 辽宁 08 吉林 09 黑龙江 010 内蒙古 011 江苏 012 浙江 013 安徽 014 福建 015 江西 016 山东 017 湖北 018 湖南 019 广东 020 海南 021 广西 022 河南 023 四川 024 贵州 025 云南 026 西藏 027 陕西 028 甘肃 029 青海 030 宁夏 031 新疆 032 香港 033 澳门 034 台湾 0213 东城区 1214 西城区 1217 朝阳区 1218 海淀区 1219 丰台区 1220 石景山区 1221 房山区 1222 通州区 1223 顺义区 1224 门头沟区 1225 昌平区 1226 大兴区 1227 怀柔区 1228 平谷区 1229 密云区 1230 延庆区 1232 和平区 2233 河东区 2234 河西区 2235 南开区 2236 河北区 2237 红桥区 2241 东丽区 2242 西青区 2243 北辰区 2244 津南区 2245 武清区 2246 宝坻区 2247 静海区 2248 宁河区 2249 蓟州区 23651 滨海新区 21581 黄浦区 31583 徐汇区 31584 长宁区 31585 静安区 31586 普陀区 31588 虹口区 31589 杨浦区 31590 闵行区 31591 宝山区 31592 嘉定区 31593 浦东新区 31594 金山区 31595 松江区 31596 青浦区 31598 奉贤区 31599 崇明区 31604 渝中区 41605 大渡口区 41606 江北区 4
按照方法二，选择所有北京的文章，语句如下
with t as ( select city_id,city_name,city_parent from cities ),t1 as ( select * from t where city_id=1 union all select a.* from t a,t1 b where a.city_parent=b.city_id)select * from news where city in (select city_id from t1)
而方法一，则需要触发器来进行一下支持
假设有地域查询冗余表news_city(id int identity,city int,newsid int)
那么触发器里应该根据cities表，获得地域及其所有父级id插入到这个冗余表中
然后筛选语句为
select * from news awhere exists(select top 1 1 from news_city where newsid=a.id and city=1)
比如，ID为1的文章内，地域字段是石景山（220），那么news_city内应该有两条信息
city newsid-------- --------1 1220 1
那么，触发器该怎么写呢，其实也很简单
CREATE TRIGGER extract_city -- 触发器名称 ON news -- 表名称 AFTER INSERT,DELETE,UPDATEAS BEGIN SET NOCOUNT ON; delete from news_city where newsid in (select id from deleted) with t1 as ( select a.id,a.city,b.city_parent from inserted a cross apply ( select city_parent from cities where city_id=a.city ) b union all select a.id,b.city_id,b.city_parent from t1 a inner join dict_cities b on a.city_parent=b.city_id ) insert into news_city(city,newsid) select id,city from t1END
在更新或删除时，清除相关文章的地域冗余信息
在更新或插入时，从新插入新的相关冗余信息
如此，我们便可以方便的查询分类及其子类的相关内容了
转载地址：http://axvxi.baihongyu.com/