postgres常用查询

一.字符串截取

left: 从左往右截取字符
right: 从右往左截取字符

如截取4个字符:

SELECT left( column_name, 4 ) from table
SELECT right( column_name, 4 ) from table

二.条件统计

COUNT(CASE WHEN column_name= ‘value’ THEN 1 END) AS count_name

如截统计值1,值2的数量:

SELECT
COUNT(CASE WHEN column_name= ‘value1’ THEN 1 END) AS count_name1,
COUNT(CASE WHEN column_name= ‘value2’ THEN 1 END) AS count_name2,
COUNT(*) AS total
from table

三.时间转字符

to_char( column_name, ‘YYYY年MM月’ )
如:

select to_char( column_name, ‘YYYY年MM月’ ) from table

四.时间格式化

:Date
时间强制转换,把时间戳转换为日期
如:

select create_time:Date as create_date from table

date_trunc(‘year’, now())
获取本年1月1日0时0分时间
date_trunc(‘month’, now())
获取本月1日0时0分时间
date_trunc(‘day’, now())
获取今天0时0分时间
如查询今年创建的数据:

SELECT * FROM table WHERE create_time >date_trunc(‘year’, now())

五,条件返回值

if(column_name==value, value_when_true, value_when_false)
当某列值等于value时返回value_when_true, 否则返回value_when_false
如查询column_name为1就返回1, 其他值都返回0

SELECT if(column_name==1, 1, 0) as column_name from table

如条件求和,当值大于value才累加

SELECT sum(if(column_name>value, column_name, 0) )as column_name from
table

六.返回第一个非空值

COALESCE ( column_name, value1,value2 )
如某列为空则赋值为0,否则为它本身

SELECT COALESCE (column_name, 0 ) from table

七.查询所有最新记录

ROW_NUMBER() over(PARTITION BY column_name ORDER BY create_time DESC )
每条数据的编号：row_number()
分组排序：over(partition by column_name order by order_column_name )
分组后的数据获取第一条

查询所有最新记录示例：

SELECT * FROM (SELECT ROW_NUMBER() over(PARTITION BY column_name ORDER BY create_time DESC ) AS serialOrder, * FROM t_goods) temp WHERE temp.serialOrder= 1;