去除oracle时的一个问题解决历程

问题模拟场景简述：

首先，以前的oracle中有以下两张表：user_info,city_info

user_info: user_id, register_time, register_city(整型)

city_info: id, name, pid

city_info采用的是树形结构,根节点的pid为0，树的层级不确定，id从1开始，根节点为各个省份和直辖市

user_info中的register_city对应的是city_info的id，并且没有限制其必须是市，县或是其他级别

现要随时统计当前月份，各个省份和直辖市新注册的用户量，因为oracle中有start with等相关操作，这个功能可以得到较好的实现，但pgsql里，需要自己写递归查询，而且，输入并不是外部传入的一个变量或者定好的一个常量，而是另外一个表查询出的结果集（因为如果分开执行，查出所有user_info，再遍历地去调递归sql，性能损失极大），实现相当困难且不会有很好的性能。

最终解决方案:

更改表结构，city_info扩增一个字段city_root，用于存放一个city所属的省级city的id

写一个递归sql一次性更改数据库已有数据：

先查出所有省级city的id，遍历调用递归sql，

将当前省级city下的所有city（包含省级自身）的city_root填充为当前省级city的id：

<update id="changeOnce" parameterType="java.lang.Integer">
    update city_info t set t.city_root = #{Id}
    where t.id in
    (with RECURSIVE cte as
      (select t1.id, t1.pid from city_info t1 where t1.id = #{Id}
      union all
      select t2.id, t2.pid from conf_admin_city t2 inner join cte c on c.id = t2.pid
      ) select id from cte)
</update>

做完数据更改后，更改表的插入sql，使新插入的city记录，city_root字段填为父city的city_root

最后效果：性能非常好

因为现在进行统计的过程只需要连表查询就可以轻松搞定

<select id="getAddCount" resultMap="CountResult">
    select t1.city_root, count(1) as addCount
    from city_info t1, user_info t2
    where t2.register_city = t1.id
    and <![CDATA[t2.register_time < date_trunc( 'month', now())::timestamp + '1 month' ]]>
    and <![CDATA[t2.register_time >=  date_trunc( 'month', now())]]>
    group by t1.city_root
</select>