PGSQL WITH RECURSIVE / CASE WHEN

WITH RECURSIVE 递归

1. 查询所有子节点

WITH RECURSIVE res AS (
  SELECT t1.* FROM t_code as t1  
  WHERE t1.id = '0'
  UNION 
    SELECT t2.* from t_code as t2
    INNER JOIN res as t3 ON t3.id = t2.parent_id
    WHERE t2.enable = 'true'
)SELECT res.* from res

2.查询所有父节点

WITH RECURSIVE res AS (
  SELECT t1.* FROM t_code as t1  
  WHERE t1.id = '5'

  UNION 
    SELECT t2.* from t_code as t2
    INNER JOIN res as t3 ON t2.id = t3.parent_id
    WHERE t2.enable = 'true'
)SELECT res.* from res

CASE WHEN

SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

SELECT
       CASE a WHEN 1 THEN 'one'
            WHEN 2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

类型转换

CAST (source AS target_type)

CAST(num, AS INT)
CAST(name, AS VARCHAR)
. . .