表变量、临时表(with as ,create table)

2018/03/06 17:53
阅读数 24

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

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部