楼主试着用 累加判断试试
类似于:with table_name(id,name,cd,numb)as(
select 1,'N1','A',12 from dual
union all
select 2,'N2','A',13 from dual
union all
select 3,'N3','B',21 from dual
)
select t.*,sum(t.numb) over(partition by cd order by id) lft from table_name t结果:
ID NAME CD NUMB LFT
---------- ---- -- ---------- ----------
1 N1 A 12 12
2 N2 A 13 25
3 N3 B 21 21
类似于:with table_name(id,name,cd,numb)as(
select 1,'N1','A',12 from dual
union all
select 2,'N2','A',13 from dual
union all
select 3,'N3','B',21 from dual
)
select t.*,sum(t.numb) over(partition by cd order by id) lft from table_name t结果:
ID NAME CD NUMB LFT
---------- ---- -- ---------- ----------
1 N1 A 12 12
2 N2 A 13 25
3 N3 B 21 21
SELECT A.ID, A.EXTENNUM,A.BILLEDTYPE,A.CALLINGNUM,A.CALLEDNUM,
A.STARTTIME,A.ENDTIME,A.CONTIME,A.CONCOST,A.MAID,
(CASE WHEN A.TIME>B.ARULES OR A.COST>B.PRICES THEN '套餐外' ELSE '套餐内' END) TYPE
FROM
(select T.*,SUM (CONTIME) OVER (PARTITION BY MAID ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) TIME
,SUM (CONCOST) OVER (PARTITION BY MAID ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) COST
from TS_INVENTORY T) A,
TS_MATERIALS B
WHERE A.ID=B.MAID
另外如果通话时间跨月的话怎么计算?
A.STARTTIME,A.ENDTIME,A.CONTIME,A.CONCOST,A.MAID,
(CASE WHEN A.TIME>B.ARULES OR (B.ARULES=0 and A.COST>B.PRICES)
THEN '套餐外' ELSE '套餐内' END) TYPE
FROM
(select T.*,SUM (CONTIME) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) TIME
,SUM (CONCOST) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) COST
from TS_INVENTORY T) A,
TS_MATERIALS B
WHERE A.ID=B.MAID
再试一下吧
SELECT A.ID, A.EXTENNUM,A.BILLEDTYPE,A.CALLINGNUM,A.CALLEDNUM,
A.STARTTIME,A.ENDTIME,A.CONTIME,A.CONCOST,A.MAID,
(CASE WHEN A.TIME>B.ARULES OR (B.ARULES=0 and A.COST>B.PRICES)
THEN '套餐外' ELSE '套餐内' END) TYPE
FROM
(select T.*,SUM (CONTIME) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) TIME
,SUM (CONCOST) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) COST
from TS_INVENTORY T) A,
TS_MATERIALS B
WHERE A.maID=B.MAID
order by a.endtime;
而你给出的测试数据没有这样的数据,呵呵,再试试
SELECT A.ID, A.EXTENNUM,A.BILLEDTYPE,A.CALLINGNUM,A.CALLEDNUM,
A.STARTTIME,A.ENDTIME,A.CONTIME,A.CONCOST,A.MAID,
(CASE WHEN (B.ARULES>0 and A.TIME>B.ARULES) OR (B.ARULES=0 and A.COST>B.PRICES)
THEN '套餐外' ELSE '套餐内' END) TYPE
FROM
(select T.*,SUM (CONTIME) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) TIME
,SUM (CONCOST) OVER (PARTITION BY MAID,TO_CHAR(ENDTIME,'YYYYMM') ORDER BY ENDTIME RANGE UNBOUNDED PRECEDING) COST
from TS_INVENTORY T) A,
TS_MATERIALS B
WHERE A.maID=B.MAID
order by a.endtime;