如何将访问MySQL临时表的两个链接实现表连接，并将合并后的数据写入新临时表？

本文共计303个文字，预计阅读时间需要2分钟。

sqlALTER PROCEDURE [dbo].[BlogPhotoClass_Select] @Username varchar(100)=NULLASBEGIN -- @f 默认为分类图片数量 -- @Mainpic 默认为分类封面END

ALTERPROCEDURE[dbo].[BlogPhotoClass_Select]@Usernamevarchar(100)nullASBEGIN–@f默认分类图片数量，@Ma

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