如何把查询出来的结果集插入到临时表？

2009-12-04 09:09:21 892 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【题解】【中国大学MOOC】（北京大学）人工智能与信息社会考试——期末考试 1.在Alpha-Beta剪枝算法中，我们把一个结点可能取值的上界记作____值，下界记作____值。编号选项 A 以上都不对 B Beta，Alpha C Alpha，Beta D Max，Min 2.属于非完全信息博弈的游戏是编号选项 A 围棋 B 井字棋 C 军棋 D 黑白棋 3.图中的剪枝过程称为____剪枝 ... 阅读详情

已经查询出来的结果集是：

select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
order by t.xm_id

-----------------------------------------------------------------------------------------------------------
我想上面的结果集插入到临时表#test1中，把这个结果集的字段作为#test1的字段，但是提示出错

错误信息为：服务器: 消息 1033，级别 15，状态 1，行 22
除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
insert into #test1 select

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
order by t.xm_id
)

...全文

httprunner V4.3.5 安装与使用--快速上手（超详细-超长篇） HttpRunner是一款面向 HTTP(S) 协议的通用测试框架，只需编写维护一份 YAML/JSON 脚本，即可实现自动化测试、性能测试、线上监控、持续集成等多种测试需求。继承 Requests 的全部特性，轻松实现 HTTP(S) 的各种测试需求测试用例与代码分离，采用YAML/JSON的形式描述测试场景，保障测试用例具备可维护性测试用例支持分层机制，充分实现测试用例的复用测试用例支持参数化和数据驱动机制使用 skip 机制实现对测试用例的分组执行控制。阅读详情

sdnwjd

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

sdnwjd 2009-12-04

打赏
举报

谢谢大家啊

先出去了，一会回来结贴

sdnwjd 2009-12-04

打赏
举报

明白了

这么写就对了：

select * into #test1 from

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'

)m

select * from #test1 order by 项目编号
drop table #test1

叶子 2009-12-04

打赏
举报

[Quote=引用 10 楼 sdnwjd 的回复:]
insert into #test1 select

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
) m
order by xm_id

---------------------------------------------------------------------------
select * from #test1

服务器: 消息 208，级别 16，状态 1，行 1
对象名 '#test1' 无效。

怎么提示临时表#test1无效，我想查询出#test1的数据
[/Quote]

你直接insert into #test1 ，但是还没有创建#test1 ，所以不行。

用select * into #test1 from 是直接创建后插入，两种写法不一样。

叶子 2009-12-04

打赏
举报

对象名 '#test1' 无效。

说明你上面的插入失败了。

banana_97 2009-12-04

打赏
举报

select * into #test1(這里才是臨時表啊)
insert into #test1 這里是表啊

sych888 2009-12-04

打赏
举报

SELECT ... INTO # FROM ...

sdnwjd 2009-12-04

打赏
举报

insert into #test1 select

(
select 业务编号=t.yw_id,
项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) ,
累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00),
日期 = '2009-11-2'
from t_bb2_xm t where t.status='启用' and yw_id='收入'
) m
order by xm_id

---------------------------------------------------------------------------
select * from #test1

服务器: 消息 208，级别 16，状态 1，行 1
对象名 '#test1' 无效。

怎么提示临时表#test1无效，我想查询出#test1的数据

xuejie09242 2009-12-04

打赏
举报

select 后加 top 100 percent
就可以了，还是全部的记录。
直接用select into 好了。
select .... into #test1 from .....

nianran520 2009-12-04

打赏
举报



insert into #test1 

select 业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

from t_bb2_xm t where t.status='启用' and yw_id='收入' 

order by t.xm_id

qiqi860819 2009-12-04

打赏
举报

select top 100 percent

dawugui 2009-12-04

打赏
举报

insert into #test1 select 



( 

select 业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

from t_bb2_xm t where t.status='启用' and yw_id='收入' 

) m

order by xm_id

--小F-- 2009-12-04

打赏
举报

貌似掉了个别名

insert into #test1 select 



( 

select 业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

from t_bb2_xm t where t.status='启用' and yw_id='收入' 



) t

order by t.xm_id

华夏小卒 2009-12-04

打赏
举报

select 业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

into #test1

from t_bb2_xm t where t.status='启用' and yw_id='收入' 

order by t.xm_id

黄_瓜 2009-12-04

打赏
举报

提示很明显了，不要order by
存的时候干没有必要排序

--小F-- 2009-12-04

打赏
举报

insert into #test1 select 



( 

select 

  top 8000 

      业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

from t_bb2_xm t where t.status='启用' and yw_id='收入' 

order by t.xm_id 

)

--小F-- 2009-12-04

打赏
举报

insert into #test1 select 



( 

select 业务编号=t.yw_id, 

      项目编号 = t.xm_id, 

      当日收入金额 = isnull((select dr from t_bb2_in where xm_id = t.xm_id and rq ='2009-11-1' and dept_id='屋'),0.00) , 

      累计收入金额 = isnull((select sum(dr) from t_bb2_in where xm_id = t.xm_id and (rq>='2009-11-1' and rq <= '2009-11-2') and dept_id='屋'),0.00), 

      日期 = '2009-11-2' 

from t_bb2_xm t where t.status='启用' and yw_id='收入' 



) 

order by t.xm_id

如何把查询出来的结果集插入到临时表？

相关推荐