+关注继续查看

# Group By/Having操作符

## 1.简单形式：

var q =
from p in db.Products
group p by p.CategoryID into g
select g;

var q =
from p in db.Products
group p by p.CategoryID;

foreach (var gp in q)
{
if (gp.Key == 2)
{
foreach (var item in gp)
{
//do something
}
}
}

## 2.Select匿名类：

var q =
from p in db.Products
group p by p.CategoryID into g
select new { CategoryID = g.Key, g }; 

foreach (var gp in q)
{
if (gp.CategoryID == 2)
{
foreach (var item in gp.g)
{
//do something
}
}
}

## 3.最大值

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
MaxPrice = g.Max(p => p.UnitPrice)
};

## 4.最小值

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
MinPrice = g.Min(p => p.UnitPrice)
};

## 5.平均值

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
AveragePrice = g.Average(p => p.UnitPrice)
};

## 6.求和

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
TotalPrice = g.Sum(p => p.UnitPrice)
};

## 7.计数

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
NumProducts = g.Count()
};

## 8.带条件计数

var q =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
NumProducts = g.Count(p => p.Discontinued)
};

## 9.Where限制

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

## 10.多列(Multiple Columns)

var categories =
from p in db.Products
group p by new
{
p.CategoryID,
p.SupplierID
}
into g
select new
{
g.Key,
g
};

## 11.表达式(Expression)

var categories =
from p in db.Products
group p by new { Criterion = p.UnitPrice > 10 } into g
select g;

# Any

## 1.简单形式：

var q =
from c in db.Customers
where !c.Orders.Any()
select c;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],
[t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE NOT (EXISTS(
SELECT NULL AS [EMPTY] FROM [dbo].[Orders] AS [t1]
WHERE [t1].[CustomerID] = [t0].[CustomerID]
))

## 2.带条件形式：

var q =
from c in db.Categories
where c.Products.Any(p => p.Discontinued)
select c;

SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description],
[t0].[Picture] FROM [dbo].[Categories] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY] FROM [dbo].[Products] AS [t1]
WHERE ([t1].[Discontinued] = 1) AND
([t1].[CategoryID] = [t0].[CategoryID])
)

# All

## 1.带条件形式

var q =
from c in db.Customers
where c.Orders.All(o => o.ShipCity == c.City)
select c;

# Contains

string[] customerID_Set =
new string[] { "AROUT", "BOLID", "FISSA" };
var q = (
from o in db.Orders
where customerID_Set.Contains(o.CustomerID)
select o).ToList();

var q = (
from o in db.Orders
where (
new string[] { "AROUT", "BOLID", "FISSA" })
.Contains(o.CustomerID)
select o).ToList();

Not Contains则取反：

var q = (
from o in db.Orders
where !(
new string[] { "AROUT", "BOLID", "FISSA" })
.Contains(o.CustomerID)
select o).ToList();

## 1.包含一个对象：

var order = (from o in db.Orders
where o.OrderID == 10248
select o).First();
var q = db.Customers.Where(p => p.Orders.Contains(order)).ToList();
foreach (var cust in q)
{
foreach (var ord in cust.Orders)
{
//do something
}
}

## 2.包含多个值：

string[] cities =
new string[] { "Seattle", "London", "Vancouver", "Paris" };
var q = db.Customers.Where(p=>cities.Contains(p.City)).ToList();

 Group By/Having 分组数据；延迟 Any 用于判断集合中是否有元素满足某一条件；不延迟 All 用于判断集合中所有元素是否都满足某一条件；不延迟 Contains 用于判断集合中是否包含有某一元素；不延迟

## Count

### 1.简单形式：

var q = db.Customers.Count();

### 2.带条件形式：

var q = db.Products.Count(p => !p.Discontinued);

## LongCount

var q = db.Customers.LongCount();

## Sum

### 1.简单形式：

var q = db.Orders.Select(o => o.Freight).Sum();

### 2.映射形式：

var q = db.Products.Sum(p => p.UnitsOnOrder);

## Min

### 1.简单形式：

var q = db.Products.Select(p => p.UnitPrice).Min();

### 2.映射形式：

var q = db.Orders.Min(o => o.Freight);

### 3.元素：

var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
CategoryID = g.Key,
CheapestProducts =
from p2 in g
where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
select p2
};

## Max

### 1.简单形式：

var q = db.Employees.Select(e => e.HireDate).Max();

### 2.映射形式：

var q = db.Products.Max(p => p.UnitsInStock);

### 3.元素：

var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
MostExpensiveProducts =
from p2 in g
where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
select p2
};

## Average

### 1.简单形式：

var q = db.Orders.Select(o => o.Freight).Average();

### 2.映射形式：

var q = db.Products.Average(p => p.UnitPrice);

### 3.元素：

var categories =
from p in db.Products
group p by p.CategoryID into g
select new {
g.Key,
ExpensiveProducts =
from p2 in g
where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
select p2
};

3分钟完成网站搭建

7 0
MySQL模糊搜索的几种姿势

7 0

8 0
SwiftUI 开源项目 - ZYSwiftUIFrame 自带服务端的完整示例项目（更新中...）

4 0

6 0
“飞天加速计划·高校学生在家实践”学习心得

7 0
ECS服务器体验

21 0

9 0
Web 基础——Tomcat
Tomcat 是一个开源的开放源代码的 Web 应用服务器，属于轻量级应用服务器，在中小型系统和并发访问用户不是很多的场合下被普遍使用，是开发和调式 JSP 程序的首选。官方：https://tomcat.apache.org
9 0
+关注

10427

2

JS零基础入门教程（上册）