--可以把tableB当作临时表,create创建,drop删除;可以根据需要修改 where ... and other_condition :--只导瓜皮不导瓜瓤(只导表结构不导数据)的语句:create table tableB as (select * from tableA where 1=0); --既导瓜皮又导瓜瓤的语句:create table tableB as (select * from tableA where 1=1);
一条SQL就搞定了: create table tableB as (select colum1,colum2...colum7 from tableA where 1=1);
楼主想要的结果,其实我觉得创建视图就达到你想要的结果了 如果是要两次查询结果,可以要union 或union all 语句把两次查询结果合为一个结果集为新表
--> Test Data: [T] if object_id('[T]') is not null drop table [T] create table [T] (ID int identity(1,1),[GJSJ] int,[JKYL] numeric(2,1)) insert into [T] select 0,5.0 union all select 4,5.2 union all select 12,4.8 union all select 20,4.6 union all select 28,4.5select * from [T] --Code declare @s varchar(8000),@s2 varchar(8000) select @s=ISNULL(@S+',','')+'max(case when [GJSJ]='''+ltrim([GJSJ])+''' then [GJSJ] end) as ''colum'+ltrim([id]+1)+'''', @s2=ISNULL(@S2+',','')+''+ltrim([JKYL])+'' from T --print @S --print @s2 exec('select ''GJSJ'' as colum1,'+@S+' from [T] union all select ''JKYL'','+@s2+'') --Drop drop table [T] --Result /* colum1 colum2 colum3 colum4 colum5 colum6 ------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- GJSJ 0.0 4.0 12.0 20.0 28.0 JKYL 5.0 5.2 4.8 4.6 4.5 */
上面的语句如何在Oracle中转换?我始终转换出错,可否告知?
create table tableB as select colum1,colum2,colum3,colum4,colum5,,colum6,colum7 from tableA(后面可带过滤条件)
直接 create table temptable as select colum1,colum2,colum3,colum4,colum5,colum6,colum7 from tablename
--既导瓜皮又导瓜瓤的语句:create table tableB as (select * from tableA where 1=1);
create table tableB as (select colum1,colum2...colum7 from tableA where 1=1);
如果是要两次查询结果,可以要union 或union all 语句把两次查询结果合为一个结果集为新表
if object_id('[T]') is not null drop table [T]
create table [T] (ID int identity(1,1),[GJSJ] int,[JKYL] numeric(2,1))
insert into [T]
select 0,5.0 union all
select 4,5.2 union all
select 12,4.8 union all
select 20,4.6 union all
select 28,4.5select * from [T]
--Code
declare @s varchar(8000),@s2 varchar(8000)
select @s=ISNULL(@S+',','')+'max(case when [GJSJ]='''+ltrim([GJSJ])+''' then [GJSJ] end) as ''colum'+ltrim([id]+1)+'''',
@s2=ISNULL(@S2+',','')+''+ltrim([JKYL])+'' from T
--print @S
--print @s2
exec('select ''GJSJ'' as colum1,'+@S+' from [T] union all select ''JKYL'','+@s2+'')
--Drop
drop table [T]
--Result
/*
colum1 colum2 colum3 colum4 colum5 colum6
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
GJSJ 0.0 4.0 12.0 20.0 28.0
JKYL 5.0 5.2 4.8 4.6 4.5
*/
create table temptable as select colum1,colum2,colum3,colum4,colum5,colum6,colum7 from tablename