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

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

  • 本文作者: 帐前卒
  • 本文链接: http://chillyc.info/2007/1756502/
  • 版权声明: 本博客所有文章除特别声明外,只能复制超链接地址,且必须注明出处!