我的数据库是这样子的ID COMPANYID WORKTYPE WORKTIME
1 1001 01 2006-06-10
2 1002 03 2006-06-10
3 1001 02 2006-06-11
现在COMPANYID为1001的有两条数据,我想取WORKTIME大的那条,不取WORKTIME小的那条,请问这条SELECT语句怎样写,谢谢
1 1001 01 2006-06-10
2 1002 03 2006-06-10
3 1001 02 2006-06-11
现在COMPANYID为1001的有两条数据,我想取WORKTIME大的那条,不取WORKTIME小的那条,请问这条SELECT语句怎样写,谢谢
t.*
from
tname t
where
not exists(select 1 from tname where COMPANYID=t.COMPANYID and WORKTIME>t.WORKTIME)
FROM Company a
INNER JOIN
(SELECT MAX(WorkType) AS WorkType,CompanyID FROM Company GROUP BY CompanyID) b
ON b.WorkType=a.WorkType AND b.CompanyID=a.CompanyID或者SELECT * FROM Company WHERE WorkType IN(SELECT MAX(WorkType) FROM Company GROUP BY CompanyID)
这种写法在WOrkType有重复值时不能正常取出数据
可以改写为
SELECT * FROM Company a WHERE WorkType =(SELECT MAX(WorkType) FROM Company b WHERE a.CompanyID=b.CompanyID GROUP BY CompanyID)效率有差别.
没有测试,仅供参考