LINQ to SQL 分组和计数

发布于 2024-12-31 22:33:12 字数 320 浏览 5 评论 0原文

我已经寻找解决方案，但没有类似的。我的问题是我想从数据库中选择数据，按用户 ID 对其进行分组并按状态 ID 对其进行计数

用户

UserID
Name

约会

UserID
ClientID
Status
StartDate

状态可以是活动 = 1，取消 = 2，完成 = 3

这就是我显示结果的方式。

结果表

提前致谢。

原文

I have searched for solution, but nothing similar.
My problem is that I want to select data from database, group it by UserID and Count it by Status id

Users

UserID
Name

Appointments

UserID
ClientID
Status
StartDate

Status can be active=1, canceled=2, done=3

This is how I will display results.

Results table

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

新人笑 2025-01-07 22:33:12

在您的问题中，您说您想要对 UserId 进行分组，但在输出中您显示 Name。此查询将对两者进行分组。您可能想根据您的需要进行调整。

from u in tblUsers
join a in tblAppointments on u.UserID equals a.UserID
group a by new { u.UserID, u.Name } into g
select new
{
    Name     = g.Key.Name,
    Active   = g.Count (x => x.Status == 1),
    Canceled = g.Count (x => x.Status == 2),
    Done     = g.Count (x => x.Status == 3)
}

（如果两个用户具有相同的名称，这将处理这种情况）

In you question you say you want to group on UserId, but in the output you show Name. This query will group on both. You might want to adjust it to your needs.

from u in tblUsers
join a in tblAppointments on u.UserID equals a.UserID
group a by new { u.UserID, u.Name } into g
select new
{
    Name     = g.Key.Name,
    Active   = g.Count (x => x.Status == 1),
    Canceled = g.Count (x => x.Status == 2),
    Done     = g.Count (x => x.Status == 3)
}

(this will handle the case if two users have the same name though)

回复收藏 0 原文