【SqlServer】------- SqlServer 创建临时表存入别的表中的数据及字段值

﹏ヽ暗。殇╰゛Y 2022-02-02 01:06 615阅读 0赞

> 1.简单的创建临时表
> 
>     drop table #tmp --删除临时表
>     --创建临时表
>     create table #tmp (
>     	ID    VARCHAR(80) not null,
>     	PNAME VARCHAR(200)
>     )
>     INSERT INTO #tmp(ID,PNAME) SELECT ID,CNAME as PNAME from ORG_DEPARTMENT; --把别的表数据存入临时表
>     SELECT * FROM #tmp;--查询临时表数据
>     
>     
>     // 创建临时表的另一种方式
>     
>     drop table #tmpStudent
>     select *  into #tmpStudent from ES_NOTICE_TARGET
>     select * from #tmpStudent
> 
> 2.相对复杂的创建临时表 更新字段数据
> 
>     BEGIN --开始
>     drop table #tmp --删除临时表 可以不要 看需求了 不能删一个在建一个 是不是很影响效率
>     --创建临时表
>     create table #tmp (
>     	ID    VARCHAR(80) not null,
>     	ZCOUNT VARCHAR(200),
>     	WCOUNT VARCHAR(200),
>     	YCOUNT VARCHAR(200)
>     )
>     --找到别表数据据插入临时表中
>     INSERT INTO #tmp(ID,ZCOUNT) SELECT NOTICE_ID AS ID,COUNT(DEPT_ID) AS ZCOUNT from ES_NOTICE_TARGET GROUP BY NOTICE_ID;
>     --更新临时表字段值 按条件更新
>      update #tmp  set WCOUNT=(SELECT  COUNT(READS_IS_OK) as WCOUNT  from ES_NOTICE_TARGET  where #tmp.ID=NOTICE_ID AND (READS_IS_OK='0' OR READS_IS_OK IS NULL) GROUP BY NOTICE_ID) 
>      where  EXISTS(SELECT NOTICE_ID AS ID from ES_NOTICE_TARGET  GROUP BY NOTICE_ID) 
>      update #tmp  set YCOUNT=(SELECT  COUNT(READS_IS_OK) as WCOUNT  from ES_NOTICE_TARGET  where #tmp.ID=NOTICE_ID AND READS_IS_OK='1'  GROUP BY NOTICE_ID) 
>      where  EXISTS(SELECT NOTICE_ID AS ID from ES_NOTICE_TARGET  GROUP BY NOTICE_ID) 
>     SELECT * FROM #tmp; --查询临时表数据
>     END --结束
>     GO --go
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzY2NjU3_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MzY2NjU3_size_16_color_FFFFFF_t_70]: /images/20220202/372cc747724e4134b4fd8a39e4e284ca.png