如何在同一个select查询中按原始结果和返回结果进行分组?
我有一个表#t,它在工作流周期中通过了Bill Id和error's。一个帐单Id可能多次经历相同的错误类型或不同的错误类型。
我想为每个账单id选择不同的错误类型,并获得Group By error计数。
根据不同的错误,错误计数,我需要在同一个表中获得错误类型计数和错误类型。
我不知道该怎么做。我在这篇文章中保存了示例查询和预期结果。
create table #t
(
BillId int,
StepName varchar(100),
StepExec varchar(100),
StepExecResult varchar(100),
Created_date datetime
)
insert into #t
values
(1, 'Initiated', 'Taken Place','Pass', getdate()-10),
(1, 'POS', 'Deadlock Error','Error', getdate()-9),
(1, 'POS', 'Processed','Pass', getdate()-9),
(1, 'Merchandise', 'Taken Place','Pass', getdate()-8),
(1, 'verification', 'Webservice call error','Error', getdate()-7),
(1, 'verification', 'Webservice call error','Error', getdate()-6),
(1, 'verification', 'Webservice call','Pass', getdate()-5),
(1, 'verification', 'Webservice Response','Error', getdate()-5),
(1, 'verification', 'Webservice Response','Pass', getdate()-5),
(1, 'verification', 'Timeout Error','Error', getdate()-5),
(1, 'verification', 'Timeout Error','Error', getdate()-5),
(1, 'verification', 'Timeout Error','Error', getdate()-5),
(1, 'verification', '','Pass', getdate()-5),
(1, 'Payment', 'calculationError','Error', getdate()-4),
(1, 'Payment', 'calculationProcessed','Pass', getdate()-3),
(1, 'Completed', 'Archived','Pass', getdate()-1),
(2, 'Initiated', 'Taken Place','Pass', getdate()-10),
(2, 'POS', 'Deadlock Error','Error', getdate()-9),
(2, 'POS', 'Processed','Pass', getdate()-9),
(2, 'Merchandise', 'Taken Place','Pass', getdate()-8),
(2, 'verification', 'Webservice call error','Error', getdate()-7),
(2, 'verification', 'Webservice call error','Error', getdate()-6),
(2, 'verification', 'Webservice call','Pass', getdate()-5),
(2, 'verification', 'Webservice Response','Error', getdate()-5),
(2, 'verification', 'Webservice Response','Pass', getdate()-5),
(2, 'verification', '','Pass', getdate()-5),
(2, 'Payment', 'calculationProcessed','Pass', getdate()-3),
(2, 'Completed', 'Archived','Pass', getdate()-1)
select *
from #t
order by Created_date desc
select *
from #t
where StepExecResult = 'Error'
;With cte as
(
select
*,
row_number() over (partition by Billid,StepExec order by StepExecResult) as rownum
from
#t
where
StepExecResult = 'Error'
)
select *
from cte
where rownum = 1
select
stepname, count(*) as ErrorCount
from
#t
where
StepExecResult = 'Error'
group by
stepname预期输出
StepName TotalStepError ErrorType ErrorTypeCount
--------------------------------------------------------------
Payment 1 calculationError 1
POS 2 Deadlock Error 2
verification 5 Timeout Error 1
verification 5 Webservice Response 2
verification 5 Webservice call error 2发布于 2016-08-01 03:05:21
您可以获得按StepName分组的BillId和ErrorTypeCount的StepExec的不同计数。然后只需使用窗口函数对每个StepName计数求和
SELECT StepName ,
SUM(COUNT(DISTINCT BillId)) OVER (PARTITION BY StepName) TotalStepError,
StepExec ,
COUNT(DISTINCT BillId) ErrorTypeCount
FROM #t
WHERE StepExecResult = 'Error'
GROUP BY StepName,
StepExechttps://stackoverflow.com/questions/38687148
复制相似问题