--一日直充无效订单
select count(1) zcwx from ykc_TF_B_ONLINEPAY
where PAY_STATUS='0' and to_char(PAY_FEE_DATE,'yyyy-mm-dd')='&time' and trade_id like 'J98%1';
--一日直充支付成功总数
select count(1) zczs from Tf_b_Chargeinfo t
where CHARGE_TYPE='2' and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time'
and trade_id like 'J98%1';
--一日直充成功笔数
select count(1) zccg from Tf_b_Chargeinfo
where CHARGE_TYPE='2' and CHARGE_STATUS='1' and CARD_CHARGE_STATUS='1'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1';
--一日直充成功全国情况
select * from Tf_b_Chargeinfo
where CHARGE_TYPE='2' and CHARGE_STATUS='1' and CARD_CHARGE_STATUS='1'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1';
--看某省某天的直充成功数
select count(1) 直充成功笔 from Tf_b_Chargeinfo
where CHARGE_TYPE='2' and CHARGE_STATUS='1' and CARD_CHARGE_STATUS='1'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1'and entry_province='59';
--一日直充失败全国情况
select * from Tf_b_Chargeinfo
where CHARGE_TYPE='2' and (CARD_CHARGE_STATUS='2' or (CARD_CHARGE_STATUS='1' and CHARGE_STATUS='2'))
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1' and entry_province!='81';
--看某省某天的直充失败
select count(*) 直充失败笔数 from Tf_b_Chargeinfo
where CHARGE_TYPE='2' and (CARD_CHARGE_STATUS='2' or (CARD_CHARGE_STATUS='1' and CHARGE_STATUS='2'))
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1' and entry_province='59';
--一日充值支付成功总数
select count(1) czsb from Tf_b_Chargeinfo
where CHARGE_TYPE='1' and trade_id like 'J98%0'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time';
--一日充值成功
select count(1) czcg from Tf_b_Chargeinfo
where CHARGE_TYPE='1' and CHARGE_STATUS='1' and CARD_CHARGE_STATUS='1'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0';
--一日充值成功全国情况
select * from Tf_b_Chargeinfo
where CHARGE_TYPE='1' and CHARGE_STATUS='1' and CARD_CHARGE_STATUS='1'
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0';
--一日充值全国失败
select * from Tf_b_Chargeinfo
where CHARGE_TYPE='1' and ( CARD_CHARGE_STATUS!='1' or CHARGE_STATUS!='1' )
and to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0';
--一日够卡成功总张数
select count(TRADE_ID) from TF_B_CHARGESALELOG
where to_char(SALE_TIME,'yyyy-mm-dd')='&time' and TRADE_ID like '02%';
--查看各个省份每天的直充笔数
select * from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1'
and entry_province='30'
;
--查每天充值笔数最多的省
select count(*) 充值笔数, sum(money) 总金额,entry_province 省份 from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0'
group by entry_province order by count(*)
;
--查每天直充笔数最多的省
select count(*) 充值笔数, sum(money) 总金额,entry_province 省份 from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%1'
group by entry_province order by count(*)
;
--查每天直充和充值总笔数最多的省
select count(*) 笔数, sum(money) 总金额,entry_province 省份 from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%'
group by entry_province order by count(*)
;
--查每天直充和充值总金额最多的省
select count(*) 充值笔数, sum(money) 总金额,entry_province 省份 from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%'
group by entry_province order by sum(money)
;
--查看每日卡号不同省的充值情况
select * from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0'
and card_province!=entry_province and card_province!='98'
;
---统计每日省份卡的充值情况
select * from Tf_b_Chargeinfo
where to_char(CHARGE_TIME,'yyyy-mm-dd')='&time' and trade_id like 'J98%0'
and card_province!='98'
;
--在线支付表
select count(*) 直充加购卡笔数 from ykc_tf_b_onlinepay tsd where to_char(tsd.pay_fee_date,'yyyy-mm-dd')='&time'
and tsd.pay_status = '1';
--卡销售记录表
select count(distinct(tdd.trade_id)) from tf_b_chargesalelog tdd
where to_char(tdd.sale_time,'yyyy-mm-dd')='&time';
--直充与购卡涉及的总卡张数
select count(tdd.trade_id) 涉及的卡张数 from tf_b_chargesalelog tdd
where to_char(tdd.sale_time,'yyyy-mm-dd')='&time';
--18日直充这笔易宝可能没有记录
select * from Tf_b_Chargeinfo where trade_id like 'J980811182359%1';
--统计一天实时的充值失败金额
select nvl(sum(MONEY),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081201' and TRADE_ID like 'J%0'
and (CHARGE_STATUS<>'1' or CARD_CHARGE_STATUS<>'1');
--统计一天实时的直充成功笔数
select nvl(count(*),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%1' and CHARGE_STATUS='1';
--统计一天实时的直充成功金额
select nvl(sum(MONEY),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%1' and CHARGE_STATUS='1';
--统计一天实时的直充失败笔数
select nvl(count(*),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%1'
and (CHARGE_STATUS<>'1' or CARD_CHARGE_STATUS<>'1');
--统计一天实时的直充失败金额
select nvl(sum(MONEY),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%1'
and (CHARGE_STATUS<>'1' or CARD_CHARGE_STATUS<>'1');
--统计一天实时的充值成功笔数
select nvl(count(*),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%0' and CHARGE_STATUS='1';
--统计一天实时的充值成功金额
select nvl(sum(MONEY),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%0' and CHARGE_STATUS='1';
--统计一天实时的充值失败笔数
select nvl(count(*),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%0'
and (CHARGE_STATUS<>'1' or CARD_CHARGE_STATUS<>'1');
--统计一天实时的充值失败金额
select nvl(sum(MONEY),0) from TF_B_CHARGEINFO
where to_char(CHARGE_TIME,'yyyymmdd')='20081121' and TRADE_ID like 'J%0'
and (CHARGE_STATUS<>'1' or CARD_CHARGE_STATUS<>'1');
--统计一天实时的购卡的卡品分布
select nvl(count(*),0) from TF_B_CHARGESALELOG
where TRADE_ID like '02%' and PAR_VALUE='500'
and to_char(SALE_TIME,'yyyymmdd')='20081120';
--统计一天实时的购卡的面值合计
select nvl(sum(PAR_VALUE),0) from TF_B_CHARGESALELOG
where TRADE_ID like '02%' and to_char(SALE_TIME,'yyyymmdd')='20081120';
---电子渠道卡充值成功金额统计，分直充，充值，IVR
--WEB充值,成功
--不包括一卡两充1893650
--包括一卡两充1894050
select sum(money)
from (
select card_no,money from Tf_b_Chargeinfo t
where card_no like '98%'
and (to_char(charge_time,'yyyymm')='200811' or to_char(charge_time,'yyyymm')='200810')
and charge_status='1'
and card_charge_status ='1'
-- group by card_no,money
-- having count(card_no)=1
);
-- IVR充值且没有进行补充值的总金额
--467240
select sum(payamount / 1000)
from (
select crdnum, payamount,channeltype
from TF_CARD_INTERFACE_PAY
where crdnum like '98%'
and (to_char(processtime, 'yyyymm') = '200811' or to_char(processtime, 'yyyymm') = '200810')
group by crdnum, payamount,channeltype
having count(crdnum) = 1
)
where channeltype = 'IVR'
;
自己写的经典SQL积累
需积分: 1 64 浏览量
2011-08-09
22:33:45
上传
评论
收藏 26KB RAR 举报
My Sql.rar （42个子文件） 
My Sql 
创建广告.sql 668B MP--同比.sql 5KB 查询团队的触发类通知带区域和小区.sql 692B 虚拟团队管理.sql 208B 查看IP归属省语句.sql 1KB 5版统计分析语句.sql 6KB 对账.sql 5KB 分拣文件读取.sql 206B 日志模块.sql 789B 购卡业务量日分析报表.sql 531B 购卡信息根据IP落到省分.sql 2KB MP图片生成查询语句.sql 976B 查MAP文件在哪些主机哪些用户下使用了.sql 2KB 取卡.sql 1KB 网上交费统计.sql 862B 哪些用户到达集合地.sql 4KB 备份还原MAP文件.sql 360B 根据主机和用户类型校验OPC号.sql 2KB 一日充值直充失败明细.sql 3KB 小节名称校验.sql 3KB MAP树多一层.sql 465B 小节-配置文件-主机.sql 170B 直充充值失败全国统计.sql 4KB ISPP相关表.sql 352B 修改密码测试.sql 114B 统计充值共涉及多少号码.sql 238B VAS用户角色权限.sql 748B 备份恢复分拣文件.sql 421B 备份恢复配置信息语句.sql 3KB 收款日月报表.sql 3KB TaskManage发邮件提醒条件.sql 430B 购卡直充充值各种业务查询.sql 7KB 特定主被叫营销.sql 112B MP相关表.sql 1KB 购卡直充充值每日情况统计.sql 5KB 按时间段统计各省直充充值笔数和金额.sql 7KB 共享内存值校验.sql 2KB 查看线程会话的语句.sql 315B 卡销售及直充业务应收款项对帐日报表.sql 2KB 发送历史查询.sql 2KB 下发配置文件校验.sql 305B 应用端口号判断.sql 3KB资源评论
zhouli253
- 粉丝: 15
- 资源: 135
最新资源
- 基于Vue+Echarts实现风力发电机中传感器的数据展示监控可视化系统+源代码+文档说明(高分课程设计)
- 基于单片机的风力发电机转速控制源码
- 基于C++实现的风力发电气动平衡监测系统+源代码+测量数据(高分课程设计)
- 毕业设计- 基于STM32F103C8T6 单片机，物联网技术的太阳能发电装置+源代码+文档说明+架构图+界面截图
- 基于 LSTM（长短期记忆）（即改进的循环神经网络）预测风力发电厂中风力涡轮机产生的功率+源代码+文档说明
- 基于stm32f103+空心杯电机+oled按键+运动算法
- 《CKA/CKAD应试指南/从docker到kubernetes 完全攻略》学习笔记 第1章docker基础（1.1-1.4）
- 基于python实现的水下压缩空气储能互补系统建模仿真与经济效益分析+源代码+论文
- 华中科技大学-自然语言处理实验，Bi-LSTM+CRF的中文分词框架，并且利用基于深度学习的方法进行中文命名实体识别++源码报告
- 基于动态罚函数的铁路车流分配与径路优化模型python源码
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
