SELECT cpay2.demandno,
COUNT(DISTINCT cpay2.ACCD_1) ACCD_1,
MAX(cpay2.ACCD_3) ACCD_3,
COUNT(DISTINCT cpay2.K_B) ACCD_KIND_B,
COUNT(DISTINCT cpay2.K_A) ACCD_KIND_A,
COUNT(DISTINCT cpay2.K_Z) ACCD_KIND_Z,
COUNT(DISTINCT cpay2.T1) ACCD_1_T1,
COUNT(DISTINCT cpay2.T2) ACCD_1_T2,
COUNT(DISTINCT cpay2.T3) ACCD_1_T3,
COUNT(DISTINCT cpay2.T4) ACCD_1_T4,
COUNT(DISTINCT cpay2.T5) ACCD_1_T5,
COUNT(DISTINCT cpay2.T9) ACCD_1_T9
FROM (SELECT cpay1.demandno,
cpay1.losstime ACCD_1,
(SELECT COUNT(DISTINCT t.losstime)
FROM ods_ciinsuredemandpay t,
ods_ciinsuredemandduplicated f
WHERE t.demandno = f.demandno
AND TO_DATE(t.losstime, 'YYYY-MM-DD hh24:mi') BETWEEN ADD_MONTHS(f.startdate, -36) AND f.enddate
AND t.demandno = cpay1.demandno
AND t.lossfee > 0 + UID * 0
GROUP BY t.demandno) ACCD_3,
DECODE(cpay1.kindcode, '2', cpay1.losstime) K_B,
DECODE(cpay1.kindcode, '3', cpay1.losstime) K_A,
DECODE(cpay1.kindcode, '9', cpay1.losstime) K_Z,
DECODE(cpay1.paytype, '1', cpay1.losstime) T1,
DECODE(cpay1.paytype, '2', cpay1.losstime) T2,
DECODE(cpay1.paytype, '3', cpay1.losstime) T3,
DECODE(cpay1.paytype, '4', cpay1.losstime) T4,
DECODE(cpay1.paytype, '5', cpay1.losstime) T5,
DECODE(cpay1.paytype, '9', cpay1.losstime) T9
FROM ods_ciinsuredemandpay cpay1,
(SELECT DISTINCT t.demandno,
t.startdate,
t.enddate
FROM ods_ciinsuredemandduplicated t) cdmd1
WHERE cpay1.lossfee > 0 + UID * 0
AND cpay1.demandno = cdmd1.demandno
AND TO_DATE(cpay1.losstime, 'YYYY-MM-DD hh24:mi') BETWEEN cdmd1.startdate AND cdmd1.enddate ) cpay2
GROUP BY cpay2.demandno
解决方案 »
- listagg 字符串连接的结果过长
- 这样的存储过程怎么写?利用它产生唯一主键
- oracle怎样根据case when 条件update纵表的值
- 哪位知道在Oracle的存储过程中如何加锁解锁?
- 求一个报表型的SQL语句
- 请问:EXEC SQL SELECT ename, sal, NVL(comm, 0) INTO :emprec INDICATOR :emprec_ind FROM EMP中的INDICATOR是什么意义啊?
- 两个有难度的分组,SQL语句(其实我不知道是不是真的有难度!)
- in 允许的最大数据量是多少?
- 关于两个表的查询
- ora-01114错误,求解
- 客户端配置了监听和服务,但是windows里的服务找不到监听程序...
- 如何接收过程为table类型的输出参数
每个表都可以
select uid from 表
对于同一个数据库,取出的uid值都是一样的但是这个数*0再加0 貌似和0没啥区别