开发笔记

  • 首页
  • 工具箱
三味线的博客
  1. 首页
  2. SQL
  3. 正文

PGSQL WITH RECURSIVE / CASE WHEN

2019-07-20 294点热度 0人点赞 0条评论

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)
. . .
本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: 暂无
最后更新:2020-06-06

Clownce

不吃咸鱼的猫

点赞
下一篇 >

文章评论

取消回复

Captcha Code

COPYRIGHT © 2020 Clownce. ALL RIGHTS RESERVED.

THEME KRATOS MADE BY VTROIS

蜀ICP备18010095号