如何根据其他表记录更新一个表 [英] how to update one table based on other table records
本文介绍了如何根据其他表记录更新一个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下表格
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使用以下代码< br $>
实体实体= 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;
< span class =code-comment> // 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 tables
1.EmployeeCategory
Id Employee_Id Sector_Id Cat_Id Date
1 sa 1 2 mmmmm
2nd table
2.DailyPerformance
Id Employee_Id Sector_Id Cat_Id Date
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 want that if employee cat_id==1 is more than 4 then it will update the cat_id in EmployeeCategory table of that Employee_Id
i 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.
Anupdatestatement might look like:
UPDATE t1 SET t1.Field1 = t2.Field2 FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON t1.PK = t2.FK
where:
PK- Primary Key
FK= Foreign Key
Primary 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 ok
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 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
这篇关于如何根据其他表记录更新一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
