请教一个SQL 查询问题。

[「已注销」]

一、需求
假设数据表PrinterStatusLog：
No CmnGUID LifeCount Date
1 'FFF3D9' 10 2013-03-18 20:00:13
2 'FFF3D9' 10 2013-03-18 20:00:14
3 'FFF3D9' -1 2013-03-18 20:00:15
4 'FFF3D0' 10 2013-03-18 20:00:13
5 'FFF3D0' 10 2013-03-18 20:00:14
给定一组CmnGUID值（如：'FFF3D9','FFF3D0'）在数据库中找出该表中每个CmnGUID对应的最新一条数据且LifeCount不能为-1。
如给出的CmnGUID为：'FFF3D9','FFF3D0'应该得到2、5两条数据。

二、问题
我现在用下面SQL查询时1、2、4、5都出来了，而1、4两条数据是不需要的。

SELECT "PSL"."CmnGUID","PSL"."Date","PSL"."LifeCount" FROM "PrinterStatusLog" "PSL" 
WHERE "CmnGUID" in ('FFF3D9','FFF3D0') 
AND   "PSL"."LifeCount" <> -1 ORDER BY "PSL"."Date" DESC

求高手～

分不够了、不要介意啊～

[「已注销」]

谢谢楼上的回答，参照你们的回答，问题解决了。

SELECT "PSL"."CmnGUID","PSL"."Date","PSL"."LifeCount" FROM "PrinterStatusLog" "PSL" 
inner join (select "CmnGUID", MAX("Date") AS "Date" from "PrinterStatusLog" where "CmnGUID" 
in ('FFF3D9','FFF3D0') 
Group by "CmnGUID") "PSL2" on "PSL2"."CmnGUID" = "PSL"."CmnGUID" and "PSL2"."Date" = "PSL"."Date"
AND "PSL"."LifeCount" <> -1 ORDER BY "PSL"."Date" DESC

不过数据比较多的候效率不是很好，40万行数据中指定4000个CmnGUID检索时花了大概8秒左右（PostgreSQL）。 感觉这个速度还是不能接受～

[hyrongg]

 ;with A AS(
	 select *, 
		   row_number()over(partition by CmnGUID order by [date] DESC) rn
	from PrinterStatusLog
	WHERE lIFECOUNT!=-1
)SELECT * FROM a WHERE RN=1

[「已注销」]

引用 1 楼 hyrongg 的回复:

SQL code?123456 ;with A AS( select *, row_number()over(partition by CmnGUID order by [date] DESC) rn from PrinterStatusLog WHERE lIFECOUNT!=-1)SELECT * FROM a WHERE R……

非常感谢你的回答， 忘记说了， 数据库是SQL Server 和 PostgreSQL 两个都要支持的。

[rucypli]

select * from tb A where not exists (select 1 from tb B where A.CmnGUDI=B.CmnGUID and A.Data<B.data)

[qiuchu2003]

SELECT * FROM Table WHERE CmnGUID in ('FFF3D9','FFF3D0') AND LifeCount <> -1 AND Date IN(SELECT MAX(Date) FROM Table where CmnGUID in ('FFF3D9','FFF3D0') GROUP BY CmnGUID)

[深～海]

SELECT "PSL"."CmnGUID","PSL"."Date","PSL"."LifeCount" FROM "PrinterStatusLog" where "id" in (select "id", MAX("date") from "PrinterStatusLog" where where "CmnGUID" in ('FFF3D9','FFF3D0') and "PSL"."LifeCount" <> -1 ORDER BY "PSL"."CmnGUID");