SqlSugar 3.分组去重

用到的表结构、数据、实体类

方便测试 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`

文档参考：分组去重
文档参考：查询函数