用到的表结构、数据、实体类
方便测试 Demo
Student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentId` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`SchoolId` int(11) NOT NULL,
PRIMARY KEY (`StudentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `student` VALUES (1, 'Zzr', 1);
INSERT INTO `student` VALUES (2, 'Ls', 2);
INSERT INTO `student` VALUES (3, 'Wem', 3);
INSERT INTO `student` VALUES (4, 'Hpf', 4);
INSERT INTO `student` VALUES (5, 'Zzr', 5);
INSERT INTO `student` VALUES (6, 'hpf', 2);
INSERT INTO `student` VALUES (7, 'hpf', 2);
INSERT INTO `student` VALUES (9, 'hpf', 2);
INSERT INTO `student` VALUES (11, 'qwe', 0);
INSERT INTO `student` VALUES (12, 'qwe', 0);
public class Student
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int StudentId { get; set; }
public string Name { get; set; }
public int SchoolId { get; set; }
[Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是Student类里面的
public School School { get; set; } //不能赋值
}
Order
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Price` decimal(10, 0) NOT NULL,
`CreateTime` datetime NULL DEFAULT NULL,
`CustomId` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
INSERT INTO `order` VALUES (1, 'Cs1', 1, '2022-01-01 00:05:28', 1);
INSERT INTO `order` VALUES (2, 'Cs2', 2, '2022-02-02 00:05:42', 3);
INSERT INTO `order` VALUES (3, 'Cs3', 3, '2022-03-03 00:05:48', 4);
INSERT INTO `order` VALUES (4, 'Cs4', 4, '2022-03-04 00:05:55', 5);
INSERT INTO `order` VALUES (5, 'Cs5', 5, '2022-03-04 00:05:55', 6);
public class Order
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
[SugarColumn(IsNullable = true)]
public DateTime CreateTime { get; set; }
[SugarColumn(IsNullable = true)]
public int CustomId { get; set; }
}
1.普通分组查询(用where)
var list1 = db.Queryable<Student>().Where(it => it.StudentId > 2)
.GroupBy(it => new { it.Name })
.Select(it => new { Sum =SqlFunc.AggregateAvg(it.StudentId), Name = it.Name }).ToList();
//SELECT AVG(`StudentId`) AS `Sum` , `Name` AS `Name` FROM `Student` WHERE( `StudentId` > '2')GROUP BY `Name`
2.聚合对象条件筛选(用Having)
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用
var list2 = db.Queryable<Student>().GroupBy(it => new { it.Name })
.Having(it => SqlFunc.AggregateCount(it.StudentId) >= 2)
.Select(it => new { Sum =SqlFunc.AggregateAvg(it.StudentId), Name = it.Name }).ToList();
//SELECT AVG(`StudentId`) AS `Sum` , `Name` AS `Name` FROM `Student` GROUP BY `Name` HAVING (COUNT(`StudentId`) >= 2 )
3.Distinct使用
一般用来指定字段去重复,查询不重复的值,去重字段
var list = db.Queryable<Student>().Distinct().Select(it => new {CsName = it.Name }).ToList();
//SELECT DISTINCT `Name` AS `CsName` FROM `Student`
4.分组获取第一条
4.1 MySql、Sqlite写法
var list = db.Queryable<Student>().GroupBy(it => it.Name).Take(1).ToList();
//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student` GROUP BY `Name` LIMIT 0,1
4.2 SqlServer、Oracle写法
var list = db.Queryable<Order>().Take(1).PartitionBy(it=>it.Name).ToList()
//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student` GROUP BY `Name` LIMIT 0,1
5.按照年月日分组
按照年、月分组，用 Select 投影出一个匿名对象类型
var list = db.Queryable<Order>()
.Select(it => new
{
name = it.Name,
year = it.CreateTime.Year,
month = it.CreateTime.Month
})
.MergeTable()
.GroupBy(it => new { it.year, it.month })//对新表进行分组
.Select(it => new
{
date = SqlFunc.MergeString(it.year.ToString(), "-", it.month.ToString()),
year = it.year.ToString(),
month = it.month.ToString(),
count = SqlFunc.AggregateCount(it.name)
}).
ToList();
//SELECT concat(CAST(`year` AS CHAR),'-',CAST(`month` AS CHAR)) AS `date` ,
CAST(`year` AS CHAR) AS `year` ,
CAST(`month` AS CHAR) AS `month` ,
COUNT(`name`) AS `count`
FROM (SELECT `Name` AS `name` , Year(`CreateTime`) AS `year` , Month(`CreateTime`) AS `month` FROM `Order` ) MergeTable
GROUP BY `year`,`month`
- MergeTable()：将查询出来的结果合并成一个新表，如果想查看具体结果，需要加上 .ToList();
- SqlFunc.MergeString()：合并字符串
6.按照日期分组
var list5 = db.Queryable<Order>().Select(it => new {
Id = it.Id,
Name = it.Name,//这儿不能写聚合函数，因没分组
Price = it.Price,
CreateTime = it.CreateTime.Date//只取日期
})
.MergeTable()//将查询结果转成一个表
.GroupBy(it => it.CreateTime)
.Select(it => new { id = SqlFunc.AggregateMax(it.Id),price = SqlFunc.AggregateSum(it.Price), crate = it.CreateTime })
.ToList();
//SELECT MAX(`Id`) AS `id` , SUM(`Price`) AS `price` , `CreateTime` AS `create`
//FROM (SELECT `Id` AS `Id` , `Name` AS `Name` , `Price` AS `Price` , CAST(`CreateTime` AS DATE) AS `CreateTime` FROM `Order` ) MergeTable
//GROUP BY `CreateTime`
