SQL函数总结(更新中)

SQL函数总结(更新中)

Scroll Down

SQL函数总结

1.with as 临时表的写法(db2)

Db2

  	WITH A AS(
		SELECT PKID,HANDLE_STATUS,LEAD_UNIT,TASK_TYPE
		FROM 
		dbo.T_DOC_OFFICE 
		WHERE 
		LEAD_UNIT = #{UNDER_UNIT,jdbcType=VARCHAR}  or UNDER_UNIT LIKE CONCAT(CONCAT('%',#{UNDER_UNIT,jdbcType=VARCHAR}),'%') 
		)
		SELECT SUM(BLZ) AS 'BLZ',SUM(YBJ) AS 'YBJ',COUNT(1) AS TOTAL FROM (
		SELECT 
		CASE WHEN HANDLE_STATUS = '办理中' THEN 1 END AS 'BLZ',
		CASE WHEN HANDLE_STATUS = '已办结' THEN 1 END AS 'YBJ'
		FROM A 
		WHERE TASK_TYPE=#{TASK_TYPE}
		) A

常用来多表进行查询的时候用来提前筛选出数据 进行业务处理(在数据量大的时候 不推荐使用 因为相当于多进行了一次查询)

2.“,”拆分列为行

1.db2函数

1,2 3
效果:1 3
2 3

with n(str, ori, pos) as (
 values ('abc,bc,cd,d,ff,', 1, posstr('abc,bc,cd,d,ff,', ','))
 union all 
 select  str,  pos+1, locate(',', str, pos+1)
 from n 
 where locate(',', str, pos+1)>0)
 select str, ori, pos, substr(str, ori, pos-ori) as result from n

2.sqlserver

select distinct SUBSTRING(a.CONCERN_ID, b.number, CHARINDEX(',', a.CONCERN_ID+',', b.number)-b.number) AS LEADERS from air.dbo.T_DOC_CONCERN a
JOIN MASTER.dbo.spt_values b ON CHARINDEX(',', ','+a.CONCERN_ID, b.number) = b.number
WHERE  b.[type]= 'P'  

locate函数
locate(arg1,arg2,)

查找arg2中第一次出现arg1的位置,指定pos,则
从arg2的pos处开始找arg1第一次出现的位置。
posstr函数
posstr(arg1,arg2)查看arg2在arg1中的位置

3.行转列

1. listagg(db2)

行转列
1 3
2 3
效果:
1,2 3


SELECT
	T .DEPTNO,
	listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) names
FROM
	SCOTT.EMP T
WHERE
	T .DEPTNO = '20'
GROUP BY
	T .DEPTN

2. group_concar(mysql)

select `name`,group_concat(score separator ',') from test group by `name`

3.sqlserver

with A AS (
SELECT
	A.USER_ID,
	A.USER_NAME,
	A.LOGIN_NAME,
	C.DEPT_ID,
	C.DEPT_NAME,
	A.SORT,
	B.IS_MAIN 
FROM
	JDP_OU_USER A 
		INNER JOIN JDP_OU_USERDEPT B 
		ON A.USER_ID=B.USER_ID 
			INNER JOIN JDP_OU_DEPT C 
			ON B.DEPT_ID =C.DEPT_ID 
WHERE
	A.STATUS = 10 AND
	A.USER_ID ='304' 
)
SELECT 
DISTINCT
A.USER_ID,
A.USER_NAME,
A.LOGIN_NAME,
STUFF((SELECT ',' + convert(varchar(100),DEPT_ID) FROM A FOR xml path('')),1,1,'') DEPT_ID,
STUFF((SELECT ',' + convert(varchar(100),DEPT_NAME) FROM A FOR xml path('')),1,1,'') AS DEPT_NAME,
A.SORT
FROM A

4.按照一列数据排序并且返回一个编号

rownumber() over( order by)
先按psd进行排序,排序完后,给每条数据进行编号。

5.左侧数据补0 00999

1. digits (DB2)

digits(cast(999 as smallint))

6.条件函数instr

instr(en.DATASOURCE, '重点排污单位')=0 不包含
instr和like的区别
<1> instr>0 和like、instr=0 和not like 一般来说查询的结果相同(不考虑特殊字符)
<2> instr是一个函数,可以建立函数索引,如果过滤的条件有索引,那么instr就可以提高性能。
<3> like查询时,以'%'开头,列所加的索引是不起作用的。
<4> 在没有索引的前提下,当数据量比较大时,instr要比like效率高。

7.递归查询

1.MySQL 只能递归查询本表里面的数据 不能递归出数据

SELECT * FROM (SELECT id, parent_id, dept_code, dept_name FROM tb_dept WHERE parent_id IS NOT NULL) rd,(SELECT @pid := 3) pd
where FIND_IN_SET( parent_id, @pid ) > 0 AND @pid := concat( @pid, ',', id );

2.db2 递归出数据

WITH A(STR) AS (
    SELECT SYSDATE AS STR FROM DUAL
    UNION ALL
    SELECT A.STR -1 HOURS FROM A WHERE A.STR >SYSDATE -24 HOURS
)
SELECT STR FROM A

8.时间函数

1.sqlserver
1.将时间装换为指定的格式
CONVERT (VARCHAR(20),t.BEGIN_DATE,23) AS BEGIN_DATE
2.天数相减
DATEDIFF (DAY,T.HANDLE_SUC_DATE,GETDATE()) AS LASTDAY
3.根据指定时间取日期
DATENAME(DAY,T.BEGIN_DATE) AS CYCLEDAY
4.根据指定时间取周几
DATENAME(WEEKDAY,T.BEGIN_DATE) AS CYCLEWEEK
5.根据指定时间取月份
DATEPART(MONTH,T.BEGIN_DATE) AS CYCLEMONTH
6.季度算法
(CASE WHEN DATEPART(MONTH,T.BEGIN_DATE) % 2 =0 THEN 3 ELSE DATEPART(MONTH,T.BEGIN_DATE) % 2 END) AS CYCLEQUARTER
2.mysql
7.日期加减
select date_add(now(),interval -30 year/month/day/hour) FROM DUAL
8.时间搓
SELECT TO_CHAR(left(1483228800000,10) / (60 * 60 * 24)+TO_DATE('1970-01-01 08:00:00', 'YYYY/MM/DD HH:MI:SS'), 'YYYY/MM/DD HH24:MI:SS') FROM dual

-------------------------------->未完待续