To_date('2015-05-01', 'yyyy-mm-dd')

本文档提供了一系列SQL查询案例,包括部门奖金计算、重复数据检查等操作。通过具体SQL语句展示了如何从不同维度进行数据汇总及筛选。
 ------分点部计提汇总奖金----------------------
select dept_code,
       emp_code ,
       COMMISSION_month,
       emp_convert_time,
       sum(emp_commis_money)
  from TR_TCAS_DEPOT_COMMISSION_EMP
 GROUP BY dept_code, emp_code, COMMISSION_month, emp_convert_time
 
        GROUP BY  
  select t.*
    from TT_TCAS_HUBPBS_HUB_EMP_INFO t
   where dept_code = '020WD'
     and work_date >= to_date('2015-05-01', 'yyyy-mm-dd')
     and work_date < to_date('2015-06-01', 'yyyy-mm-dd');
     
     
     
       select t.*
    from TT_TCAS_HUBPBS_HUB_EMP_INFO t
   where dept_code = '020WD'
     and work_date >= to_date('2015-05', 'yyyy-mm')

     and work_date < to_date('2015-06', 'yyyy-mm');


 select t.*, t.rowid
   from ccw_push_record t
  WHERE T.ALI_USERID IN
        ('C506A88839298CACB345D076DEAF78A9',
         'gill3brKJHdAPjlDtEuhQWcTDbmpvKZWm7oa +
                         WtN3mUQCYxETDvGao + ZLzOHbRwk01')
    AND T.CREATE_TM > to_date('2016-01-25', 'yyyy-mm-dd')
  order BY T.CREATE_TM DESC;



merge into (select * from CCWP_QUDAO_HMD where phone is null) d
using (with group1 as (select max(m.id) id,
                              m.ALIPAY_UID,
                              m.bind_status,
                              max(m.bind_status) over(partition by m.ALIPAY_UID) maxStatus
                         from tlc_alipay_phone_bind m
                        group by m.ALIPAY_UID, m.bind_status)
  select b.*
    from group1 g
    join tlc_alipay_phone_bind b on g.id = b.id
   where g.maxStatus = g.bind_status) s on (d.alipay_uid = s.ALIPAY_UID) when matched then
    update
       set d.phone           = s.phone,
           d.alipay_publicid = s.ALIPAY_PUBLICID,
           d.falg            = 1,
           d.creator         = 'admin',
           d.creattm         = sysdate,
           d.updator         = 'admin',
           d.updatetm        = sysdate;


commit;

--------------重复数据查询-------------------------

select  t.push_type,t.bno,t.order_channel,max(t.create_tm),min(t.create_tm),count(1)
  from ccw_push_record t
 where t.push_type in ('2', '3', '4') 
   and trunc(sysdate-2) < t.create_tm
   and t.create_tm < trunc(sysdate-1)
   
   group by t.push_type,t.bno,t.order_channel having(count(1))>1

SELECT * FROM ( SELECT nvl(b.NAME,'待分配') responsible_Dept ,count(1) punches_In_Total ,count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) at_Punches_In_Total ,count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) bt_Punches_In_Total ,count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end) closed_In_Total ,count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Closed ,count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Closed ,CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'A' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end),2) end punch_At_Closed_Rate ,CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'B' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end),2) end punch_Bt_Closed_Rate ,CASE WHEN count(1) =0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end)/count(1),2) END closed_Rate ,count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Remaining ,count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Remaining ,count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 end) remaining_In_Total ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) total_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd')+1)<to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Difference ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end) weekly_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Difference ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) daily_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Difference FROM COMMENT_MAINDETAILS a LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT WHERE MAIN_TYPE_CODE = 'N06' and SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202 GROUP BY b.NAME UNION SELECT '合计' responsible_Dept ,count(1) punches_In_Total ,count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) at_Punches_In_Total ,count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) bt_Punches_In_Total ,count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end) closed_In_Total ,count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Closed ,count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Closed ,CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'A' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end),2) end punch_At_Closed_Rate ,CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'B' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end),2) end punch_Bt_Closed_Rate ,CASE WHEN count(1) =0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end)/count(1),2) END closed_Rate ,count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Remaining ,count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Remaining ,count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 end) remaining_In_Total ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) total_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd')+1)<to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Difference ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end) weekly_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Difference ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Planned_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) daily_Actual_Elimination ,count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Difference FROM COMMENT_MAINDETAILS a LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT WHERE MAIN_TYPE_CODE = 'N06' and SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202 ) 怎么让总计在最后一行其他按照punches_In_Total降序
最新发布
12-06
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值