首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >分组依据/排序依据

问分组依据/排序依据
EN

Stack Overflow用户
提问于 2015-10-20 04:45:06
回答 1查看 125关注 0票数 0

Excel Image Reference 我使用此SQL语句按部门显示批准区域以及返回的工作流批准或拒绝计数。我当前的声明如下所示,以及它当前生成的内容,然后是我希望它看起来是什么样子。它与group by和order by有关,但我不知道如何根据需要更改顺序。任何帮助都是非常感谢的。我已经附上了一个图像,以显示当前的结果与期望的结果。

代码语言:javascript
复制
    SELECT DECODE(SUBSTR(P.DESCR,1,18),'Chartfield Signers','Chartfield Signers',P.DESCR), DECODE(U.EOAWSTEP_STATUS,'A','Approval','Denial'), COUNT(U.EOAWSTEP_INSTANCE), TO_CHAR(AVG(
    EXTRACT(DAY FROM (U.EOAWDTTM_MODIFIED - U.DTTM_CREATED) DAY TO SECOND ) +
    (EXTRACT(HOUR FROM (U.EOAWDTTM_MODIFIED - U.DTTM_CREATED) DAY TO SECOND ) / 24) +
    (EXTRACT(MINUTE FROM (U.EOAWDTTM_MODIFIED - U.DTTM_CREATED) DAY TO SECOND ) / (60*24)) +
    (EXTRACT(SECOND FROM (U.EOAWDTTM_MODIFIED - U.DTTM_CREATED) DAY TO SECOND ) / (60*60*24))),'999.99') "DURATION IN DAYS"
    FROM PS_EOAW_STEPINST S  , PS_EOAW_STEP P  , PS_MDA_EPAF_WFXREF T  , PS_EOAW_USERINST U
    WHERE T.EOAWPRCS_ID LIKE 'MDA_EPAF%'
    AND T.EOAWTHREAD_ID = S.EOAWTHREAD_ID
    AND T.EOAWPRCS_ID = S.EOAWPRCS_ID
    AND S.EOAWSTEP_INSTANCE = U.EOAWSTEP_INSTANCE
    AND U.EOAWSTEP_STATUS IN ('A','D')
    AND P.EOAWPRCS_ID = S.EOAWPRCS_ID
    AND P.EOAWDEFN_ID = S.EOAWDEFN_ID
    AND P.EFFDT = S.EFFDT
    AND U.DTTM_CREATED between to_date(:d1, 'dd-mon-yy' )
             and to_date(:d2, 'dd-mon-yy' )
    AND P.EOAWSTAGE_NBR = S.EOAWSTAGE_NBR
    AND P.EOAWPATH_ID = S.EOAWPATH_ID
    AND P.EOAWSTEP_NBR = S.EOAWSTEP_NBR
    GROUP BY DECODE(SUBSTR(P.DESCR,1,18),'Chartfield Signers','Chartfield Signers',P.DESCR),DECODE(U.EOAWSTEP_STATUS,'A','Approval','Denial')
    ORDER BY DECODE(SUBSTR(P.DESCR,1,18),'Chartfield Signers','Chartfield Signers',P.DESCR),DECODE(U.EOAWSTEP_STATUS,'A','Approval','Denial');
EN

回答 1

Stack Overflow用户

发布于 2015-10-21 19:33:18

下面的代码使用初始查询(为了减少长度而省略了大部分查询)作为CTE,然后添加一个pivot部分,这是在它们自己的列中获得批准和拒绝所需的部分。

代码语言:javascript
复制
    --Be sure to put a ; before WITH
--Don't miss the ) at the end of the section

;WITH Main AS (
SELECT DECODE(SUBSTR(P.DESCR,1,18),'Chartfield Signers','Chartfield Signers',P.DESCR) AS Descr
    , DECODE(U.EOAWSTEP_STATUS,'A','Approval','Denial') AS AppDen
    , COUNT(U.EOAWSTEP_INSTANCE) AS CountAD, TO_CHAR(AVG(
    EXTRACT ...
    ...
    GROUP BY Descr, AppDen
/*    ORDER BY DECODE(SUBSTR(P.DESCR,1,18),'Chartfield Signers','Chartfield Signers',P.DESCR)
        ,DECODE(U.EOAWSTEP_STATUS,'A','Approval','Denial')*/
)
--I didn't see anywhere the string Stage was set, but I see it in your desired output.  
--I've just added it as a separate variable

--In your code you have Approvals and Denials in separate records.  You need to pivot
--Approval and Denial to be columns on the same row.  The PIVOT function will do that
--It takes some getting used to.  You should try and become familiar with it online or otherwise

--The two COALESCE functions allow sql to use a 0 in the Approval and Denial columns when there
--is no Approval or Denial row for a given descr.

,PivotTable AS (
SELECT Stage
      ,Descr
      ,[Approval]
      ,[Denial]
  FROM (SELECT Stage, Descr
      ,COALESCE([Approval],0) AS [Approval]
      ,COALESCE([Denial],0) AS [Denial]
      FROM (SELECT Stage, Descr, AppDen, [CountAD]
            FROM Main) AS so
        PIVOT(SUM(CountAD) FOR AppDen IN (
             [Approval]
            ,[Denial]
        )) AS p) AS p
) 

--since the values in duration are not the same for the one example you gave us of
--a descr with both approval and denial, you can't include that in the pivot section.
--I've added this next section to insert the duration of the Approval row. 
--You may need to adjust it for however you want to determine Duration

SELECT pt.Stage
    , pt.Descr
    , pt.Approval
    , pt.Denial
    , m.Duration 
    FROM PivotTable AS pt
        LEFT JOIN Main AS m ON pt.Stage = m.Stage
            AND pt.Descr = m.Descr
            AND m.AppDen = 'Approval'
  ORDER BY pt.Stage, pt.Descr

(我对变量的初始声明使用了别名,以避免在GROUP BY部分中重复代码。如果您对变量的计算发生了更改,您不需要滚动代码来查找需要更改它的所有不同位置。)

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/33223575

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档