如何根据其他表记录更新一个表

本文介绍了如何根据其他表记录更新一个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我有以下表格 1.EmployeeCategory Id Employee_Id Sector_Id Cat_Id Date 1 sa 1 2 mmmmm 第二张表 2.DailyPerformance Id Employee_Id Sector_Id Cat_Id 日期 1 sa 1 2 21/01/15 2 sa 1 1 22/01/15 3 sa 1 3 23/01/15 4 sa 1 1 24/01/15 5 sa 1 1 25/01/15 6 sa 1 1 26/01/15 7 sa 1 1 27/01/15 i希望如果员工cat_id == 1超过4，那么它将更新Employee_Id的EmployeeCategory表中的cat_id i am使用以下代码 实体实体= new 实体（）; var EmpCat = _service.GetAllDailyPerformance（）; foreach （ var empcat in EmpCat） { var emp1 = 来自 dp in entities.DailyPerformances.OrderByDescending（x => x.Date）.Where（x => x.Employee_Id == empcat.Employee_Id&& x.Category_Id == 1 ）。取（ 10 ） select dp; if （emp1.Count（）> = 4 ） { var dpemp = empcat; CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory（）; emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 1 ; _service.UpdateEmployeeCategory（emp）; } 如果（emp1.Count（）> = 3 ） { var dpemp = empcat; CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory（）; emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 2 ; _service.UpdateEmployeeCategory（emp）; } } i想要检查每位员工的最后10条记录，然后根据查询更新类别在Employeecategory表解决方案 正如我在问题的评论中所提到的，您的要求并不清楚。所以，我只建议您提供示例解决方案。 更新声明可能如下所示： 更新 t1 SET t1.Field1 = t2.Field2 FROM 表1 AS t1 INNER JOIN 表2 AS t2 ON t1.PK = t2.FK 其中： PK - 主键 FK =外键 主要和外键约束 [ ^ ] 根据需要更改代码。 /> 下次，请更具体，并提供更多信息能够理解更改数据的标准。 检查这个示例 [ ^ ]没关系 var EmpCat = _service.GetDailyPerformanceBySector_Id（sectorId）; // var Empcategory = _service.GetEmployeeCategoryBySector_Id（sectorId）.FirstOrDefault（）; // double datediff =（date - Empcategory.Date）.TotalDays; // if（datediff> = 5） // { foreach （ var empcat in EmpCat） { var emp1 = EmpCat.Where（x = > x.Employee_Id == empcat.Employee_Id）.OrderByDescending（x = > x.Date）.Take（ 5 ）。ToList（）; int category = 0 ; foreach （ var \\ temp in emp1） { category + = emp.Category_Id; } decimal avg = category /（ decimal ）emp1 。计数; CTP.HRMS.Business.EmployeeCategory empCategory = _service.GetEmployeeCategoryBySector_Id（sectorId）.LastOrDefault（x = > x.Employee_Id == empcat.Employee_Id）; if （avg < 2 ） { if （empCategory.Category_Id < = 2 ） { empCategory.Category_Id = 1 ; empCategory.Date = DateTime.Today; } else if （empCategory.Category_Id < = 3 ） { empCategory.Category_Id = 2 ; empCategory.Date = DateTime.Today; } } 如果（avg > 2 ） { if （empCategory.Category_Id == 1 ） { empCategory.Category_Id = 2 ; empCategory.Date = DateTime.Today; } 其他 如果（empCategory.Category_Id == 2 ） { empCategory.Category_Id = 3 ; empCategory.Date = DateTime.Today; } } _service.UpdateEmployeeCategory（empCategory）; } 我的代码满足了我的要求，而不是让所有人认为我的问题然后解决方案 i have following tables1.EmployeeCategoryId Employee_Id Sector_Id Cat_Id Date1 sa 1 2 mmmmm2nd table2.DailyPerformanceId Employee_Id Sector_Id Cat_Id Date1 sa 1 2 21/01/152 sa 1 1 22/01/153 sa 1 3 23/01/154 sa 1 1 24/01/155 sa 1 1 25/01/156 sa 1 1 26/01/157 sa 1 1 27/01/15i want that if employee cat_id==1 is more than 4 then it will update the cat_id in EmployeeCategory table of that Employee_Idi am using following code Entities entities = new Entities(); var EmpCat = _service.GetAllDailyPerformance(); foreach (var empcat in EmpCat) { var emp1 = from dp in entities.DailyPerformances.OrderByDescending(x=>x.Date).Where(x=>x.Employee_Id==empcat.Employee_Id && x.Category_Id==1).Take(10) select dp; if(emp1.Count()>=4) { var dpemp = empcat; CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory(); emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 1; _service.UpdateEmployeeCategory(emp); }if(emp1.Count()>=3) { var dpemp = empcat; CTP.HRMS.Business.EmployeeCategory emp = new Business.EmployeeCategory(); emp.Employee_Id = dpemp.Employee_Id; emp.Date = dpemp.Date; emp.Sector_Id = dpemp.Sector_Id; emp.Category_Id = 2; _service.UpdateEmployeeCategory(emp); } }i want to check last 10 records against each employee,then base on query update the category in the Employeecategory table 解决方案 As i mentioned in the comment to the question, your requirements are not clear. So, i can only suggest you sample solution.An update statement might look like:UPDATE t1 SET t1.Field1 = t2.Field2FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.PK = t2.FKwhere:PK - Primary KeyFK = Foreign KeyPrimary and Foreign Key Constraints[^]Change the code to your needs.Next time, please be more specific and provide more details to be able to understand criteria to change data.check this example[^] is it okvar EmpCat = _service.GetDailyPerformanceBySector_Id(sectorId); //var Empcategory = _service.GetEmployeeCategoryBySector_Id(sectorId).FirstOrDefault(); // double datediff =( date - Empcategory.Date).TotalDays; // if (datediff >= 5) // { foreach (var empcat in EmpCat) { var emp1 = EmpCat.Where(x => x.Employee_Id == empcat.Employee_Id).OrderByDescending(x => x.Date).Take(5).ToList(); int category = 0; foreach (var emp in emp1) { category += emp.Category_Id; } decimal avg = category / (decimal)emp1.Count; CTP.HRMS.Business.EmployeeCategory empCategory = _service.GetEmployeeCategoryBySector_Id(sectorId).LastOrDefault(x => x.Employee_Id == empcat.Employee_Id); if (avg < 2) { if (empCategory.Category_Id <= 2) { empCategory.Category_Id = 1; empCategory.Date = DateTime.Today; } else if (empCategory.Category_Id <= 3) { empCategory.Category_Id = 2; empCategory.Date = DateTime.Today; } } if (avg > 2) { if (empCategory.Category_Id == 1) { empCategory.Category_Id = 2; empCategory.Date = DateTime.Today; } else if (empCategory.Category_Id == 2) { empCategory.Category_Id = 3; empCategory.Date = DateTime.Today; } } _service.UpdateEmployeeCategory(empCategory); }that my code that fullfill my requirement,thanx to all to make me think right in to my problem and then to solution 这篇关于如何根据其他表记录更新一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！