<img src="http://photoimg62.qq.com/cgi-bin/load_pic?verify=XIoa9C5kAWfCTCtAGW4ZTQ%3D%3D">
这是我的表,我想要选出这样的记录:DeviceSN UseSN UseDeptID UseEmpID
1 3 99 99001
2 1 18 07001要满足两个条件:1、DeviceSN不同,2、UseSN最大
SELECT a.DeviceSN, b.UseSN, b.UseDeptID
FROM (SELECT DeviceSN, max(UseSN) AS _UseSN FROM EQ_DeviceUseList GROUP BY DeviceSN) AS a
LEFT JOIN EQ_DeviceUseList AS b ON a.DeviceSN=b.DeviceSN
这个查询得到了如下的结果:<img src="http://photoimg61.qq.com/cgi-bin/load_pic?verify=3veibxkAgz1n26gbSwwryQ%3D%3D">
但是我不想要第2条和第3条记录,该如何把它们排除掉?
这是我的表,我想要选出这样的记录:DeviceSN UseSN UseDeptID UseEmpID
1 3 99 99001
2 1 18 07001要满足两个条件:1、DeviceSN不同,2、UseSN最大
SELECT a.DeviceSN, b.UseSN, b.UseDeptID
FROM (SELECT DeviceSN, max(UseSN) AS _UseSN FROM EQ_DeviceUseList GROUP BY DeviceSN) AS a
LEFT JOIN EQ_DeviceUseList AS b ON a.DeviceSN=b.DeviceSN
这个查询得到了如下的结果:<img src="http://photoimg61.qq.com/cgi-bin/load_pic?verify=3veibxkAgz1n26gbSwwryQ%3D%3D">
但是我不想要第2条和第3条记录,该如何把它们排除掉?
from EQ_DeviceUseList
where (DeviceSN, UseSN) in(select DeviceSN,max(UseSN) from EQ_DeviceUseList b
group by b.DeviceSN);
(select deviceSN,userSn,usedptid,useempid,row_number() over(partition by deviceSN
order by userSN desc) rn)
where rn=1;
(select deviceSN,userSn,usedptid,useempid,row_number() over(partition by deviceSN
order by userSN desc) rn from tab)
where rn=1;
FROM (
SELECT DeviceSN, max(UseSN) AS _UseSN
FROM EQ_DeviceUseList
GROUP BY DeviceSN) AS a
LEFT JOIN EQ_DeviceUseList AS b
ON a.DeviceSN=b.DeviceSN
AND a.UseSN=b._UseSN