ALTERPROCEDURE[dbo].[BlogPhotoClass_Select]@Usernamevarchar(100)=nullASBEGIN@f默认分类图片数量，@Mainpic默认分类相册封面图片地址declare@fint,@Mainpicvarchar(100)select@f=0,@Mainpic=默认图片地址select@f=count(*)fromBlogphotowhereUsername=@UsernameandClassid=0如果有图片就把第一张图片做相册封面if(@f>0)BEGINselecttop1@Mainpic=SPathfromBlogphotowhereUsername=@UsernameandClassid=0orderbyidEND创建一个临时分类表@temptable，比数据库中的分类表多一个图片数量字段Declare@temptabletable(IDint,Usernamevarchar(100),Classnamevarchar(100),[Datetime]datetime,Passwordvarchar(100),MainPicvarchar(200),PhotoCountint)把默认分类的信息写到临时分类表中insertinto@temptable(ID,Username,Classname,[Datetime],Password,MainPic,PhotoCount)values(0,@Username,默认相册,getdate(),null,@MainPic,@f)循环读取表数据并把它写到临时表中DECLARE@_IDint,@_Usernamevarchar(100),@_Classnamevarchar(100),@_Datetimedatetime,@_Passwordvarchar(100),@_MainPicvarchar(200),@_PhotoCountintDECLARErsCURSORLOCAL SCROLLFOR/*SQL语句，实现两个表的连接，并统计出该分类下的记录条数。现在被写到表的视图里了
SELECT BlogPhotoClass.ID,BlogPhotoClass.Username,BlogPhotoClass.Classname,BlogPhotoClass.[Datetime],
BlogPhotoClass.Password,BlogPhotoClass.MainPic,isnull(TEMP.COUNT, 0) AS PhotoCount
FROM BlogPhotoClass LEFT JOIN
(SELECT Classid, COUNT(*) AS COUNT FROM BlogPhoto GROUP BY Classid) AS TEMP ON TEMP.Classid = BlogPhotoClass.ID*/读视图表中的数据select*fromBlogPhotoClass_ViewOPENrsFETCHNEXTFROMrsINTO@_ID,@_Username,@_Classname,@_Datetime,@_Password,@_MainPic,@_PhotoCount循环WHILE@@FETCH_STATUS=0BEGINinsertinto@temptable(ID,Username,Classname,[Datetime],Password,MainPic,PhotoCount)values(@_ID,@_Username,@_Classname,@_Datetime,@_Password,@_MainPic,@_PhotoCount)FETCHNEXTFROMrsINTO@_ID,@_Username,@_Classname,@_Datetime,@_Password,@_MainPic,@_PhotoCountENDCLOSErsselect*from@temptableorderbyidEND