实现两个表连接并将数据并写入到一张临时表中

代码

ALTER PROCEDURE [ dbo ] . [ BlogPhotoClass_Select ]
@Username varchar ( 100 ) = null
AS
BEGIN
-- @f默认分类图片数量，@Mainpic默认分类相册封面图片地址
declare @f int , @Mainpic varchar ( 100 )
select @f = 0 , @Mainpic = ' 默认图片地址 '
select @f = count ( * ) from Blogphoto where Username = @Username and Classid = 0

-- 如果有图片就把第一张图片做相册封面
if ( @f > 0 )
BEGIN
select top 1 @Mainpic = SPath from Blogphoto where Username = @Username and Classid = 0 order by id
END

-- 创建一个临时分类表@temptable，比数据库中的分类表多一个图片数量字段
Declare @temptable table (ID int ,Username varchar ( 100 ),Classname varchar ( 100 ), [ Datetime ] datetime ,Password varchar ( 100 ),MainPic varchar ( 200 ),PhotoCount int )

-- 把默认分类的信息写到临时分类表中
insert into @temptable (ID,Username,Classname, [ Datetime ] ,Password,MainPic,PhotoCount) values ( 0 , @Username , ' 默认相册 ' , getdate (), null , @MainPic , @f )

-- 循环读取表数据并把它写到临时表中
DECLARE @_ID int , @_Username varchar ( 100 ), @_Classname varchar ( 100 ), @_Datetime datetime , @_Password varchar ( 100 ), @_MainPic varchar ( 200 ), @_PhotoCount int
DECLARE rs CURSOR
LOCAL SCROLL FOR

/* 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 * from BlogPhotoClass_View

OPEN rs
FETCH NEXT FROM rs INTO @_ID , @_Username , @_Classname , @_Datetime , @_Password , @_MainPic , @_PhotoCount -- 循环
WHILE @@FETCH_STATUS = 0
BEGIN
insert into @temptable (ID,Username,Classname, [ Datetime ] ,Password,MainPic,PhotoCount) values ( @_ID , @_Username , @_Classname , @_Datetime , @_Password , @_MainPic , @_PhotoCount )
FETCH NEXT FROM rs INTO @_ID , @_Username , @_Classname , @_Datetime , @_Password , @_MainPic , @_PhotoCount
END
CLOSE rs

select * from @temptable order by id
END

 

转载于:https://www.cnblogs.com/lear/archive/2010/08/26/1809083.html