具有分组依据的动态列值（sql server 和 Linq）

发布于 2024-12-20 22:45:00 字数 659 浏览 3 评论 0原文

我有一个表格Plan，其中包含以下示例数据
在此处输入图像描述
我想按 PlanMonth 汇总结果，对于 PlanStatus，我希望如果其任何（在组中）值是 Drafted，我会在结果中起草，否则 Under Approval 。我已经使用以下查询完成了此操作，

select PlanMonth, case when Flag=1 then 'Drafted' else 'Under Approval' end as PlanStatus
from 
(select p.PlanMonth, Max(CASE WHEN p.PlanStatus = 'Drafted' THEN 1 ELSE 0 END) Flag 
from Plans p 
group by p.PlanMonth
  ) inquery

我已查阅这篇博文< /a>.难道是有什么问题吗？此外，如果有人能帮我将其翻译成 linq 我将不胜感激

原文

i have a table Plan with following sample data
enter image description here
i want to aggregate the result by PlanMonth and for PlanStatus i want that if any of its (in a group) values is Drafted i get drafted in the result and Under Approval otherwise. i have done it using following query

select PlanMonth, case when Flag=1 then 'Drafted' else 'Under Approval' end as PlanStatus
from 
(select p.PlanMonth, Max(CASE WHEN p.PlanStatus = 'Drafted' THEN 1 ELSE 0 END) Flag 
from Plans p 
group by p.PlanMonth
  ) inquery

i have consulted this blog post. Is there something wrong with it? Moreover, if someone can help me translate it to linq i will be grateful

分享到QQ

分享到微博

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

发布评论

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

花间憩 2024-12-27 22:45:00

您的查询将会起作用。

根据您提供的示例数据，可以稍微简化一下。

select p.PlanMonth,
       min(p.PlanStatus) as PlanStatus
from Plans as p
group by p.PlanMonth

如果 PlanStatus 中的值按字母顺序排序在 Drafted 之前，则此方法将不起作用。

The query you have will work.

With the sample data you have provided it can be simplified a bit.

select p.PlanMonth,
       min(p.PlanStatus) as PlanStatus
from Plans as p
group by p.PlanMonth

This will not work if you have values in PlanStatus that is alphabetically sorted before Drafted.

回复收藏 0 原文

疾风者 2024-12-27 22:45:00

以下 Linq 查询对我有用

 return (from p in db.mktDrVisitPlans                        

                    group p by p.PlanMonth into grop
                    select
                    new VMVisitPlan
                    {
                        PlanMonth = grop.Key
                        PlanStatus = grop.Any(x=>x.p.PlanStatus == "Drafted")?"Hold":"Under Approval",

                    }).ToList();

Following Linq query worked for me

 return (from p in db.mktDrVisitPlans                        

                    group p by p.PlanMonth into grop
                    select
                    new VMVisitPlan
                    {
                        PlanMonth = grop.Key
                        PlanStatus = grop.Any(x=>x.p.PlanStatus == "Drafted")?"Hold":"Under Approval",

                    }).ToList();

回复收藏 0 原文

~没有更多了~