select into from 多个表

[苦苦的潜行者]

刚才看这标题党哥们的select into查询语句很长,
http://topic.csdn.net/u/20111104/12/9ed73061-a899-4098-98f3-4c1697169d43.html
很有意思,就突然奇想从多个表中选择列插入到一个新表中
结果悲剧了,
我想从两个表t1,t2中分别选两个列插入到t3中,一共生成4个字段

/*
为了学习sql,强制自己用sql操作表,即使再简单的查询也要用sql测试
作者:Jully
*/

if object_id('t1') is not null 
	drop table t1
go
if object_id('t2') is not null
	drop table t2
go 
	create table t1
(
	[id] int,
	num int
)
go
	create table t2
(
	[id] int,
	num int
)
go 
insert t1 select 2,4
union all select 3,6
union all select 6,9
union all select 3,8
go 
insert t2 select 1,3
union all select 2,5
union all select 4,7
union all select 2,9
go
if object_id('t3') is not null
	drop table t3
go
-- select 
-- (select [id] from t1 where [id]>3) as id1,
-- (select num from t1 where num<7) as num1,
-- (select [id] from t2 where [id]<5) as id2,
-- (select num from t2 where num>5) as num2
-- into t3    //这是一开始的写法,t3表能生成,且有4个字段 id1,num1,id2,num2 但是语句不成功
--改为如下
select * 
into t3
from
(
select [id] as id1,num as num1  from t1 where [id]>3 and num<7
union all
select [id] as id2,num as num2  from t2 where [id]<5 and num>5
) as a
go
select * from t3 

/*
但是结果为

（所影响的行数为 2 行）

id1   num1
---   ----
4	7
2	9
-------
*/

但这不是我想要的......我想要4列,还有我刚开始模拟众大神的sql coding有好的代码规范请指点,
aozugei...

[--小F--]

什么4列？

加个自增列 然后join起来。

[中国风]

-- select
-- (select [id] from t1 where [id]>3) as id1,
-- (select num from t1 where num<7) as num1,
-- (select [id] from t2 where [id]<5) as id2,
-- (select num from t2 where num>5) as num2

不能這樣用啊，用表連接

[快溜]

if object_id('t1') is not null 
    drop table t1
go
if object_id('t2') is not null
    drop table t2
go 
    create table t1
(
    [id] int,
    num int
)
go
    create table t2
(
    [id] int,
    num int
)
go 
insert t1 select 2,4
union all select 3,6
union all select 6,9
union all select 3,8
go 
insert t2 select 1,3
union all select 2,5
union all select 4,7
union all select 2,9
go
if object_id('t3') is not null
    drop table t3
go
-- select 
-- (select [id] from t1 where [id]>3) as id1,
-- (select num from t1 where num<7) as num1,
-- (select [id] from t2 where [id]<5) as id2,
-- (select num from t2 where num>5) as num2
-- into t3    //这是一开始的写法,t3表能生成,且有4个字段 id1,num1,id2,num2 但是语句不成功
--改为如下
select * 
into t3
from
(
select * from
(select [id] as id1,num as num1  from t1 where [id]>3 and num<7) a
full join 
(select [id] as id2,num as num2  from t2 where [id]<5 and num>5) b
on a.id1=b.id2
) as a
go
select * from t3 

