SQL语句的编写--生产中遇到的复杂sql语句

1.从父到子（递归查询）--通过父类唯一标识进行查询所有子类数据

2.从子到父（递归查询）--通过子类，查询所有的上级分类数据

## -- 1.从父到子
WITH RECURSIVE dict AS (
     SELECT *
     FROM creditcard.xyk_file_category
     WHERE code= '102000'
     union ALL
     SELECT ca.*
     FROM creditcard.xyk_file_category ca,
          dict
     WHERE ca.parent_code = dict.code
)
SELECT  id AS id, name as name,code, parent_code 
FROM dict
ORDER BY id


## -- 2.从子到父

WITH RECURSIVE dict AS (
     SELECT *
     FROM creditcard.xyk_file_category
     WHERE code= '102004'
     union ALL
     SELECT ca.*
     FROM creditcard.xyk_file_category ca,
          dict
     WHERE ca.code = dict.parent_code
)
SELECT id AS id, code,name as name, parent_code 
FROM dict
ORDER BY id

xml:  postgrepsql数据库的时间范围查询，以及格式化

<if test="startDate != null ">
    and to_date(to_char(crt_time,'yyyy-MM-dd'),'yyyy-MM-dd') <![CDATA[>=]]> #{startDate}
</if>
<if test="endDate != null ">
    and to_date(to_char(crt_time,'yyyy-MM-dd'),'yyyy-MM-dd') <![CDATA[<=]]> #{endDate}
</if>