SQL中间表使用

1、在父子关系表中获取子孙后代结点
本次项目涉及的场景是，父节点入库后需要删除所有父子链中的子节点，思路是： 建立临时表后递归获取树中父子关系链数据：【From http://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