Sql语句-临时表

pictureName    version   status

A                             1              0

A                             2               0

A                              1              1

B                             1               0

B                            1                1

B                             0               1

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

Select distinct pictureName from picture---------得到A B

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

然后SELECT picture.picturename,picture.version, picture.status FROM 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.picturename AND status=0

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