分组/有问题 [英] Group By/Having Problems

问题描述

<预LANG = "X-SQL"> SELECT DocentShiftSchedule.DocentAssigned，COUNT（*）AS NumShifts，DocentInfo。[MaxDaysPerMonth]
。从（ScheduledEvent RIGHT JOIN DocentShiftSchedule
关于ScheduledEvent.EventId = DocentShiftSchedule .EventId）
INNER JOIN DocentInfo ON DocentShiftSchedule.DocentAssigned = DocentInfo。[索引]
，其中（（月（[ScheduledEvent] [天]）= 6）和（年份（[ScheduledEvent]。[日]）= 2010））
GROUP BY DocentShiftSchedule.DocentAssigned，DocentInfo.MaxDaysPerMonth，DocentInfo.MaxDaysPerMonth
。具有（计数（*）> = [DocentInfo] [MaxDaysPerMonth]）

如何在LINQ C＃中执行此操作？特别是，我遇到了"Group By"的问题。和"有'部分。我现在正在这里：

来自se在ScheduledEvents中
加入dss在DocentShiftSchedules上se.EventId等于dss.EventId 
加入d在dss.DocentAssigned中的DocentInfo等于d.Index 
其中se.Day> = new DateTime（2010,6,1）&& 
 se.Day< =新日期时间（2010年，6，30）
的OrderBy d.Index 
选择新的
 {
 DIndex = d.Index，
 MaxDays = d.MaxDaysPerMonth 
} 

解决方案

您好ZooGuy1492，

 

欢迎来到LINQ to SQL论坛。

根据您的描述，关于群组有一个专家的博客谈论它们，请看这个：

在查询LINQ时，有时我们将不得不使用group By子句。但很多时候我们也想在LINQ中使用SQL的having子句和group by子句。在LINQ中没有直接的关键字来执行此操作我们需要使用where
子句本身。

您可以查询LINQ to SQL以同时具有Group By和Having这个。

var categories =   来自db.Products中的p

        &NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;将p.CategoryID分组为g <
           &NBSP;&NBSP;&NBSP;其中g.Count（）> = 10

           &NBSP;&NBSP;&NBSP;选择新的{

              &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; g.Key，

              &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ProductCount = g.Count（）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; };

但有时您可能不会使用现有列进行分组，但会使用在运行时本身计算的列。对于这些条件，您可以像这样编写查询。

var categories =    from db.Products中的p

 

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP; group by new {Criterion = p.UnitPrice> 10}为克

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;其中g.Count（）> = 10

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;选择新{

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; g.Key，&
                    &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; ProductCount = g.Count（）

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP; }; 

我希望这可以帮到你。

 

祝你有个美好的一天，

SELECT DocentShiftSchedule.DocentAssigned, Count(*) AS NumShifts, DocentInfo.[MaxDaysPerMonth]
 FROM (ScheduledEvent RIGHT JOIN DocentShiftSchedule
 ON ScheduledEvent.EventId = DocentShiftSchedule.EventId)
 INNER JOIN DocentInfo ON DocentShiftSchedule.DocentAssigned = DocentInfo.[Index]
 WHERE ((Month([ScheduledEvent].[Day])=6) AND (Year([ScheduledEvent].[Day])=2010))
 GROUP BY DocentShiftSchedule.DocentAssigned, DocentInfo.MaxDaysPerMonth, DocentInfo.MaxDaysPerMonth
 HAVING (Count(*)>=[DocentInfo].[MaxDaysPerMonth])

How do I do this in LINQ C#? In particular, I'm having trouble with the "Group By" and "Having' part. I'm currently at this:

from se in ScheduledEvents
join dss in DocentShiftSchedules on se.EventId equals dss.EventId
join d in DocentInfo on dss.DocentAssigned equals d.Index
where se.Day >= new DateTime(2010, 6, 1) &&
se.Day <= new DateTime(2010, 6, 30) 
orderby d.Index
select new
{
DIndex = d.Index,
MaxDays = d.MaxDaysPerMonth
}

解决方案

Hello ZooGuy1492,

 

Welcome to the LINQ to SQL Forum.

According to your description, about Group By and Having there is a expert's blog talking about them, please see this:

While querying with LINQ, some times we will have to use the group By clause. But many a times we also want to use the having clause of SQL with the group by clause in the LINQ. There is no direct having keyword in LINQ to do this we need to use the where clause itself.

You can query the LINQ to SQL to have both Group By and Having like this.

var categories =   from p in db.Products
               group p by p.CategoryID into g
               where g.Count() >= 10
               select new {
                      g.Key,
                      ProductCount = g.Count()
                     };

But there are occasion when you might not to group with an existing column but with a column which calculated at the run time itself. For those conditions you can write the query like this.

var categories =   from p in db.Products
 
                          group p by new { Criterion = p.UnitPrice > 10 } into g
                           where g.Count() >= 10
                           select new {
                                    g.Key,
                                    ProductCount = g.Count()
                                   }; 

I hope this can help you.

 

Have a ncie day,

这篇关于分组/有问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！