PLSQL 上的 ORDER BY 问题

Question

我有一组应该提取的数据，并应按以下顺序准备一份报告

Last Name      First Name      Location
Blake          James           101007
fusio          Wiko            101008 
                     100 Total

FAROMOJU       BANKOLE         303315
Gibbs          Rhonda          303315
Greene         Leette          303315
                     331 Total

......
............

，我们可以看到 3 字母代码 [从位置代码中提取 - CONCAT >(SUBSTR(COLUMN3,3,3),'Total')] 在所有位置设置之后。我刚刚想办法得到报告，除了位置代码降序排列的情况外，它工作得很好。它在将“位置”制成 3 个字母代码后使用“位置”进行排序，而不是使用完整的数字代码。因此，在结果中，降序排列并不能按照我的需要正常工作。请给我一个解决方案，

我附上我的代码，

******************************************************

FUNCTION GET_ACTY_SUMMARY(V_STARTDATE IN DATE,V_ENDDATE IN DATE)
RETURN TEMP_OUTPUT_TABLE PIPELINED IS
CURSOR ACTY_SUMMARY IS

/* The query fetches the report field grouped by First Name,Last Name
and Location.*/

   SELECT
          COLUMN1 AS LASTNAME,
          COLUMN2 AS FIRSTNAME,
          COLUMN3 AS LOCATION,
          SUM(COLUMN4) AS ACTIVITYNM1,
          SUM(COLUMN5) AS ACTIVITYNM2,
          SUM(COLUMN6) AS ACTIVITYNM3,
          SUM(COLUMN7) AS ACTIVITYNM4,
          SUM(COLUMN8) AS ACTIVITYNM5,
          SUM(COLUMN9) AS REG_HRS,
          SUM(COLUMN10) AS OT_HRS,
          SUM(COLUMN11) AS TOTAL_HRS,
          SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM
  TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))
  GROUP BY
   COLUMN1,
   COLUMN2,
   COLUMN3


  UNION

  SELECT NULL,
       NULL,
       CONCAT(SUBSTR(COLUMN3,3,3),'Total'),
       SUM(COLUMN4) AS ACTIVITYNM1,
       SUM(COLUMN5) AS ACTIVITYNM2,
       SUM(COLUMN6) AS ACTIVITYNM3,
       SUM(COLUMN7) AS ACTIVITYNM4,
       SUM(COLUMN8) AS ACTIVITYNM5,
       SUM(COLUMN9) AS REG_HRS,
       SUM(COLUMN10) AS OT_HRS,
       SUM(COLUMN11) AS TOTAL_HRS,
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))
  GROUP BY COLUMN3
  UNION

  SELECT NULL,
       NULL,
       'Total',
       SUM(COLUMN4) AS ACTIVITYNM1,
       SUM(COLUMN5) AS ACTIVITYNM2,
       SUM(COLUMN6) AS ACTIVITYNM3,
       SUM(COLUMN7) AS ACTIVITYNM4,
       SUM(COLUMN8) AS ACTIVITYNM5,
       SUM(COLUMN9) AS REG_HRS,
       SUM(COLUMN10) AS OT_HRS,
       SUM(COLUMN11) AS TOTAL_HRS,
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE

  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE))


  ORDER BY 3 desc ;

GAS ACTY_SUMMARY%ROWTYPE;

/*Intialize the table output format with NULL.*/

TT TEMP_OUTPUT_FORMAT := INITIALIZE_TABLE_FORMAT();
  BEGIN
  OPEN ACTY_SUMMARY;
  FETCH ACTY_SUMMARY INTO GAS;
    WHILE ACTY_SUMMARY%FOUND LOOP
      BEGIN
        TT.COLUMN1 := GAS.LASTNAME;
        TT.COLUMN2 := GAS.FIRSTNAME;
        TT.COLUMN3 := GAS.LOCATION;
        TT.COLUMN4 := GAS.ACTIVITYNM1;
        TT.COLUMN5 := GAS.ACTIVITYNM2;
        TT.COLUMN6 := GAS.ACTIVITYNM3;
        TT.COLUMN7 := GAS.ACTIVITYNM4;
        TT.COLUMN8 := GAS.ACTIVITYNM5;
        TT.COLUMN9 := GAS.REG_HRS;
       TT.COLUMN10 := GAS.OT_HRS;
       TT.COLUMN11 := GAS.TOTAL_HRS;
       TT.COLUMN12 := GAS.PRODUCTIVITY_PERCENTAGE;
       PIPE ROW(TT);
     END;
    FETCH ACTY_SUMMARY INTO GAS;
   END LOOP;
  CLOSE ACTY_SUMMARY;
  RETURN;
END GET_ACTY_SUMMARY;

