且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

如何从MS Sql server中的表中获取最近的记录

更新时间:2023-02-03 13:20:38

试试这个:

 选择 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