linq分组
https://docs.microsoft.com/zh-cn/dotnet/csharp/linq/group-query-results
orderby 排序
https://docs.microsoft.com/zh-cn/dotnet/csharp/language-reference/keywords/orderby-clause
参考代码:
/// <summary>
/// 根据部门分组统计每月一级分类多少钱
/// </summary>
/// <param name="departmentId"></param>
/// <param name="year"></param>
/// <returns></returns>
public async Task<List<MoneyByMonthDepartmentView>> GetMoneyByMonthOfClassfiyDeparetmentAsync(string departmentId, uint year)
{
var query = from q in dbContext.Reward_form_item
join a in dbContext.Appoint_form on q.Form_main_id equals a.Form_main_id into avv
from av in avv.DefaultIfEmpty()
join d in dbContext.Department on av.Department_id equals d.Id into dvv
from dv in dvv.DefaultIfEmpty()
where q.Parent_column_id == null && av.Order_month != null && av.Createtime.Value.Year== year && av.Department_id== departmentId
group new { q, av, dv } by new { q.Column_n,q.Money, av.Order_month, dv.Department_name, av.Department_id } into b
orderby b.Key.Department_id ascending, b.Key.Order_month ascending
select new MoneyByMonthDepartmentView() {
Column_n=b.Key.Column_n,
Department_id=b.Key.Department_id,
Department_name=b.Key.Department_name,
Money=b.Key.Money,
Order_month=b.Key.Order_month
};
var list = await query.ToListAsync();
return list;
}
/// <summary>
/// 分类金额
/// </summary>
/// 2020-11-11 12:28:23 添加
public class MoneyByMonthDepartmentView
{
/// <summary>
/// 一级分类名称
/// </summary>
public string Column_n { get; set; }
/// <summary>
/// 奖励金额
/// </summary>
public decimal Money { get; set; }
/// <summary>
/// 奖励单月份
/// </summary>
public string Order_month { get; set; }
/// <summary>
/// 部门名称
/// </summary>
public string Department_name { get; set; }
public string Department_id { get; set; }
}
Linq相同效果,类似的sql server语句查询
select a.column_n,sum(a.money) as monty,f.order_month,d.department_name,f.department_id
FROM [reward_form_items] a
left join appoint_form f on f.form_main_id =a.form_main_id
left join department d on f.department_id=d.id
where a.parent_column_id is null and order_month is not null
group by a.column_n,f.order_month,d.department_name,f.department_id
order by f.department_id,order_month
查询结果
参考分组查询2
查询的源数据如上图
//获取指定月份部门奖励了多少钱
public async Task<List<MonthMoneyView>> GetDepartmentMoneyByMonthAsync(string month)
{
var query = from g in dbContext.Appoint_form.Where(x => x.Is_delete == 0 && x.Order_month == month)
join dv in dbContext.Department on g.Department_id equals dv.Id into dvv
from d in dvv.DefaultIfEmpty()
group g by new { d.Department_name, g.Order_month} into m
select new MonthMoneyView()
{
Month = m.Key.Department_name,//部门名称
Money =m.Sum(x=>x.Reward_money)//月累计金额
};
var list = await query.OrderByDescending(x=>x.Money).ToListAsync();
return list;
}