MS SQL 查询按状态列出计数

Question

我想达到下面的结果。我想列出状态为“待批准”的所有记录以及此条件：

例如，对于用户名“Leo”

if ProcedureLevel="Propose" then count all userId of 'Leo' from ProposedCheckBy field 
if ProcedureLevel="Endorse" then count all userId of 'Leo' from EndorsedCheckBy field 
if ProcedureLevel="Approve" then count all userId 'Leo' from ApprovedCheckBy field 

，然后将所有具有“待批准”状态（即检查器为“Leo”）的状态与上述条件相加

I want to achieved the result like this
-----------------------------------------
Username   | No of For Approval  |
Leo        |   3                 |
Taurus     |   2                 | 
Capricorn  |   1                 | 
-----------------------------------------
Tables

Users
Id, userName

Limits

Id, [Description] ,[status],[procedurelevel] checkbyby1,checkbyby2, checkby3

Users

Id   [UserName]
1    Leo
2    Taurus
3    Capricorn

Limits

Id, [Description] ,[status]       ,[procedurelevel] ProposedCheckedBy1 ,EndorsedCheckedBy2 , ApprovedCheckBy3

1     Limits1       For Approval    Propose         1                      null                null
2     Limits2       For Approval    Propose         1                      null                null
3     Limits3       For Approval    Endorse         1                      2                   null  
4     Limits4       For Approval    Approve         1                      2                   1  
5     Limits5       For Approval    Approve         2                      3                   2
5     Limits5       For Approval    Approve         1                      2                   3

Answer 1

我想这就是你所追求的；如果不是，我已经创建了表格和数据插入，这将帮助任何为您提供最终解决方案的人。我认为合并就是你正在寻找的东西；使用状态 1、2、3 列，您可以一次向后查找当前用户。

目前它返回

NoMatches   procedurelevel  userName
1           Approve     Leo
1           Endorse     Taurus
1           Propose         Capricorn
2           Propose     Leo
1           Propose         Taurus



Create table tblUsers
(
Id int, userName varchar(100)
)

Create table tblLimits

(
Id int, [Description] varchar(20),[status] varchar(20),[procedurelevel] varchar(20), checkby1 int,checkby2 int, checkby3 int
)

insert into tblUsers select 1, 'Leo'
insert into tblUsers select 2, 'Taurus'
insert into tblUsers select 3, 'Capricorn'



insert into tblLimits select 1, 'Limits1', 'For Approval', 'Propose',1, null,null
insert into tblLimits select 2, 'Limits2', 'For Approval', 'Propose',1, null, null
insert into tblLimits select 3, 'Limits3', 'For Approval', 'Endorse',1, 2,null  
insert into tblLimits select 4, 'Limits4', 'For Approval', 'Approve',1, 2,1  
insert into tblLimits select 5, 'Limits5', 'For Approval', 'Propose',2, 3,2
insert into tblLimits select 5, 'Limits5', 'For Approval', 'Propose',1, 2,3


select count(ProcedureLevel) as NoMatches, procedurelevel, bb.userName
From tbllimits aa
inner join tblUsers bb
on coalesce(checkby3, checkby2, checkby1) = bb.id
group by bb.userName, aa.procedurelevel

drop table tblusers
drop table tbllimits

Answer 2

这是我的答案，问题是 Leo 有 2 次否决，而不是 3 次。如果我错了，请纠正

WITH UserAsignedLimits AS (SELECT ProposedCheckedBy AS Id
    , COUNT(ProposedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Propose'
GROUP BY ProposedCheckedBy
UNION ALL
SELECT EndorsedCheckedBy AS Id
    , COUNT(EndorsedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Endorse'
GROUP BY EndorsedCheckedBy
UNION ALL
SELECT ApprovedCheckedBy AS Id
    , COUNT(ApprovedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Approve'
GROUP BY ApprovedCheckedBy)

SELECT ID, COUNT(NoCheckBy) as NoofForApproval 
FROM UserAsignedLimits
GROUP BY Id