我想要这个SQL for循环

由 发布于 oraclesqlplsql
 收藏

下面是一个查询，其中我希望周期1,2,3是动态的，因为我希望它是可配置的。 表结构

CREATE TABLE "SalesTable"

（“ ID”始终以身份身份MINVALUE 1最大值（1,0）生成，最大值1 9999999999999999999999999999从1开始增加1缓存20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE，     “ UNIT” VARCHAR2（50 CHAR）NOT NULL ENABLE，     “ GROUP” VARCHAR2（50 CHAR）NOT NULL ENABLE，     “ LOC” VARCHAR2（50 CHAR）NOT NULL ENABLE，     “ POSTDATE” DATE NOT NULL ENABLE，     “销售”浮标（126）      主键（“ ID”）      ）

select a.UNIT,a.GROUP,a.LOC,a.mindate POSTDATE,
sum(CASE WHEN a.POSTDATE =mindate THEN sales ELSE 0 END) PERIOD1,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*1)) THEN sales ELSE 0 END) PERIOD2,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*2)) THEN sales ELSE 0 END) PERIOD3,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*3)) THEN sales ELSE 0 END) PERIOD4,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*4)) THEN sales ELSE 0 END) PERIOD5,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*5)) THEN sales ELSE 0 END) PERIOD6,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*6)) THEN sales ELSE 0 END) PERIOD7,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*7)) THEN sales ELSE 0 END) PERIOD8,
sum(CASE WHEN a.POSTDATE = (mindate + ((interval '1' minute)*a.DUR*8)) THEN sales ELSE 0 END) PERIOD9
from (
select UNIT,GROUP,LOC,POSTDATE,DUR,min(POSTDATE) OVER (PARTITION BY UNIT,GROUP,LOC) mindate, sum(sales) sales
from SalesTable GROUP BY UNIT,GROUP,LOC,POSTDATE,DUR
) a
GROUP BY a.UNIT,a.GROUP,a.LOC,a.mindate;
评论