Sql语句-临时表

pictureNameversion status

A1 0

A 2 0

A 1 1

B 1 0

B1 1

B 0 1

希望得到picture中相同pictureName的最大version中的status为0的结果

Select distinctpictureName from picture---------得到A B

Select pictureName,max(version) as v from picture where pictureName in (Select distinctpictureName from picture) Group by pictureName--------得到 A 2与B 1

然后SELECT picture.picturename,picture.version, picture.statusFROM picture,(SELECT pictureName,MAX(version) AS v FROM picture WHERE pictureName IN (SELECT DISTINCT pictureName FROM picture) GROUP BY pictureName) AS picTemp WHERE picture.version=picTemp.v AND picture.picturename=pictTemp.picturenameAND status=0

这里并没有考虑效率问题.........