SQL中间表使用

最近做项目涉及存储过程比较多，尤其是有大量数据需要在多个表取出处理一下后再进行操作，遥想上次写SQL语句大约 好像 应该追溯到自己在学校学习数据库的青葱年代了~一边感慨自己没有好好学习的同时，一边研究了一下该场景，在网上搜刮了一下别人的智慧，贴一下权当学习笔记了，如果对其他人有用，欢迎一起学习~ 
1、在父子关系表中获取子孙后代结点 
本次项目涉及的场景是，父节点入库后需要删除所有父子链中的子节点，思路是： 建立临时表后递归获取树中父子关系链数据：【Fromhttp://www.cnblogs.com/chriskwok/archive/2009/12/10/1621279.html】 
CREATE PROCEDURE [dbo].[pGetDescendedPhysicalItemCatalogs] 
( 
@PhysicalItemCatalogId int 
) 
AS 
set nocount on

BEGIN TRY 
IF NOT EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = ‘##PhysicalItemCatalog’) 
CREATE TABLE ##PhysicalItemCatalog( 
[PhysicalItemCatalogId] [int] , 
[Name] nvarchar NOT NULL , 
[MnemonicCode] nvarchar NOT NULL , 
[ParentId] [int] NOT NULL , 
[IsDeleted] [bit] NOT NULL , 
[IsValid] [bit] NOT NULL , 
[PhysicalSpecialtyId] [int] NOT NULL , 
[Handled] [bit] NOT NULL default 0 
)

INSERT ##PhysicalItemCatalog(PhysicalItemCatalogId, Name, MnemonicCode, ParentId, IsDeleted, IsValid, PhysicalSpecialtyId)
SELECT PhysicalItemCatalogId, Name, MnemonicCode, ParentId, IsDeleted, IsValid, PhysicalSpecialtyId 
FROM entity.PhysicalItemCatalog with(nolock) WHERE PhysicalItemCatalogId > -1 AND ParentId = @PhysicalItemCatalogId

DECLARE @catalogId int 
SELECT TOP 1 @catalogId = PhysicalItemCatalogId FROM ##PhysicalItemCatalog WHERE Handled = 0
IF @catalogId IS NOT NULL 
begin
    update ##PhysicalItemCatalog set Handled = 1 where PhysicalItemCatalogId = @catalogId
    exec [dbo].[pGetDescendedPhysicalItemCatalogs] @catalogId
end
ELSE
begin
    SELECT * FROM ##PhysicalItemCatalog
    DROP TABLE ##PhysicalItemCatalog
end

END TRY 
BEGIN CATCH 
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE name = ‘##PhysicalItemCatalog’) 
DROP TABLE ##PhysicalItemCatalog 
END CATCH

set nocount off

2、项目中涉及很多需要各表几轮查找获取的数据集，并且有些数据在查找过程中反复使用，可以使用With表达式搞个临时结果集 
【From http://blog.csdn.net/wang1127248268/article/details/53406564】 
我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create view语句的执行范围内进行定义。 
with statNum(id, num) as 
( 
select cid, count(*) from student where id > 0 group by cid 
) 
select id, num from statNum order by id;

with statNum(id, num) as 
( 
select cid, count(*) from student where id > 0 group by cid 
) 
select max(id), avg(num) from statNum;

或者将查询的结果创建一个新表进行保存：【写法From http://blog.csdn.net/qq_34416191/article/details/51508888】 
–将查询结果创建新表 
select stuName,stuInfo.stuNo,writtenExam,labExam, 
ispass=case 
when writtenExam>=60 and labExam>=60 then 1 
else 0 
end 
into newTable 
from stuInfo left join stuScore on stuInfo.stuNo=stuScore.stuNo 
select * from newTable 
Go

use student 
select stuName as ‘姓名’,stuNo as ‘学号’, 
‘笔试成绩’=case

           when writtenExam is null then '缺考'  
           else convert(varchar(4),writtenExam)--注意转型  
       end  

,’机试成绩’=case

           when labExam is null then '缺考'  
           else convert(varchar(4),labExam)  
       end  

,’是否通过’=case 
when ispass=1 then ‘是’ 
else ‘否’ 
end 
from newTable 

go

宁波眼部整形：http://www.iyestar.com/ybzx/