如何从MS Sql server中的表中获取最近的记录 [英] How to get recent record from a table in MS Sql server

问题描述

 Id DeptId EmpId sal 
 
  1   1   101   100  
  2   1   102   100  
  3   1   103   300  
  4   1   104   300  
  5   1   105   300  
  6   1   101   500  
  7   1   101   400  
  8   1   102   300  
  9   1   104   250  
  10   1   103   350  
 

我想要输出像这样

 <   pre  < span class =code-attribute>   lang   =  SQL >  
 
 Id DeptId EmpId sal 
 
 5 1 105 300 
 7 1 101 400 
 8 1 102 300 
 9 1 104 250 
 10 1 103 350 
 
 <   / pre  >  

嗨朋友们，正如我在上面的表格中所解释的那样，我想知道雇员的最近工资，这意味着EmpId在这里重复，并希望明确的EmpId不会因为Id而重复。

任何帮助将不胜感激。在此先感谢。

解决方案

试试这个：

 选择 id，deptid，empid，sal 来自 table1 t1 其中 
 id = （选择  top   1  id 来自 table1 其中 t1.empid = empid  order   by  id  desc ）

这是稍微复杂一点，因为您将不得不依赖于Id值以递增的数字顺序 - 如果可以，您应该考虑为行添加时间戳，以便为您提供正确的定义的排序顺序。

这是因为SQL服务器可以按任何顺序返回记录，除非您明确指定ORDER BY子句或类似的子句。

所以...试试这个：

  SELECT  m.id ，m.deptId，m.empId，m.sal 
  FROM  MyTable m 
  LEFT   JOIN  MyTable n  ON  m.empId = n.empId  AND  m.id< n.id 
  WHERE  n.id  IS   NULL  

Id    DeptId     EmpId    sal

 1       1         101    100
 2       1         102    100
 3       1         103    300
 4       1         104    300
 5       1         105    300
 6       1         101    500
 7       1         101    400
 8       1         102    300
 9       1         104    250
10       1         103    350

I want output like this

<pre lang="SQL">

 Id    DeptId     EmpId    sal

  5       1         105    300
  7       1         101    400
  8       1         102    300
  9       1         104    250
 10       1         103    350

</pre>

Hi friends, as i have explained in the above table i would like to know that the recent salary of the Employee which means the EmpId is getting repeated here and want distinct EmpId which is not getting repeated based on the Id.
Any help will appreciated. Thanks in advance.

解决方案

Try this:

select id, deptid, empid, sal from table1 t1 where
id = (select top 1 id from table1 where t1.empid = empid order by id desc)

This is slightly complicated, since you are going to have to rely on the Id values being in ascending numeric order - if you can, you should consider time-stamping the rows instead to give you a "proper", defined sort order.
This is because SQL server can return records in any order unless you explicitly specify an ORDER BY clause or similar.
So... try this:

SELECT m.id, m.deptId, m.empId, m.sal
   FROM MyTable m
   LEFT JOIN MyTable n ON m.empId = n.empId AND m.id < n.id
WHERE n.id IS NULL

这篇关于如何从MS Sql server中的表中获取最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！