更新时间: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