SQL笔记

统计排名

select A.deptcode,A.score,RANK() over(order by A.score desc) as ran from (select deptcode,sum(score) as score from tb_kaohe group by deptcode )A

查询年份

今年 year(now())
去年 year(dateadd(yy,-1,getdate()))
前年 year(dateadd(yy,-2,getdate()))

查询12个月份数据（列转行）

select concat
	(
		jan, ',', Feb, ',', Mar, ',', Apr, ',', May, ',', June, ',', July, ',', Aug, ',', Sept, ',', Oct,',', Nov, ',', Dece
	) as Year
from 
	(
		select sum(case month (sendtime) when '1'  then 1 else 0 end) as jan,
               sum(case month (sendtime) when '2'  then 1 else 0 end) as Feb,
               sum(case month (sendtime) when '3'  then 1 else 0 end) as Mar,
               sum(case month (sendtime) when '4'  then 1 else 0 end) as Apr,
               sum(case month (sendtime) when '5'  then 1 else 0 end) as May,
               sum(case month (sendtime) when '6'  then 1 else 0 end) as June,
               sum(case month (sendtime) when '7'  then 1 else 0 end) as July,
               sum(case month (sendtime) when '8'  then 1 else 0 end) as Aug,
               sum(case month (sendtime) when '9'  then 1 else 0 end) as Sept,
               sum(case month (sendtime) when '10' then 1 else 0 end) as Oct,
               sum(case month (sendtime) when '11' then 1 else 0 end) as Nov,
               sum(case month (sendtime) when '12' then 1 else 0 end) as Dece
        from "DOCUMENTFILE"."tb_sendtelegram"
        where year (sendtime)= YEAR (NOW())
    )

统计各市（dept_id）下单位的收发文数量

select t.DEPT_ID PARENT_ID, count(s.senduserorg) num
from (
			select distinct DEPT_ID
      		from "FILETRANSFER"."sys_dept"
     		 where DEPT_ID BETWEEN '103' and '115'
     ) t
left join
     (
      	select distinct PARENT_ID, DEPT_ID
      	from "FILETRANSFER"."sys_dept"
      	where PARENT_ID BETWEEN 103 and 115
     ) a
on t.DEPT_ID = a.PARENT_ID
left join
     (
        select * from "FILETRANSFER"."tb_sendtelegram" where year (sendtime)= YEAR (NOW())
     ) s
on a.DEPT_ID = s.senduserorg
group by t.DEPT_ID;

自动生成 ID

DM

select sys_guid() from dual

<selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
	select sys_guid() from dual
</selectKey>

MySQL

select replace(uuid(),'-','') from dual

<selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
	select replace(uuid(),'-','') from dual
</selectKey>