所以我正在编写一个查询,从我的表中获取前 10 个计数并显示总数。我想要的是让它把它拆分为"失败"和"成功"。
所以我的表格显示
Total Groups
58 Group 5
32 Group 77
32 Group 78
31 Group 99
29 Group 89
29 Group 34
26 Group 57
15 Group 54
12 Group 25
11 Group 15
因此,如果您在第 5 组中看到总共有 58 条记录。但在这 58 个中,有 53 个是"成功"的,5 个是"失败的"。
我无法搜索失败的帐户并输入前 10 名,因为这样计数会更低,前 10 名会有所不同。
SELECT top 10 Count(distinct FileID) AS Total, Groups
FROM Table
where Date = '2020-05-28'
group by Group
order by Total DESC
这是我的问题。我希望它显示我显示的总数,然后还有两列具有成功计数和失败计数。我不知道该怎么做那部分。
所以它应该看起来像
Failed Success Total Groups
53 5 58 Group 5
5 27 32 Group 77
如果我像这样执行查询,它会给我一个不同的前 10 名,因为失败或成功计数较低。
SELECT top 10 Count(distinct FileID) AS Total, Groups
FROM Table
where Date = '2020-05-28' AND Status = 'Success'
group by Group
order by Total DESC
这是我的查询,我得到了超级接近,但要么总计双倍给出它应该更高的数字。只有没有成分的记录才是正确的。
SELECT top 10 Count(distinct FileID) AS Total, Groups,
SUM(CASE WHEN status = 'Success' AND Date = '2020-05-28' THEN 1 ELSE 0 END) Success
FROM Table
group by Group
order by Total DESC
这就是结果
Total Group Success
58 Group 5 110 The correct one for this is 55
32 Group 77 0 The correct one for this is 0
32 Group 78 27 The correct one for this is 27
31 Group 99 9 The correct one for this is 3
29 Group 89 13 The correct one for this is 4
29 Group 34 15 The correct one for this is 4
根据您使用的RDBMS,在oracle,mssql,postgresql等(几乎所有(中都有窗口函数,您可以使用此窗口函数之一ROW_NUMBER()OVER(GROUP BY GROUP ORDER BY TOTAL DESC)
然后小心地将一些情况写进您的选择中,如下所示。
CASE WHEN ... 'SUCCESS' THEN ROW_NUMBER()OVER(GROUP BY GROUP ORDER BY TOTAL DESC)
WHEN ...'FAIL' THEN
ROW_NUMBER()OVER(GROUP BY GROUP ORDER BY TOTAL ASC) END