如何用SQL与LINQ实现用户任务完成情况的行转列统计

SQL 语句实现 #

这是典型的行转列统计需求，通过条件聚合即可实现，SQL 写法如下：

SELECT 
    CASE WHEN isDone = 'y' THEN 'done' ELSE 'undone' END AS nCol,
    SUM(CASE WHEN xuser = 'tom' THEN 1 ELSE 0 END) AS tom,
    SUM(CASE WHEN xuser = 'jeff' THEN 1 ELSE 0 END) AS jeff
FROM 你的表名
GROUP BY CASE WHEN isDone = 'y' THEN 'done' ELSE 'undone' END
ORDER BY nCol DESC

注：将上述语句中的你的表名替换为实际的表名即可执行。

LINQ 语法实现 #

假设对应表的实体类名为Activity，包含Id、Activity、Xuser、IsDone四个属性，实现代码如下：

// 针对内存集合的LINQ to Objects写法，EF/EF Core等ORM查询写法基本一致
var statResult = activityList
    .GroupBy(a => a.IsDone == "y" ? "done" : "undone")
    .Select(g => new 
    {
        nCol = g.Key,
        tom = g.Count(item => item.Xuser == "tom"),
        jeff = g.Count(item => item.Xuser == "jeff")
    })
    .OrderByDescending(x => x.nCol)
    .ToList();

内容的提问来源于stack exchange，提问作者Haminteu