SQL统计语句记录

1.达梦数据库

统计指定单位的12个月份的业务数据

SELECT 
      a.DEPT_ID, b.dept_name, a.USER_NAME, count(a.dept_id) as count, 
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-01' THEN '1' else '0' end) as one,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-02' THEN '1' else '0' end) as two,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-03' THEN '1' else '0' end) as three,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-04' THEN '1' else '0' end) as four,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-05' THEN '1' else '0' end) as five,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-06' THEN '1' else '0' end) as six,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-07' THEN '1' else '0' end) as seven,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-08' THEN '1' else '0' end) as eight,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-09' THEN '1' else '0' end) as nine,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-10' THEN '1' else '0' end) as ten,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-11' THEN '1' else '0' end) as eleven,
      sum(case when  to_char(a.CREATE_TIME,'yyyy-mm') = '2023-12' THEN '1' else '0' end) as twelve
FROM 
    SYS_USER  as a
    left join sys_dept as b
     on a.dept_id = b.dept_id
WHERE 
     a.CREATE_TIME >='2023-01-01' AND a.CREATE_TIME <= '2023-12-31' 
     group by a.dept_id, b.dept_name,  user_name

2.达梦数据库计算机两个日期之间的天数

DATEDIFF(day,sysdate(), dt_jzrq_date) as am_last_day

3.人大金仓数据库计算两个日期之间的天数

 FLOOR(sysdate()- DATE('2024-05-25'))