Take(1).PartitionBy(d=>d.Id)
//完整查询demo如下:
result = await _repDrives.AsQueryable()
.LeftJoin<ClientUser>((d, u) => d.UserId == u.Id)
.LeftJoin<Car>((d, u, c) => c.UserId == d.UserId)
.LeftJoin<UserExtension>((d, u, c, uc) => u.Id == uc.UserId)
.WhereIF(!string.IsNullOrEmpty(input.FullName), ((d, u, c, uc) => u.FullName.Contains(input.FullName)))
.WhereIF(input.Id > 0, ((d, u, c, uc) => d.Id == input.Id))
.WhereIF(!string.IsNullOrEmpty(input.Account), ((d, u, c, uc) => u.Account == input.Account))
.WhereIF(input.IsOnline >= 0, ((d, u, c, uc) => uc.IsOnLine == Convert.ToBoolean(input.IsOnline)))
.WhereIF(!string.IsNullOrEmpty(input.PhoneNo), ((d, u, c, uc) => u.PhoneNo == input.PhoneNo))
.WhereIF(input.CarStatus > 0, ((d, u, c, uc) => c.Status == input.CarStatus))
.WhereIF(!string.IsNullOrEmpty(input.MerchantId), ((d, u, c, uc) => (d.MerchantId == Convert.ToInt64(input.MerchantId)) && (input.MerchantId != "0")))
.WhereIF(input.AuditResults > 0, ((d, u, c, uc) => d.AuditResults == input.AuditResults))
.Where((d, u, c, uc) => d.CreateTime >= input.BeginDt && d.CreateTime <= input.EndDt)
.Where((d, u, c, uc) => d.IsDelete == false)
.OrderByDescending((d, u, c) => d.CreateTime)
.Select((d, u, c, uc) => new DriveListOutputDto
{
Id = d.Id,
//用户ID
UserId = u.Id,
//司机ID
DriverId = d.Id,
//帐号ID
Account = u.Account,
//头像
Avatar = u.Avatar,
//名字
FullName = string.IsNullOrEmpty(u.FirstName + u.LastName) == true ? "暂无数据" : u.FirstName + u.LastName,
//电话号码
PhoneNo = u.PhoneNo,
//司机评分
DriverScore = uc.DriverScore,
//注册时间
RegistTime = d.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"),
//上线状态
IsOnline = uc.IsOnLine,
//账户状态
IsEnabled = uc.IsEnabled,
//车辆状态
CarStatus = c.Status,
//司机所属商户Id
MerchantId = d.MerchantId,
}).Take(1).PartitionBy(d => d.Id)
.ToPagedListAsync(input.Index, input.Limit);
//生成mysql查询脚本如下:
SELECT `d`.`id` AS `Id` , `u`.`id` AS `UserId` , `d`.`id` AS `DriverId` , `u`.`account` AS `Account` , `u`.`avatar` AS `Avatar` ,
( CASE WHEN (( CASE WHEN ( concat(`u`.`first_name`,`u`.`last_name`) IS NULL OR concat(`u`.`first_name`,`u`.`last_name`) ='') THEN 1 ELSE 0 END ) = 1 ) THEN N'暂无数据' ELSE concat(`u`.`first_name`,`u`.`last_name`) END ) AS `FullName` ,
`u`.`phone_no` AS `PhoneNo` , `uc`.`driver_score` AS `DriverScore` , DATE_FORMAT(`d`.`create_time`, '%Y-%m-%d %H:%i:%S') AS `RegistTime` , `uc`.`is_online` AS `IsOnline` , `uc`.`is_enabled` AS `IsEnabled` , `c`.`status` AS `CarStatus` ,
`d`.`merchant_id` AS `MerchantId`
FROM `driver_info` `d`
Left JOIN `client_user` `u` ON ( `d`.`user_id` = `u`.`id` )
Left JOIN `car_info` `c` ON ( `c`.`user_id` = `d`.`user_id` )
Left JOIN `client_user_extension` `uc` ON ( `u`.`id` = `uc`.`user_id` )
WHERE ( `uc`.`is_online` = CAST(0 AS SIGNED)) AND (( `d`.`create_time` >= '1970-01-01 00:00:00.000' ) AND ( `d`.`create_time` <= '2100-01-01 00:00:00.000' )) AND ( `d`.`is_delete` = 0 )
GROUP BY `d`.`Id` ORDER BY `d`.`create_time` DESC LIMIT 0,10
SqlSugar 查询列表去重
最新推荐文章于 2024-09-06 17:25:54 发布