PGSQL常用函数

1.to_char()：将字符串转为指定格式输出

列：to_char(xxfssj,'yyyy-MM-dd HH24:mi:ss') 将时间按指定格式输出。

2.position() > 0：判断是否存在指定字符串中/position()返回字符串中某个字符的位置

列：position('1' IN rylx) > 0 判断1是否在rylx字段里

3.string_agg()：根据分组将多条数据合并为一条数据

列：

SELECT string_agg(合并字段,',') as a FROM 表名 WHERE 1=1 GROUP BY 分组条件

4.concat（）：将同一条数据中的某些字段合并

列：

SELECT concat(合并字段A,',',合并字段B) as a FROM 表名

5.row_number ( ) over ( 字段名 ) as xh：排序

SELECT row_number ( ) over ( ORDER BY 字段名 DESC ) AS xh from 表名

6.split_part(字段 ,',', 1)：截取某个字符串

split_part(bamjbh （要截取的字段）,','(分隔符), 1(截取第几位))

7.COALESCE(col,value) :空值替换函数

COALESCE('字段值',需要替换的值)

8.unnest：将以逗号分隔的字符串进行分割，转为多行

SELECT unnest(string_to_array(字段名,',')) as 别名;

9.模糊匹配（varchar用法）

1.~ 匹配正则表达式，大小写相关.

2.~* 匹配正则表达式，大小写无关.

3.！~ 匹配正则表达式，大小写无关

10.递归查询

1. WITH RECURSIVE递归函数

例如：父单位下有子单位，子单位下有子单位，查询父单位下的所有用户，向下递归查询

SELECT t1.user_id FROM samr_user_info t1
WHERE t1.unit_id IN (
 WITH RECURSIVE TEMP AS (
SELECT unit_id FROM samr_unit_info WHERE	unit_id =1 UNION
SELECT	bmb.unit_id 	FROM	samr_unit_info bmb
INNER JOIN TEMP T ON T.unit_id::VARCHAR = bmb.parent_unit_id ) SELECT unit_id FROM TEMP
)

11.判断某个字符是否在指定字符中

1.比如判断 a 在（ab,ac,bc）中是否存在，比较的字符以逗号间隔开->查询包含的数据

SELECT  字段1，字段2 FROM 表1 WHERE '指定字符'=ANY (regexp_split_to_array('比较的字符', ','))

2.1.比如判断 a 在（ab,ac,bc）中是否存在，比较的字符以逗号间隔开，coalesce:去除空串->查询不包含的数据

SELECT  字段1，字段2 FROM 表1 WHERE '指定字符'!=all (regexp_split_to_array(COALESCE('比较的字符','') , ','))

12.获取中文首字母大写（数据库函数）

SELECT chtopy(xm),xm FROM table

CREATE OR REPLACE FUNCTION "public"."chtopy"("s" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare
  retval character varying;
  c character varying;
  l integer;
  b bytea;  
  w integer;
	
	temp_i integer;
	
	
	mbzf character varying;
    
  i integer;
	
	
begin

mbzf=s ;




l=length(mbzf);
retval='';


i=1 ;

while i<=l loop

  -- c=left(mbzf,1);
		
  c=substring(mbzf,i,1);
	
  b=convert_to(c,'GB18030')::bytea;

	
  if get_byte(b,0)<127 then
    retval=retval || upper(c);
  elsif length(b)=2 then
    begin
    w=get_byte(b,0)*256+get_byte(b,1);
    --汉字GBK编码按拼音排序，按字符数来查找，基于概率来说，效率应该比挨个强:)
		
		
    if w between 48119 and 49061 then --"J";48119;49061;942
      retval=retval || 'J';
    elsif w between 54481 and 55289 then --"Z";54481;55289;808
      retval=retval || 'Z';
    elsif w between 53689 and 54480 then --"Y";53689;54480;791
      retval=retval || 'Y';
    elsif w between 51446 and 52217 then --"S";51446;52208;762
      retval=retval || 'S';
    elsif w between 52980 and 53640 then --"X";52980;53640;660
      retval=retval || 'X';
    elsif w between 49324 and 49895 then --"L";49324;49895;571
      retval=retval || 'L';
    elsif w between 45761 and 46317 then --"C";45761;46317;556
      retval=retval || 'C';
    elsif w between 45253 and 45760 then --"B";45253;45760;507
      retval=retval || 'B';
    elsif w between 46318 and 46825 then --"D";46318;46825;507
      retval=retval || 'D';
    elsif w between 47614 and 48118 then --"H";47614;48118;504
      retval=retval || 'H';
    elsif w between 50906 and 51386 then --"Q";50906;51386;480
      retval=retval || 'Q';
    elsif w between 52218 and 52697 then --"T";52218;52697;479
      retval=retval || 'T';
    elsif w between 49896 and 50370 then --"M";49896;50370;474
      retval=retval || 'M';
    elsif w between 47297 and 47613 then --"G";47297;47613;316
      retval=retval || 'G';
    elsif w between 47010 and 47296 then--"F";47010;47296;286
      retval=retval || 'F';
    elsif w between 50622 and 50905 then--"P";50622;50905;283
      retval=retval || 'P';
    elsif w between 52698 and 52979 then--"W";52698;52979;281
      retval=retval || 'W';
    elsif w between 49062 and 49323 then--"K";49062;49323;261
      retval=retval || 'K';
    elsif w between 50371 and 50613 then --"N";50371;50613;242
      retval=retval || 'N';
    elsif w between 46826 and 47009 then--"E";46826;47009;183
      retval=retval || 'E';
    elsif w between 51387 and 51445 then--"R";51387;51445;58
      retval=retval || 'R';
    elsif w between 45217 and 45252 then --"A";45217;45252;35
      retval=retval || 'A';
    elsif w between 50614 and 50621 then --"O";50614;50621;7
      retval=retval || 'O';
    end if;
		
		
    end;
		end if;
		
  --mbzf:=substr(mbzf,2,l-1);
	
  i=i+1;
	
end loop;


return retval;



END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

13.特殊字符的处理，保证字符在编译的时候不会被转义

<![CDATA[ 需要处理的字符 ]]>
<![CDATA[ AND jz.gxsj <= '${dto.endTime} 23:59:59' ]]> 

14.对数据统计切在首行加入合计

效果图：

示例代码：

 

SELECT
COALESCE(xtbm, '1') AS dwbm,
COALESCE(xtmc, '合计') AS dwmc,
COALESCE(SUM(rk), 0) AS rk
FROM 
(

SELECT jz.babmbm as xtbm,jz.babmmc as xtmc,
CASE WHEN czlxbm = '1' THEN 1 ELSE 0 END AS rk
FROM qx_ag_jzxx AS jz
LEFT JOIN (
SELECT jzbh, czlxbm
FROM qx_ag_jzcrkjl
WHERE state = 0
GROUP BY jzbh, czlxbm
) AS jl ON jz.jzbh = jl.jzbh


) AS t2
GROUP BY GROUPING SETS ((xtbm, xtmc), ())
ORDER BY dwbm