END GEHC_ACTY_REPT_PKG;


******************************************************

谢谢 阿南德

Answer 1

我建议您将查询更改为

   SELECT 
          COLUMN1 AS LASTNAME, 
          COLUMN2 AS FIRSTNAME, 
          COLUMN3 AS LOCATION,
          COLUMN3 AS FULL_LOCATION,
          SUM(COLUMN4) AS ACTIVITYNM1, 
          SUM(COLUMN5) AS ACTIVITYNM2, 
          SUM(COLUMN6) AS ACTIVITYNM3, 
          SUM(COLUMN7) AS ACTIVITYNM4, 
          SUM(COLUMN8) AS ACTIVITYNM5, 
          SUM(COLUMN9) AS REG_HRS, 
          SUM(COLUMN10) AS OT_HRS, 
          SUM(COLUMN11) AS TOTAL_HRS, 
          SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  GROUP BY 
   COLUMN1, 
   COLUMN2, 
   COLUMN3
UNION ALL
  SELECT NULL, 
       NULL, 
       CONCAT(SUBSTR(COLUMN3,3,3),'Total'),
       COLUMN3 AS FULL_LOCATION,
       SUM(COLUMN4) AS ACTIVITYNM1, 
       SUM(COLUMN5) AS ACTIVITYNM2, 
       SUM(COLUMN6) AS ACTIVITYNM3, 
       SUM(COLUMN7) AS ACTIVITYNM4, 
       SUM(COLUMN8) AS ACTIVITYNM5, 
       SUM(COLUMN9) AS REG_HRS, 
       SUM(COLUMN10) AS OT_HRS, 
       SUM(COLUMN11) AS TOTAL_HRS, 
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  GROUP BY COLUMN3 
UNION ALL
  SELECT NULL, 
       NULL, 
       'Total',
       'Total' AS FULL_LOCATION,
       SUM(COLUMN4) AS ACTIVITYNM1, 
       SUM(COLUMN5) AS ACTIVITYNM2, 
       SUM(COLUMN6) AS ACTIVITYNM3, 
       SUM(COLUMN7) AS ACTIVITYNM4, 
       SUM(COLUMN8) AS ACTIVITYNM5, 
       SUM(COLUMN9) AS REG_HRS, 
       SUM(COLUMN10) AS OT_HRS, 
       SUM(COLUMN11) AS TOTAL_HRS, 
       SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
  FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
  ORDER BY 4 DESC;

我们在这里尝试做的是将完整位置（数据中的 COLUMN3，如果我正确读取原始查询）添加到查询中，以便排序可以基于它。您可能需要更改 GROUP BY - 我不确定，并且没有可用于测试的数据。但这是基本的想法。

分享并享受。

Answer 2

我建议查看分组集，而不是使用联合数据。分组集（以及 CUBE 和 ROLLUP ）是在多个级别聚合数据的方法，这就是您似乎正在做的事情。使用分组集将获得您正在寻找的数据。

为了对数据进行排序，您需要向 order by 子句添加其他列。我使用的 grouping_id() 函数返回一个数字（0 或 1），具体取决于它是否是给定表达式的“超级聚合”行。这些“超级聚合”行是与总计和小计相关的附加行。我正在对数据以及该列是否是超级聚合进行排序。

认为你应该能够做类似的事情：

SELECT 
  COLUMN1 AS LASTNAME, 
  COLUMN2 AS FIRSTNAME, 
  case 
    when grouping_id(column3, substr(column3,3,3)) = 3 then 'Total'
    when grouping_id(column3, substr(column3,3,3)) = 2 
      then substr(column3,3,3) ||' Total'
    else column3 end as location,
  SUM(COLUMN4) AS ACTIVITYNM1, 
  SUM(COLUMN5) AS ACTIVITYNM2, 
  SUM(COLUMN6) AS ACTIVITYNM3, 
  SUM(COLUMN7) AS ACTIVITYNM4, 
  SUM(COLUMN8) AS ACTIVITYNM5, 
  SUM(COLUMN9) AS REG_HRS, 
  SUM(COLUMN10) AS OT_HRS, 
  SUM(COLUMN11) AS TOTAL_HRS, 
  SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE 
FROM TABLE(ACTY_COLUMN(V_STARTDATE,V_ENDDATE)) 
GROUP BY grouping sets ((),  (substr(column3,3,3)), 
    (COLUMN1,COLUMN2,COLUMN3, substr(column3,3,3)))
order by 
  grouping_id(substr(column3,3,3)), 
  substr(column3,3,3) desc, 
  grouping_id(column3, substr(column3,3,3)), 
  column3 desc

希望跑得更快并做你想做的事。我可能使 order by 子句变得比必要的更复杂，但它应该满足您的需要。