表变量、临时表（with as ，create table）

1.declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode)  (select CountryRegionCode from person.CountryRegion where Name like 'C%')

2.CREATE TABLE #tmp(
FuncID UNIQUEIDENTIFIER
)
INSERT INTO #tmp EXEC usp_Menu_GetMenuByUserID @UserID
DROP TABLE #tmp

3.

WITH p as
(
SELECT ProjectID FROM[dbo].[ProjectDepMapping] WHERE OUID = @OUID
),
s as
(
SELECT * FROM [dbo].[system20180118] where ID in(select * from p)
),
x as
(
SELECT * FROM [dbo].[xmjjrwttj] where festival = @festival and year = @year
)
SELECT s.*,
x.id xmjjrwttjID, x.year, x.userId, x.tbtime, x.sysid,
x.festival, x.userName, x.paltform,
ISNULL(x.zbrs,'0') zbrs, ISNULL(x.xczsrc,'0') xczsrc, ISNULL(x.xtkyl,'0') xtkyl, ISNULL(x.wttbs,'0') wttbs,
ISNULL(x.wtclwcs,'0') wtclwcs, ISNULL(x.wwcwts,'0') wwcwts, ISNULL(x.Emergency,'0') Emergency,
ISNULL(x.wlkyl,'0') wlkyl, ISNULL(x.llltx,'0') llltx, ISNULL(x.dbl,'0') dbl, ISNULL(x.state,'0') state
FROM s
LEFT JOIN x
ON s.ID = x.sysid

转载于:https://www.cnblogs.com/liuqiyun/p/8515807.html