/*
id1         num1        id2         num2
----------- ----------- ----------- -----------
NULL        NULL        4           7
NULL        NULL        2           9

(2 行受影响)

[--小F--]

select
  a.id,a.num,b.id,b.num
from
(
select px=row_number()over(order by getdate()),[id] as id1,num as num1  from t1 where [id]>3 and num<7)a
join
(
select px=row_number()over(order by getdate()),[id] as id2,num as num2  from t2 where [id]<5 and num>5
)b
on
   a.px=b.px

[gw6328]

是能插入的。

[苦苦的潜行者]

[Quote=引用 4 楼 fredrickhu 的回复:]

SQL code
select
a.id,a.num,b.id,b.num
from
(
select px=row_number()over(order by getdate()),[id] as id1,num as num1 from t1 where [id]>3 and num<7)a
join
(
select px=row_number()over(order by get……
[/Quote]

'row_number' 不是可以识别的 函数名。


我的本意说的有点含糊
我的想法是
-- (select [id] from t1 where [id]>3) as id1,
-- (select num from t1 where num<7) as num1,
-- (select [id] from t2 where [id]<5) as id2,
-- (select num from t2 where num>5) as num2
每一条子查询都会出来一列,分别是id1,num1,id2,num2,然后作为t3的字段生成并导入数据

[苦苦的潜行者]

[Quote=引用 2 楼 roy_88 的回复:]

-- select
-- (select [id] from t1 where [id]>3) as id1,
-- (select num from t1 where num<7) as num1,
-- (select [id] from t2 where [id]<5) as id2,
-- (select num from t2 where num>5) as num2

……
[/Quote]

我知道我错了,我写到这我就虚了.所以才换了写法.
R神别见怪啊.

[火才松]

+1,用union all是拼接行，要用join才能拼接列。用full join就可以了。
[Quote=引用 3 楼 ssp2009 的回复:]

SQL code
if object_id('t1') is not null
drop table t1
go
if object_id('t2') is not null
drop table t2
go
create table t1
(
[id] int,
num int
)
go
create table t2
(
[id]……
[/Quote]

[--小F--]

[Quote=引用 6 楼 xiaolinyouni 的回复:]
引用 4 楼 fredrickhu 的回复:

SQL code
select
a.id,a.num,b.id,b.num
from
(
select px=row_number()over(order by getdate()),[id] as id1,num as num1 from t1 where [id]>3 and num<7)a
join
(
select px=……
[/Quote]

汗 2000换identity

[苦苦的潜行者]

[Quote=引用 3 楼 ssp2009 的回复:]

SQL code
if object_id('t1') is not null
drop table t1
go
if object_id('t2') is not null
drop table t2
go
create table t1
(
[id] int,
num int
)
go
create table t2
(
[id]……
[/Quote]

其实我的写法应该是错误的,我想要4列东西,
如果说想要两列的话就如筛选id和num我就不费周折问众大神了,嘻嘻.
不好意思,我实在是没事折磨自己,不小心又折磨到你们宝贵的时间了.

[TimZhuFaith]

select *
into t3


from
(
select id1, num1, id2, num2 from (
select [id] as id1,num as num1 from t1 where [id]>3 or num<7
) t11,
(
select [id] as id2,num as num2 from t2 where [id]<5 or num>5
) t12) as a

[-晴天]

要四列?

    create table t1
(
    [id] int,
    num int
)
go
    create table t2
(
    [id] int,
    num int
)
go 
insert t1 select 2,4
union all select 3,6
union all select 6,9
union all select 3,8
go 
insert t2 select 1,3
union all select 2,5
union all select 4,7
union all select 2,9
go
if object_id('t3') is not null
    drop table t3
go
select a.id,a.num,b.id as id1,b.num as num1
from t1 a ,t2 b
/*
id          num         id1         num1
----------- ----------- ----------- -----------
2           4           1           3
3           6           1           3
6           9           1           3
3           8           1           3
2           4           2           5
3           6           2           5
6           9           2           5
3           8           2           5
2           4           4           7
3           6           4           7
6           9           4           7
3           8           4           7
2           4           2           9
3           6           2           9
6           9           2           9
3           8           2           9

(16 行受影响)

*/
go
drop table t1,t2

[苦苦的潜行者]

唉,整天蹲在屋里不说话我表达能力都下降了.
我再重新整理一下.希望你们能看懂.

select [id] as id1 from t1 where [id]>3
id1
--
6
select num as num1 from t1 where num<7
num1
---
4
6
select [id] as id2 from t2 where [id]<5
id2
--
1
2
4
2
select num as num2 from t2 where num>5 
num2
----
7
9

/*
最后的结果t3,应为
id1  num1   id2  num2
--  ----   ---    ----
6     4     1     7
null  6     2     9
null null   4    null
null null   2    null

*/

[苦苦的潜行者]

[Quote=引用 13 楼 qianjin036a 的回复:]

要四列?
SQL code
create table t1
(
[id] int,
num int
)
go
create table t2
(
[id] int,
num int
)
go
insert t1 select 2,4
union all select 3,6
union all select 6,9
union all sel……
[/Quote]



哥.你是我哥.

[稻草_木偶]

我的方法比较笨就是把这两个表拆成4个表（每个表都有个自增ID号 ）
然后把4个表联系在一起

[-晴天]

[Quote=引用 18 楼 lastone_key 的回复:]
我的方法比较笨就是把这两个表拆成4个表（每个表都有个自增ID号 ）
然后把4个表联系在一起
[/Quote]

啊!
原来就有ID啊,我还用那么多的公用表达式创建序号!

[苦苦的潜行者]

众神,我想要的结果如14楼的分析,请帮忙.

[-晴天]

create table t1([id] int,num int)
create table t2([id] int,num int)
insert t1 select 2,4
union all select 3,6
union all select 6,9
union all select 3,8
go 
insert t2 select 1,3
union all select 2,5
union all select 4,7
union all select 2,9
go
;with c1 as(
select id as id1,row_number()over(order by (select 1))rn from t1 where id>3
),c2 as(
select num as num1,row_number()over(order by (select 1))rn from t1 where num<7
),c3 as(
select id as id2,row_number()over(order by (select 1))rn from t2 where id<5
),c4 as(
select num as num2,row_number()over(order by (select 1))rn from t2 where num>5
),c5 as(
select rn from c1 union select rn from c2 union select rn from c3 union select rn from c4
)select b.id1,c.num1,d.id2,e.num2
from c5 a left join c1 b on a.rn=b.rn
left join c2 c on a.rn=c.rn
left join c3 d on a.rn=d.rn
left join c4 e on a.rn=e.rn
/*
id1         num1        id2         num2
----------- ----------- ----------- -----------
6           4           1           7
NULL        6           2           9
NULL        NULL        4           NULL
NULL        NULL        2           NULL

(4 行受影响)

*/
go
drop table t1,t2