两则实现相同功能的代码比较(使用临时表与不用临时表比较)

最新推荐文章于 2024-05-27 12:17:11 发布

Single

最新推荐文章于 2024-05-27 12:17:11 发布

阅读量1.2k

点赞数

分类专栏：原创代码文章标签： null join table object c

本文链接：https://blog.csdn.net/cyz1980/article/details/783201

版权

原创代码专栏收录该内容

29 篇文章 0 订阅

订阅专栏

if (not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[old_outid]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))
begin
select customerid,outid into old_outid from t_customers
--update t_customers set outid='请输入新的学工号'
/* --把新工号恢复成老工号值
UPDATE T_Customers
SET T_Customers.outid = old_outid.outid
FROM old_outid
WHERE old_outid.customerid = T_Customers.CustomerID
*/
end

select * into #dpt from (SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+'/'+dpname2+'/'+dpname3 as 部门名称 FROM T_Department where dpname1 is not null and dpname2 is not null and dpname3 is not null union all SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+'/'+dpname2 as 部门名称 FROM T_Department where dpname1 is not null and dpname2 is not null and dpname3 is null UNION ALL SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1 as 部门名称 FROM T_Department where dpname1 is not null and dpname2 is null and dpname3 is null) l

SELECT CustomerID, MAX(OpDt) AS 最近一次补卡时间 into #ReNewCard_MaxDt
FROM T_ReNewCard
GROUP BY CustomerID

SELECT 部门名称,Name AS 姓名, Alias AS 别名, CardType AS 卡类, c.outid AS 新工号,o.outid as 旧工号,最近一次补卡时间
FROM T_Customers c inner join #dpt d on d.部门代码=c.account inner join #ReNewCard_MaxDt r on
r.customerid=c.customerid inner join old_outid o on c.customerid=o.customerid
WHERE (c.CardType IN (1, 2))

drop table #dpt
drop table #ReNewCard_MaxDt

/* --作视图代码，功能同上，不便阅读理解与修改更新,但适用广。
SELECT d.部门名称, c.Name AS 姓名, c.Alias AS 别名, c.CardType AS 卡类,
      c.outid AS 新工号, o.outid AS 旧工号, r.最近一次补卡时间
FROM T_Customers c INNER JOIN
          (SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码,
               dpname1 + '/' + dpname2 + '/' + dpname3 AS 部门名称
         FROM T_Department
         WHERE dpname1 IS NOT NULL AND dpname2 IS NOT NULL AND
               dpname3 IS NOT NULL
         UNION ALL
         SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码,
               dpname1 + '/' + dpname2 AS 部门名称
         FROM T_Department
         WHERE dpname1 IS NOT NULL AND dpname2 IS NOT NULL AND
               dpname3 IS NULL
         UNION ALL
         SELECT dpcode1 + dpcode2 + dpcode3 AS 部门代码, dpname1 AS 部门名称
         FROM T_Department
         WHERE dpname1 IS NOT NULL AND dpname2 IS NULL AND dpname3 IS NULL)
      d ON d.部门代码 = c.Account INNER JOIN
          (SELECT CustomerID, MAX(OpDt) AS 最近一次补卡时间
         FROM T_ReNewCard
         GROUP BY CustomerID) r ON r.CustomerID = c.CustomerID INNER JOIN
      old_outid o ON c.CustomerID = o.customerid
WHERE (c.CardType IN (1, 2))
*/