时间戳

首页 > 解决方案 > 创建表中的 group by 问题作为选择

oracle - 创建表中的 group by 问题作为选择

问题描述

我对 Oracle 的查询有一个非常奇怪的问题。如果我是否根据此查询的结果创建表，则选择查询的结果不同。

1/ 仅选择查询：

SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT,
    SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,
    SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) total
FROM(
    SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE,
            NVL(SUM(SLICE),0) TOTAL
        FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID
        WHERE SLICE_REQUEST_ID=113
        AND  TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY')
  and PRJ_OBJECT_ID = 5002239
    group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM')
    )
group by PRJ_OBJECT_ID,SUBSTR(YEAR,1,4)

结果：我只有一行 结果

2/ 根据结果创建表

CREATE TABLE "U_PR4_1"."MI_ETC_TEMP" as (
SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT,
    SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,
    SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) total
FROM(
    SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV,
            DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE,
            NVL(SUM(SLICE),0) TOTAL
        FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID
        WHERE SLICE_REQUEST_ID=113
        AND  TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY')
  and PRJ_OBJECT_ID = 5002239
    group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM')
    )
group by PRJ_OBJECT_ID,SUBSTR(YEAR,1,4))

表中结果：逐月一行

结果表 提前感谢您的帮助，

标签： oracle

解决方案

通过更新查询解决的问题（另一个论坛中提供的解决方案）：

  select PR.PRJ_OBJECT_ID
   , to_char(SLICE_DATE, 'YYYY')                                         as YEAR
   , 'ETC'                                                               as UNIT
   , sum(case to_char(SLICE_DATE, 'MM') when '01' then SLICE else 0 end) as JAN
   , sum(case to_char(SLICE_DATE, 'MM') when '02' then SLICE else 0 end) as FEB
   , sum(case to_char(SLICE_DATE, 'MM') when '03' then SLICE else 0 end) as MAR
   , sum(case to_char(SLICE_DATE, 'MM') when '04' then SLICE else 0 end) as APR
   , sum(case to_char(SLICE_DATE, 'MM') when '05' then SLICE else 0 end) as MAY
   , sum(case to_char(SLICE_DATE, 'MM') when '06' then SLICE else 0 end) as JUN
   , sum(case to_char(SLICE_DATE, 'MM') when '07' then SLICE else 0 end) as JUL
   , sum(case to_char(SLICE_DATE, 'MM') when '08' then SLICE else 0 end) as AUG
   , sum(case to_char(SLICE_DATE, 'MM') when '09' then SLICE else 0 end) as SEP
   , sum(case to_char(SLICE_DATE, 'MM') when '10' then SLICE else 0 end) as OCTO
   , sum(case to_char(SLICE_DATE, 'MM') when '11' then SLICE else 0 end) as NOV
   , sum(case to_char(SLICE_DATE, 'MM') when '12' then SLICE else 0 end) as DECE
   , coalesce(sum(SLICE), 0)                                             as TOTAL
from PRJ_BLB_SLICES_M_ETC PR
join PRASSIGNMENT         PA ON PA.PRID = PR.PRJ_OBJECT_ID  where PR.SLICE_REQUEST_ID = 113
 and SLICE_DATE         >= trunc(add_months(sysdate, -3), 'yyyy')
 and PR.PRJ_OBJECT_ID    = 5002239 group by PR.PRJ_OBJECT_ID
   , to_char(SLICE_DATE, 'YYYY');

问题似乎是 Oracle 12 中的一个错误。

推荐阅读