sql - 如何通过子分组获取列数据

Question

我试图通过不同的状态来获得状态百分比，我们有 4 种状态。这是下面列的数据。

MR_ID Sup_ID    Status

1   1   Rejected    
1   1   Accepted    
1   1   Accepted    
1   1   Rejected    
2   2   InProgress  
2   2   InProgress  
2   2   Accepted    
2   2   Fordwarded  

与 MR_ID:1 和 Sup_ID:1 组合一样，“接受”的百分比为 50%，“拒绝”的百分比为 50%

需要写一个sql查询或者存储过程在sql server中查找

没有得到任何线索。

标签： sqlsql-servertsql

Answer 1

您正在寻找一个简单的 group by 语句：

SELECT 
    MR_ID,
    Sup_ID,
    (CAST(SUM(CASE WHEN [Status] ='InProgress' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'InProgress',
    (CAST(SUM(CASE WHEN [Status] ='Fordwarded' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Fordwarded',
    (CAST(SUM(CASE WHEN [Status] ='Accepted' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Accepted',
    (CAST(SUM(CASE WHEN [Status] ='Rejected' THEN 1 ELSE 0 END) AS FLOAT) / CAST(COUNT(*) AS FLOAT)) * 100 'Rejected'
FROM 
    @Table1 t
GROUP BY 
    MR_ID,
    Sup_ID

结果：