当初由于数据库设计的有些不合理,有平常的查询很难达到想要的效果,就上网查找了临时表查询,然后进行拼接
BEGIN
–判断临时表是否存在删除临时存储表
IF OBJECT_ID(‘tempdb…#temp3’) IS NOT NULL
BEGIN
DROP TABLE #temp3;
END;
IF OBJECT_ID(‘tempdb…#temp2’) IS NOT NULL
BEGIN
DROP TABLE #temp2;
END;
–第一个临时表
SELECT BS_SetMealUser.SetMealUserID ,
BS_SetMealUser.MobileNewsMealID ,
MM_MobileNewsMeal.NewsNum
–将查询的数据放入临时存储表
INTO #temp3 --关键字,相当于把这一段的查询结果放入这个关键字
FROM BS_SetMealUser
JOIN MM_MobileNewsMeal ON BS_SetMealUser.MobileNewsMealID = MM_MobileNewsMeal.MobileNewsMealID
WHERE BS_SetMealUser.UserID = @UserID
AND BS_SetMealUser.ExpireTime >= GETDATE()
AND BS_SetMealUser.MobileNewsMealID != 0;
–第二个临时表
SELECT BS_SetMealUse.SetMealUserID ,ISNULL(SUM(BS_SetMealUse.NewsUseNum), 0) AS bbbINTO #temp2--关键字,相当于把这一段的查询结果放入这个关键字FROM BS_SetMealUseJOIN BS_SetMealUser ON BS_SetMealUser.SetMealUserID = BS_SetMealUse.SetMealUserIDWHERE BS_SetMealUse.IsDelete = 0AND BS_SetMealUser.UserID = @UserIDGROUP BY BS_SetMealUse.SetMealUserID;SELECT #temp3.SetMealUserID ,#temp3.MobileNewsMealID ,#temp3.NewsNum ,#temp2.SetMealUserID ,ISNULL(#temp2.bbb, 0) AS bbbFROM #temp2
–根据相同的SetMealUserID 进行拼接
RIGHT JOIN #temp3 ON #temp2.SetMealUserID = #temp3.SetMealUserID
WHERE ISNULL(#temp2.bbb, 0) < #temp3.NewsNum;
END;