SQL中间表使用

最近做项目涉及存储过程比较多，尤其是有大量数据需要在多个表取出处理一下后再进行操作，遥想上次写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 = @PhysicalItemCatalogIdDECLARE @catalogId int SELECT TOP 1 @catalogId = PhysicalItemCatalogId FROM ##PhysicalItemCatalog WHERE Handled = 0IF @catalogId IS NOT NULL begin    update ##PhysicalItemCatalog set Handled = 1 where PhysicalItemCatalogId = @catalogId    exec [dbo].[pGetDescendedPhysicalItemCatalogs] @catalogIdendELSEbegin    SELECT * FROM ##PhysicalItemCatalog    DROP TABLE ##PhysicalItemCatalogend

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