如何从sql中的两个表中获取不同的值 [英] how to get distinct values from two table in sql

问题描述

我在sql db server中有两个表，名称为EmployeeDuty和EmployeeStatus，其中存在多个相同员工的记录，我使用以下查询来获取针对每个员工的最后一条记录。

  1 。 var Empdutyleft = _service.GetAllEmployeeDuty（）。OrderByDescending（x => x.EndDate）.GroupBy（x => x.Employee_Id）。选择（x => ; x.First（））。其中（x => x.ToSector_Id == sectorId）。ToList（）; 
  2 。 var EmpStatus = _service.GetEmployeeStatusBySector_Id（sectorId）.OrderByDescending（x => x.EndDate）.GroupBy（x => x.Employee_Id）。选择（x = > x.First（））。ToList（）; 

i希望获得EmployeeDuty中存在但EmployeeStatus中不存在的员工

解决方案

假设他们都返回员工集合：

  var  distinct = Empdutyleft 。除（empStatus）; 

I have two tables in sql db server,name as EmployeeDuty and EmployeeStatus where multiple records of same employees are exist,i used following queries to get last record against each employee.

1.            var Empdutyleft = _service.GetAllEmployeeDuty().OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).Where(x => x.ToSector_Id == sectorId).ToList();
2. var EmpStatus = _service.GetEmployeeStatusBySector_Id(sectorId).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.First()).ToList();

i want to get that employee that exist in EmployeeDuty but not exist in EmployeeStatus

解决方案

Assuming they both return Employee collections:

var distinct = Empdutyleft.Except(empStatus);

这篇关于如何从sql中的两个表中获取不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！