两个链接访问MySQL临时表_实现两个表连接并将数据并写入到一张临时表中

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