2014年10月16日 星期四

SQL Select 結果後 手動更新其中美些欄位值

DECLARE @TmpTable1 TABLE(
    FC_Title varchar(MAX) ,
    FC_Content varchar(MAX),
    Brands varchar(MAX),
    FC_ID int,
    QTY int
);    
    
WITH T AS(                      
SELECT FC_Title, FC_Content, dbo.GetForumBrands(FC_ID) AS Brands, FC_ID,
(SELECT COUNT(*) FROM Forum F LEFT JOIN UserItem UI ON F.UID=UI.UID WHERE F.FC_ID=FC.FC_ID AND F.FM_IsOpen=1) AS QTY
FROM ForumCatalog FC WHERE FC_IsOpen=1 )

Insert into @TmpTable1(FC_Title, FC_Content, Brands,FC_ID,QTY) select FC_Title, FC_Content, Brands,FC_ID,QTY from T

update @TmpTable1 set QTY = (
    select COUNT(*) from
             (select k.Id from dbo.KM_Expert k inner join KM_Expert_Estimate e on k.Id = e.ExpertId
             group by k.Id) T
 ) where FC_ID = 10

select * from @TmpTable1 ORDER BY FC_ID

沒有留言:

張貼留言