select into from 多个表

2011-11-04 04:50:13 1.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ABAQUS二次开发中创建SET 在ABAQUS中创建加载、边界条件时，需要使用set，创建set时需要使用对象集合对象中的Set函数，这个函数需要提供加入对象集合的列表，但这个对象列表好像并不能自行创建，只能通过ABAQUS各类对象集合中的findAt、getSequenceFromMask、getByBoundingSphere、getByBoundingCylinder或getByBoundingSphere函数得到，但这几个函数在直接使用PYTHON编程时都不太好用。这个函数返回一个字典，如下所示。阅读详情

刚才看这标题党哥们的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...

...全文

云漾AI创作平台实战：如何用GPT4.0+Midjourney快速生成商业级插画（附Prompt模板）本文详细介绍了如何利用云漾AI创作平台结合GPT4.0和Midjourney快速生成商业级插画。通过智能文案生成与视觉化工作流，大幅提升创作效率，适用于电商、社交媒体等多种场景。文章包含实用Prompt模板和参数调整技巧，帮助设计师和内容创作者高效产出专业插画。阅读详情

苦苦的潜行者

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

+ 关注 + 加入社区

切换为时间正序

-晴天 2011-11-05

打赏
举报

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

苦苦的潜行者 2011-11-05

打赏
举报

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

-晴天 2011-11-05

打赏
举报

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

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

稻草_木偶 2011-11-05

打赏
举报

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

苦苦的潜行者 2011-11-04

打赏
举报

[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]

哥.你是我哥.

苦苦的潜行者 2011-11-04

打赏
举报

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



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



*/

-晴天 2011-11-04

打赏
举报

要四列?

    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

TimZhuFaith 2011-11-04

打赏
举报

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

苦苦的潜行者 2011-11-04

打赏
举报

[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我就不费周折问众大神了,嘻嘻.
不好意思,我实在是没事折磨自己,不小心又折磨到你们宝贵的时间了.

--小F-- 2011-11-04

打赏
举报

[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

火才松 2011-11-04

打赏
举报

+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]

苦苦的潜行者 2011-11-04

打赏
举报

[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神别见怪啊.

苦苦的潜行者 2011-11-04

打赏
举报

[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的字段生成并导入数据

gw6328 2011-11-04

打赏
举报

是能插入的。

--小F-- 2011-11-04

打赏
举报

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

快溜 2011-11-04

打赏
举报

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 行受影响)

中国风 2011-11-04

打赏
举报

-- 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

不能這樣用啊，用表連接

--小F-- 2011-11-04

打赏
举报

什么4列？

加个自增列然后join起来。

select into from 多个表

相关推荐