Excel Image Reference 我使用此SQL语句按部门显示批准区域以及返回的工作流批准或拒绝计数。我当前的声明如下所示,以及它当前生成的内容,然后是我希望它看起来是什么样子。它与group by和order by有关,但我不知道如何根据需要更改顺序。任何帮助都是非常感谢的。我已经附上了一个图像,以显示当前的结果与期望的结果。
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');发布于 2015-10-21 19:33:18
下面的代码使用初始查询(为了减少长度而省略了大部分查询)作为CTE,然后添加一个pivot部分,这是在它们自己的列中获得批准和拒绝所需的部分。
--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部分中重复代码。如果您对变量的计算发生了更改,您不需要滚动代码来查找需要更改它的所有不同位置。)
https://stackoverflow.com/questions/33223575
复制相似问题