• 比较复杂的SQL转Linq


    仅仅作为自己的学习使用笔记

     eg:

     eg:

    /// <summary>
    /// *************学手段
    /// </summary>
    /// <param name="subjectId"></param>
    /// <param name="secId"></param>
    /// <param name="startDate"></param>
    /// <param name="endDate"></param>
    /// <returns></returns>
    public List<ShowTeacherDevicesResult> GetTeachingDevices(string subjectId, string secId, string startDate, string endDate)
    {
    if (string.IsNullOrEmpty(subjectId) || string.IsNullOrEmpty(secId))
    {
    return null;
    }
    //班级记录
    var classesLogs = _classesLogic.GetClassLog();
    var functionRecords = _guideLearnLogic.GetFunctionRecord();
    //**信息
    var personnel = _personLogic.GetPersonnel().Where(s => s.isdel == 0);
    //**信息
    var subjects = _subjectLogic.GetSubject().Where(s => s.isdel == 0);
    
    if (classesLogs != null)
    {
    var data = from L in classesLogs
    join R in functionRecords on L.cll_id equals R.cll_id into tempR
    from RData in tempR.DefaultIfEmpty()
    
    join P in personnel on L.per_id equals P.per_id into tempP
    from Pdata in tempP.DefaultIfEmpty()
    
    join S in subjects on L.sub_id equals S.sub_id into tempS
    from SData in tempS.DefaultIfEmpty()
    where SData.sub_id == subjectId && Pdata.isdel == 0 && SData.isdel == 0
    select new ShowTeachingDevices
    {
    per_id = Pdata.per_id,
    per_name = Pdata.per_name,
    sub_name = SData.sub_name,
    fcd_code = RData.fcd_code.StartsWith("C") ? "C" : (
    RData.fcd_code.StartsWith("D") ? "D" : "ELSE"),
    
    sub_id = SData.sub_id,
    sec_id = SData.sec_id,
    cll_cdate = L.cll_cdate,
    
    };
    if (!string.IsNullOrEmpty(subjectId))
    {
    data = data.Where(c => c.sub_id == subjectId);
    }
    if (!string.IsNullOrEmpty(secId))
    {
    data = data.Where(c => c.sec_id == secId);
    }
    //时间搜索
    if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
    {
    var formatStartDate = DateTime.Parse(startDate);
    var formatEndDate = DateTime.Parse(endDate);
    data = data.Where(c => c.cll_cdate >= formatStartDate && c.cll_cdate <= formatEndDate);
    }
    /*
    SELECT per_id,per_name, SUM(count_num),fcd_code
    FROM(
    SELECT P.per_id,P.per_name, COUNT(1) AS count_num, CASE WHEN R.fcd_code LIKE 'C%' THEN 'C' WHEN R.fcd_code LIKE 'D%' THEN 'D' ELSE 'ELSE' END fcd_code
    FROM class_log L
    LEFT JOIN function_record R ON R.cll_id=L.cll_id
    LEFT JOIN personnel P ON L.per_id= P.per_id
    LEFT JOIN subject S ON L.sub_id=S.sub_id
    WHERE L.sub_id='95301ab4-1e5f-11e5-b161-1c4bd611580c' AND L.cll_cdate BETWEEN '2016-01-10' AND '2020-01-10'
    GROUP BY P.per_id,R.fcd_code
    ORDER BY L.cll_cdate DESC
    )T
    GROUP BY per_id,fcd_code;
    */
    
    data = data.OrderByDescending(c => c.cll_cdate);
    var groupByData = (from G in data
    group G by new
    {
    G.per_id,
    G.per_name,
    G.fcd_code
    } into G
    select new ShowTeacherDevicesResult
    {
    per_id = G.Key.per_id,
    per_name = G.Key.per_name,
    fcd_code = G.Key.fcd_code,
    count = G.Count()
    }).ToList();
    
    var dataList = (from T in groupByData
    group T by new
    {
    T.per_id,
    T.per_name,
    T.fcd_code
    } into Td
    let sumCount = Td.Where(c => c.per_id == Td.Key.per_id && c.fcd_code == Td.Key.fcd_code).Sum(c => c.count)
    select new ShowTeacherDevicesTemp
    {
    per_id = Td.Key.per_id,
    per_name = Td.Key.per_name,
    fcd_code = Td.Key.fcd_code,
    count = sumCount
    }).ToList();
    if (dataList != null && dataList.Count > 0)
    {
    List<ShowTeacherDevicesResult> result = new List<ShowTeacherDevicesResult>();
    foreach (ShowTeacherDevicesTemp item in dataList)
    {
    ShowTeacherDevicesResult r = new ShowTeacherDevicesResult();
    r.per_id = item.per_id;
    r.per_name = item.per_name;
    r.fcd_code = item.fcd_code=="C"?"富媒体":(item.fcd_code=="D"?"教学工具":"互动工具");
    int pcount = dataList.Where(c => c.per_id == item.per_id).Count();//多少个分组
    int itemCount = item.count;
    int totalCount = item.count;
    if (pcount >= 2)
    totalCount = dataList.Where(c => c.per_id == item.per_id).Sum(c => c.count);
    r.count = itemCount;
    r.perfentRate = $"{(itemCount / totalCount * 1.0 * 100).ToString("f2")}%";
    result.Add(r);
    }
    return result;
    }
    
    }
    
    return null;
    }
    
    /// <summary>
    /// *****学准备
    /// </summary>
    /// <param name="subjectId"></param>
    /// <param name="secId"></param>
    /// <param name="startDate"></param>
    /// <param name="endDate"></param>
    /// <returns></returns>
    public async Task<List<ShowTeacherCourseResult>> GetTeachingPreparation(string subjectId, string secId, string startDate, string endDate)
    {
    if (string.IsNullOrEmpty(subjectId))
    {
    return null;
    }
    var classLogic = _classesLogic.GetClassLog();
    var courseLogic = _courseLogic.GetCourse().Where(c => c.isdel == 0);
    var functionRecordLogic = _guideLearnLogic.GetFunctionRecord();
    var personnelLgic = _personLogic.GetPersonnel().Where(c => c.isdel == 0);
    var subjectLogic = _subjectLogic.GetSubject().Where(c => c.isdel == 0);
    
    #region 没有分组的所有过滤的数据
    var data = (from L in classLogic
    join C in courseLogic on L.cou_id equals C.cou_id into Ctemp
    from Cdata in Ctemp.DefaultIfEmpty()
    
    join R in functionRecordLogic on L.cll_id equals R.cll_id
    
    join P in personnelLgic on L.per_id equals P.per_id
    
    join S in subjectLogic on L.sub_id equals S.sub_id into Stemp
    from Sdata in Stemp.DefaultIfEmpty()
    
    orderby Cdata.cdate descending
    where (new string[] { "in", "ou" }).Contains(L.cll_type)
    select new ShowTeachingPreparationBase
    {
    sub_id = Sdata.sub_id,
    sub_name = Sdata.sub_name,
    cll_id = L.cll_id,
    cll_type = L.cll_type,
    level = Cdata.level,
    per_id = Cdata.per_id,
    per_name = P.per_name,
    per_userid = P.per_userid,
    cll_edate = L.cll_edate,
    cll_cdate = L.cll_cdate,
    fcd_code = R.fcd_code.StartsWith("C") ? "C" : (R.fcd_code.StartsWith("D") ? "D" : "ELSE"),
    sec_id = Sdata.sec_id,
    cdate = R.cdate
    });
    
    //科目不为空时
    if (!string.IsNullOrEmpty(subjectId))
    {
    data = data.Where(c => c.sub_id == subjectId);
    }
    //年段,小学,中学,高中...
    if (!string.IsNullOrEmpty(secId))
    {
    data = data.Where(c => c.sec_id == secId);
    }
    //开始与结束时间
    if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
    {
    var formatStartDate = Convert.ToDateTime(startDate);
    var formatEndDate = Convert.ToDateTime(endDate);
    data = data.Where(c => c.cdate >= formatStartDate && c.cdate <= formatEndDate);
    data = data.Where(c => c.cll_cdate >= formatStartDate && c.cll_edate <= formatEndDate);
    }
    #endregion
    
    #region 分组后的数据
    var tempdata = (
    from tt in data
    where tt.per_id != null
    group tt by new
    {
    tt.cll_id,
    tt.sub_id,
    tt.per_id,
    tt.per_name,
    tt.level,
    tt.cll_type,
    tt.fcd_code
    
    } into G
    select new ShowTeachingPreparationTemp
    {
    cll_id = G.Key.cll_id,
    per_id = G.Key.per_id,
    per_name = G.Key.per_name,
    sub_id = G.Key.sub_id,
    level = G.Key.level,
    cll_type = G.Key.cll_type,
    fcd_code = G.Key.fcd_code,
    count_num = G.Count()
    }).ToList();
    // 按照老系统的数据展示,需要分组两次
    var newGroup = (from TD in tempdata
    group TD by new
    {
    TD.sub_id,
    TD.per_id,
    TD.per_name,
    TD.level,
    TD.cll_type,
    TD.fcd_code,
    
    } into DD
    select new ShowTeachingPreparationTemp
    {
    per_id = DD.Key.per_id,
    per_name = DD.Key.per_name,
    sub_id = DD.Key.sub_id,
    level = DD.Key.level,
    cll_type = DD.Key.cll_type,
    fcd_code = DD.Key.fcd_code,
    count_num = DD.Count()
    
    }).ToList();
    
    #endregion
    
    List<ShowTeacherCourseResult> resultList = new List<ShowTeacherCourseResult>();
    // Key 的分组的总的次数
    int totalRowCount = newGroup.Count;
    
    //-有做数据的合并(同一个老师的相同的课程会存在多条数据,如相同老师的相同课程相同年段 C,D,ELSE的情况需要合并)
    for (int i = 0; i < totalRowCount; i++)
    {
    var sub_id = newGroup[i].sub_id;
    var level = newGroup[i].level;//年级
    var cll_type = newGroup[i].cll_type;
    var fcd_code = newGroup[i].fcd_code;
    var per_id = newGroup[i].per_id;
    var per_name = newGroup[i].per_name;//课程教师
    
    //获取当前这个老师的所有 cll_id
    
    var M = tempdata.Where(c => c.per_id == per_id && c.sub_id == sub_id && c.level == c.level).ToList();
    string[] getcll_idList =M.Select(c=>c.cll_id).ToArray() ;
    
    int nowGroupCount = M.Count();//登录的次数
    ShowTeacherCourseResult re = new ShowTeacherCourseResult();
    await AssemblingModel(M, nowGroupCount, re, getcll_idList, per_id, level, sub_id, per_name, cll_type);
    resultList.Add(re);
    }
    return resultList;
    }
    
    private async Task AssemblingModel(List<ShowTeachingPreparationTemp> M, int nowGroupCount, ShowTeacherCourseResult re, string[] cll_idList, string per_id, string level, string sub_id, string per_name, string cll_type)
    {
    re.level = level;//年级
    re.sub_id = sub_id;
    re.per_id = per_id;
    re.per_name = per_name;//教师姓名
    re.loginCount = nowGroupCount.ToString(); //登录的次数
    string type = cll_type;
    re.loginCount = type == "in" ? $"{nowGroupCount}/0" : $"0/{nowGroupCount}";
    //获取到这个人的该课程的所有的cll_id,方便来获取总的时间
    var getCllid_list = M.Select(c => new cll_idList { cll_id = c.cll_id }).ToList();
    
    //使用的时间
    string havaUseTime = await Task.Factory.StartNew(() => ((from T in _classesLogic.GetClassLog()
    where cll_idList.Contains(T.cll_id)
    select new { T.cll_edate, T.cll_cdate }).ToList().Sum(c => (c.cll_edate - c.cll_cdate).TotalSeconds / 60.0).ToString("f2")));
    
    re.havaUseTime = type == "in" ? $"{havaUseTime}/0" : $"0/{havaUseTime}";
    //C 富媒体
    int dataC = GetDataCDEDataRateCount(M, "C", level, per_id, sub_id);
    //D 教学工具
    int dataD = GetDataCDEDataRateCount(M, "D", level, per_id, sub_id);
    //ELSE 互动工具
    int dataE = GetDataCDEDataRateCount(M, "ELSE", level, per_id, sub_id);
    
    int totalCount = dataC + dataD + dataE;
    
    string dataC_PercentRage = $"{(dataC * 1.0 * 100 / totalCount).ToString("f2")}";
    string dataD_PercentRage = $"{(dataD * 1.0 * 100 / totalCount).ToString("f2")}";
    string dataE_PercentRage = $"{(dataE * 1.0 * 100 / totalCount).ToString("f2")}";
    
    re.richMedia = type == "in" ? $"{dataC_PercentRage}%/0.00%" : $"0.00%/{dataC_PercentRage}%";
    re.teacherTool = type == "in" ? $"{dataD_PercentRage}%/0.00%" : $"0.00%/{dataD_PercentRage}%";
    re.interactiveTools = type == "in" ? $"{dataE_PercentRage}%/0.00%" : $"0.00%/{dataE_PercentRage}%";
    }
    
    private static int GetDataCDEDataRateCount(List<ShowTeachingPreparationTemp> M, string fcd_code, string level, string per_id, string sub_id)
    {
    return M.Where(c => c.fcd_code == fcd_code && c.level == level && c.per_id == per_id && c.sub_id == sub_id).Count();
    }
    View Code

     混合处理

        public async Task<List<GetKnowledgePointSXDShowData>> KnowledgePointSxidu(KnowledgePointSXDInputData inputData)
            {
                bool flag = inputData.KnowledgePointEnum == Domain.Enum.KnowledgePointEnum.AllClass;
                StringBuilder sb = new StringBuilder();
                sb.Append(@"SELECT   K.kno_id,K.kno_name,
                                    HR.correction_type,HR.per_id,P.per_userid,P.per_name,COUNT(1)tcount
                                     FROM guide_learning_assignment GLA
                                    LEFT JOIN guide_learning_item GLI ON GLI.gul_id=GLA.gul_id
                                    LEFT JOIN guide_learning_resources GRS ON GRS.gli_id=GLI.gli_id AND GRS.glr_type ='h' 
                                    
                                    LEFT JOIN homework_question HQ ON HQ.hom_id=GRS.glr_source   
                                    LEFT JOIN homework_respond HR ON HQ.hoq_id=HQ.hoq_id
                                    LEFT JOIN question Q ON HQ.que_id=Q.que_id
                                    LEFT JOIN knowledge K ON K.kno_id=Q.que_kno1  AND K.isdel=0
                                    
                                    LEFT JOIN personnel P ON HR.per_id=P.per_id AND P.isdel=0
                                    WHERE
                                     GLA.cou_id=@cou_id
                                    AND HR.per_id IN(
                                     SELECT R.per_id FROM class_role R 
                                     WHERE R.class_uid IN( SELECT C.class_uid FROM course C WHERE C.cou_id=@cou_id AND C.isdel=0 ) 
                                    AND R.class_role_type='0')
                                    and HR.isdel=0 AND GLA.isdel=0 AND GLI.isdel=0 and K.kno_id IS NOT NULL ");
                if (inputData.session_week > 0)
                {
                    sb.Append(" and  GLA.session_week=@session_week ");
                }
    
                if (flag) //全班
                {
                    sb.Append("   GROUP BY  K.kno_id,K.kno_name, HR.correction_type ");
                }
                else
                {
                    sb.Append(" AND HR.per_id=@per_id ");
                    sb.Append("   GROUP BY  HR.per_id, K.kno_id,K.kno_name, HR.correction_type "); //学生
                }
                sb.Append(@"  UNION all
                                    
                                    SELECT  K.kno_id,K.kno_name,
                                    HR.correction_type,HR.per_id,P.per_userid,P.per_name,COUNT(1)tcount
                                     FROM guide_learning_assignment GLA
                                    LEFT JOIN guide_learning_item GLI ON GLA.gul_id=GLI.gul_id
                                    LEFT JOIN guide_learning_resources GRS ON GRS.gli_id=GLI.gli_id AND GRS.glr_type='p' 
                                    
                                    LEFT JOIN pre_learning_question HQ ON HQ.prl_id=GRS.glr_source   
                                    LEFT JOIN pre_learning_respond HR ON HQ.plq_id=HQ.plq_id 
                                    inner JOIN question Q ON HQ.que_id=Q.que_id 
                                    inner JOIN knowledge K ON K.kno_id=Q.que_kno1  
                                    LEFT JOIN personnel P ON HR.per_id=P.per_id AND P.isdel=0
                                    WHERE 
                                    GLA.cou_id=@cou_id
                                    AND HR.per_id IN(
                                     SELECT R.per_id FROM class_role R 
                                     WHERE R.class_uid IN(
                                      SELECT C.class_uid FROM course C WHERE C.cou_id=@cou_id AND C.isdel=0 
                                      )  AND R.class_role_type='0'
                                    )
                                    AND  HR.isdel=0 AND GLA.isdel=0 AND GLI.isdel=0
                                    AND  K.kno_id IS NOT NULL AND K.isdel=0 AND  HR.isdel=0 ");
                if (inputData.session_week > 0)
                {
                    sb.Append(" and  GLA.session_week=@session_week ");
                }
                if (flag) //全班
                {
                    sb.Append("   GROUP BY  K.kno_id,K.kno_name, HR.correction_type ");
                }
                else
                {
                    sb.Append(" AND HR.per_id=@per_id ");
                    sb.Append("   GROUP BY  HR.per_id, K.kno_id,K.kno_name, HR.correction_type "); //学生
                }
                var getTempData = await ZRF_DapperHelper.QueryAsync<GetKnowledgePointSXDTempData>(sb.ToString(), new { cou_id = inputData.cou_id, per_id = inputData.per_id, session_week = inputData.session_week });
                var listinfo = new List<GetKnowledgePointSXDShowData>();
                if (getTempData != null && getTempData.Any())
                {
                    var groupKname = (from N in getTempData
                                      group N by new { N.kno_id, N.kno_name } into Ndata
                                      select new
                                      {
                                          kid = Ndata.Key.kno_id,
                                          name = Ndata.Key.kno_name,
                                          count = Ndata.Count()
                                      }).ToList();
                    GetKnowledgePointSXDShowData info = default;
                    for (int i = 0; i < groupKname.Count; i++)
                    {
                        info = new GetKnowledgePointSXDShowData();
                        string kid = groupKname[i].kid;
                        string kname = groupKname[i].name;
                        info.KnowledgePointName = kname;
                        //null:作答正确
                        int Familiarity_correction_type_null = getTempData.Where(c => (c.correction_type == "" || c.correction_type == null) && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount);
                        //0:作答错误
                        int Familiarity_correction_type_0 = getTempData.Where(c => c.correction_type == "0" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount);
                        int totalF = Familiarity_correction_type_null + Familiarity_correction_type_0;
                        info.FamiliarityWithAnsweringRate = $"{((Familiarity_correction_type_null * 1.0 / (totalF > 0 ? totalF : 1)) * 100).ToString("f2")}%";
    
    
                        //null:订正熟悉度
                        int Revised_correction_type_2 = getTempData.Where(c => c.correction_type == "2" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount);
                        //2:订正正确
                        int Revised_correction_type_1 = getTempData.Where(c => c.correction_type == "1" && c.kno_id == kid).ToList().DefaultIfEmpty().Sum(c => c == null ? 0 : c.tcount);
                        //1:订正正确
                        int totalRevised = Revised_correction_type_2 + Revised_correction_type_1;
                        //$"{((Revised_correction_type_2*1.0/(totalRevised > 0 ? totalRevised : 1))*100).ToString("f2")}%";
                        info.RevisedFamiliarityRate = $"{((Revised_correction_type_2 * 1.0 / (totalRevised > 0 ? totalRevised : 1)) * 100).ToString("f2")}%";
                        listinfo.Add(info);
                    }
                }
                return listinfo;
            }
    View Code
  • 相关阅读:
    MVC3中输出Html标签的方法
    Server.MapPath 出现未将对象引用设置到对象的实例
    谈谈网站静态化
    WCF 服务应用程序与 服务库之间的区别
    插入中国所有省和市的SQL语句--以后用
    KiCad 元件值 F4NNIU 规范 (2020-04-30)[31.98%]
    FastAdmin 安装后点登录没有反应怎么办?
    笔记:读英国老太太的复仇计划 (2019-10-15)
    KiCad 工程用 Git 管理需要忽略哪些文件?
    关于 SSD 的接口和相关名词(2019-09-10)
  • 原文地址:https://www.cnblogs.com/Fengge518/p/12193935.html
Copyright © 2020-2023  润新知