Oracle开窗函数学习

符秋枫

　　select *
   from (select photo_num,
                photo_name,
                photo_desc,
                small_addr,
                big_addr,
                group_name,
                vote_person,
                remark,GROUP_NUMBER,
                rank() over(partition by photo_num order by photo_name) mm
           from (SELECT T.*,
                        (SELECT COUNT(1)
                           FROM PHOTO_INFO R
                          WHERE R.PHOTO_NUM = T.PHOTO_NUM) GROUP_NUMBER
                   FROM PHOTO_INFO T
                  ORDER BY PHOTO_NUM))
  where mm = 1
　　说明：针对一次摄影比赛，对图片进行分组；每一组的图片编号是一样的。而且要显示每组图片的个数；同一组的图片，只显示第一张图片。数据表相关信息：
　　create table PHOTO_INFO  (
   PHOTO_NUM            varchar2(10)                    not null,
   PHOTO_NAME           varchar2(100),
   PHOTO_DESC           varchar2(800),
   SMALL_ADDR           varchar2(100),
   BIG_ADDR             varchar2(100),
   GROUP_NAME           varchar2(100),
   VOTE_PERSON          varchar2(100),
   REMARK               varchar2(100),
   constraint PK_PHOTO_INFO primary key (PHOTO_NUM)
);
　　comment on table PHOTO_INFO is
'参赛作品信息';
　　comment on column PHOTO_INFO.PHOTO_NAME is
'作品名称';
　　comment on column PHOTO_INFO.VOTE_PERSON is
'投稿人';
　　comment on column PHOTO_INFO.REMARK is
'备注';