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) . . .
文章评论