首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何在同一个select查询中按原始结果和返回结果进行分组?

问如何在同一个select查询中按原始结果和返回结果进行分组?
EN

Stack Overflow用户
提问于 2016-08-01 02:28:39
回答 1查看 22关注 0票数 0

如何在同一个select查询中按原始结果和返回结果进行分组?

我有一个表#t,它在工作流周期中通过了Bill Id和error's。一个帐单Id可能多次经历相同的错误类型或不同的错误类型。

我想为每个账单id选择不同的错误类型,并获得Group By error计数。

根据不同的错误,错误计数,我需要在同一个表中获得错误类型计数和错误类型。

我不知道该怎么做。我在这篇文章中保存了示例查询和预期结果。

代码语言:javascript
复制
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

预期输出

代码语言:javascript
复制
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
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-08-01 03:05:21

您可以获得按StepName分组的BillId和ErrorTypeCount的StepExec的不同计数。然后只需使用窗口函数对每个StepName计数求和

代码语言:javascript
复制
SELECT  StepName ,
        SUM(COUNT(DISTINCT BillId)) OVER (PARTITION BY StepName) TotalStepError,
        StepExec ,
        COUNT(DISTINCT BillId) ErrorTypeCount
FROM    #t
WHERE   StepExecResult = 'Error'
GROUP BY StepName,
        StepExec
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/38687148

复制
相关文章

相似问题

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