前言

在上中，写了好多常用的sql, 由于太饿了，所以现在再来分享一会儿

正文

with RECURSIVE 递归: 大家都知道递归就是自己调自己，这里主要是通过已知时间获取最近10天的时间, 切记一定得加limit 限制要不然会跑死的

WITH RECURSIVE t(static_time) AS (

                                        SELECT date_trunc('day', CURRENT_DATE)  

                                    UNION ALL

                                        SELECT static_time-interval '1 day' FROM t

                                    )

                                    select  * from t limit 10

case when ：就是sql中的判断，一般应用于对照表，比如性别：sex, 姓名：name。如果说需要有多个值判断，则case when 字段名 then 'eee' end ,如果只针对一个字段判断的话，case 字段名 when 'eee' end

-- 多个字段判断
select  case when fonttype = 'name' then '姓名'
             when typenttt = 'sex' then '性别' 
             else '其他' end as  typename
from tablea

-- 一个字段判断 
select  case typename when '张甜甜' then 'ok' 
             when  '赵天天天' then 'no' end as nametype 
from tablea

case when 结合聚合函数 sum、max、min 的使用

-- 统计state = 1 和 state = 0 的个数, 这里将state的这个字段分别统计出为1的个数，和为0的个数，并且生成两个字段
select
     sum(case when state = 1 then 1 end) as count1,
     sum(case when state =  then  end) as count0,  
from tablea
-- 例如 tableb 中 有两个字段，分别是科目（英语、数学、语文），成绩。现在想实现统计出英语、语文、数学分别作为字段的成绩。
就是简单的列转行
select
     max(case when subject = '英语' then score else ) end as englist,
     max(case when subject = '数学' then score else ) end as math,   
     max(case when subject = '语文' then score else ) end as china,   
from tablea

COALESCE：也是属于判断的一个方法，判断如果该字段为null ,则可赋值

select coalesce(name, '赵天天') from tables

CURRENT_DATE：这个主要获取当前日期

select CURRENT_DATE

(values(1), (2), (3) , (4)) as t(event_grade) ：这个是自己创建一个临时表作为暂存用的，应用场景有可能是我需要一个已知的表去左关联另个表，这样就可以临时创建一个表

-- 这个表名为t , 字段为riskitem_type， type_index
 select * from (values('新兴','1'),('流行','2') ,('高危','3'), ('攻击','4'),('其他','5')) as t(riskitem_type, type_index)

rank() over(order by score desc)：用于排名，这里通过score进行分组， desc 或者 asc 用于正序还是倒序， rank() 这里的排名是可以并列排名的，下一个空出名次

dense_rank() over(order by score desc) 和 rank() 的区别是，这里的排名是可以并列排名的，下一个不空出名次

row_number() over(order by score desc) 和上面的区别是，不可以并列名次的，都是进行连续排名

select rank() over(order by score desc) as rank  from  tablea

array[]: 是数组，我在项目中通常知道有几个值，需要以数组返回

select array['菜单','首页','个人中心'] as msg

但是取数组值的时候不能从第0 位取，只能从第一位开始取

select msg[1] as msg1,
       msg[2] as msg2
from (
  select array['菜单','首页','个人中心'] as msg
) tablea

::integer 这个就是强制转换为 integer 类型， :: 加类型就可以强制转化类型了

-- 字符串转整型
select  '1' :: integer

-- 整型转text

select '5656565656' :: text

to_char 就是将时间转化为指定格式的字符串类型

select to_char(CURRENT_DATE, 'yyyy-mm-dd')

distinct 就是用于去重，但是distinct 只能放在头部如： select name, distinct sex from tablea 这样语法不允许

select distinct event_grade from (values(1), (1), (3) , (4)) as t(event_grade)

date_trunc：时间截取函数

select trunc(sysdate) from dual    //返回当前日期  sysdate: 系统时间
select trunc(sysdate,'year') from dual;   //返回本年的第一天
select trunc(sysdate,'month') from dual;   //返回本月的第一天
select trunc(sysdate,'q') from dual;       //返回本季度的第一天
select to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;  //获取当天的零时零分零秒

||：用于字符串拼接

select 'a' || 'b'

split_part: 用于对字符串的截取，一定得从第一位开始

select  split_part( '56-96', '-', 2 )

interval：用于对时间的加减

select date '2019-08-31' - interval '1 day' as start_time

ITPub博客

postgresql经验之谈下

前言

正文

最新文章