17,081
社区成员
发帖
与我相关
我的任务
分享
to_char(D,'IW')
--替换为
TO_CHAR(
DECODE(TO_CHAR(D,'IW'),'52',next_day(D-7,2),'53',next_day(D-7,2),D)
,'YYYYIW')
with T AS (
select to_char(D,'yyyy-mm') 月度,
ROW_NUMBER()OVER(PARTITION BY to_char(D,'yyyy-mm') ORDER BY to_char(D,'IW')) 周次,
sum(金额) 金额
FROM table1
group by to_char(D,'yyyy-mm'),to_char(D,'IW')
)
select 月度,
SUM(DECODE(周次,1,金额)) 第一周金额,
SUM(DECODE(周次,2,金额)) 第二周金额,
……
from T
group by 月度
with T AS (
select to_char(D,'yyyy-mm') 月度,ceil((to_char(D,'dd')-to_char(D,'d')+to_char(trunc(D,'month'),'d'))/7) 周次,sum(金额) 金额
FROM table1
group by to_char(D,'yyyy-mm'),ceil((to_char(D,'dd')-to_char(D,'d')+to_char(trunc(D,'month'),'d'))/7)
)
select 月度,
SUM(DECODE(周次,1,金额)) 第一周金额,
SUM(DECODE(周次,2,金额)) 第二周金额,
……
from T
group by 月度