Linq多列分组查询,orderby多列排序

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;
}
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

王焜棟琦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值