--創建臨時表##ttb
create table ##ttb (p001 nvarchar(20),p002 nvarchar(40) ,p009 nvarchar(20) ,p013 datetime,p012 datetime ,p014 int,p005 char(4),orderP5 int)
insert into ##ttb
select pi001,pi002,pi009,pi013,pi012,PI014,p005='B',1 as orderP5 from dgmocpi where pi001 like '%B%' and pi002 not like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='B1',2 as orderP5 from dgmocpi where pi001 like '%B%' and pi002 like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='T',3 as orderP5 from dgmocpi where pi001 like '%T%' and pi002 not like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='T1',4 as orderP5 from dgmocpi where pi001 like '%T%' and pi002 like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='C',5 as orderP5 from dgmocpi where pi001 like '%C%' and pi002 not like '%立式%'
以上是創建臨時表并插入資料,我現在想將上面部分放入到下面語句中
exec( '
IF EXISTS (SELECT 1 FROM tempdb.DBO.sysobjects where name =''##ttb'')
begin
drop table ##ttb
end
')請問該如何編寫?
create table ##ttb (p001 nvarchar(20),p002 nvarchar(40) ,p009 nvarchar(20) ,p013 datetime,p012 datetime ,p014 int,p005 char(4),orderP5 int)
insert into ##ttb
select pi001,pi002,pi009,pi013,pi012,PI014,p005='B',1 as orderP5 from dgmocpi where pi001 like '%B%' and pi002 not like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='B1',2 as orderP5 from dgmocpi where pi001 like '%B%' and pi002 like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='T',3 as orderP5 from dgmocpi where pi001 like '%T%' and pi002 not like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='T1',4 as orderP5 from dgmocpi where pi001 like '%T%' and pi002 like '%立式%'
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005='C',5 as orderP5 from dgmocpi where pi001 like '%C%' and pi002 not like '%立式%'
以上是創建臨時表并插入資料,我現在想將上面部分放入到下面語句中
exec( '
IF EXISTS (SELECT 1 FROM tempdb.DBO.sysobjects where name =''##ttb'')
begin
drop table ##ttb
end
')請問該如何編寫?
解决方案 »
- sqlserver2008中如何跨数据库建立视图
- 数据库远程注册的问题
- 请问SQL Server2000中的日期格式是不是实际存入的是整数类型的数值呢?那有个整数值能否转成日期呢?两者互换?
- SQL查询Excel表出现问题,以及两个SQL表如何连接Excel表查询
- 求助一个SQLSERVER2000的问题!!!!
- 小f 2010就属您最红 sql2000服务器,一开机就不运行,点击运行没有用,我每次都去给别人重装sql,怎么办???
- 急急急,怎么通过防火墙啊
- 问一个建索引的问题
- SOS!求教关于SQLSERVER中列名无效的问题!初步论坛请多指教!
- 为什么要把月末库存、月末银行存款、应收账款月结、月销售统计、月回款统计等这些数据存入数据表,不时能查询出来吗?
- MSSQL用户权限
- 有什么办法可以得出每个用户的总分值并按照总分值排序
drop table ##ttb
if object_id(''tempdb..##ttb'') is not null
drop table ##ttb '
)
exec( '
IF EXISTS (SELECT 1 FROM tempdb.DBO.sysobjects where name =''##ttb'')
begin
drop table ##ttb
end
else
begin
create table ##ttb (p001 nvarchar(20),p002 nvarchar(40) ,p009 nvarchar(20) ,p013 datetime,p012 datetime ,p014 int,p005 char(4),orderP5 int)
insert into ##ttb
select pi001,pi002,pi009,pi013,pi012,PI014,p005=''B'',1 as orderP5 from dgmocpi where pi001 like ''%B%'' and pi002 not like ''%立式%''
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005=''B1'',2 as orderP5 from dgmocpi where pi001 like ''%B%'' and pi002 like ''%立式%''
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005=''T'',3 as orderP5 from dgmocpi where pi001 like ''%T%'' and pi002 not like ''%立式%''
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005=''T1'',4 as orderP5 from dgmocpi where pi001 like ''%T%'' and pi002 like ''%立式%''
union all
select pi001,pi002,pi009,pi013,pi012,PI014,P005=''C'',5 as orderP5 from dgmocpi where pi001 like ''%C%'' and pi002 not like ''%立式%''
end
')
我没看清楚,不好意思
试试3楼mm的,顺便把这个mm拐回家