您的位置：程序门 -> ms-sql server -> 基础类

两表生成一表

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

两表生成一表[已结贴,结贴人:zjhzzc001]

发表于：2007-04-17 14:01:42 楼主

怎样把下面两表:
dwid dwname dwparentid
1 aaa 0
2 bbb 1
3 ccc 1
4 ddd 2

和
gzid gzname gzdw
1 mmm 2
2 xxx 3
3 yyy 3
生成下面一表:
id name parentid
1 aaa 0 //单位保留原来的id,name,和父id
2 bbb 1
1 mmm 2 //职工将单位作为父id
3 ccc 1
2 xxx 3
3 yyy 3

发表于：2007-04-17 14:05:531楼得分:0

select dwid as id dwname as name dwparentid as parentid from tb1
union all

select gzid as id gzname as name gzdw as parentid from tb2

发表于：2007-04-17 14:06:382楼得分:0

逗号忘记打了
select dwid as id ,dwname as name, dwparentid as parentid from tb1
union all

select gzid as id , gzname as name , gzdw as parentid from tb2

发表于：2007-04-17 14:15:363楼得分:0

select id, name, parentid from
（
select dwid as id, dwname as name, dwparentid as parentid, 0 as flag from 单位
union all
select *, 1 as flag from 职工
) a
order by （case flag when 1 then parentid else id end), flag, id

发表于：2007-04-17 14:16:044楼得分:0

declare @tb1 table（dwid int, dwname varchar（20), dwparentid int)
insert @tb1 select 1, 'aaa ', 0
union all select 2, 'bbb ', 1
union all select 3, 'ccc ', 1
union all select 4, 'ddd ', 2

declare @tb2 table（gzid int, gzname varchar（20), gzdw int)
insert @tb2 select 1, 'mmm ', 2
union all select 2, 'xxx ', 3
union all select 3, 'yyy ', 3

--生成新表tb
select dwid as id,dwname as [name],dwparentid as parentid into tb
from @tb1
union all
select * from @tb2

--查詢結果
select * from tb
/*
1 aaa 0
2 bbb 1
3 ccc 1
4 ddd 2
1 mmm 2
2 xxx 3
3 yyy 3
*/

发表于：2007-04-17 14:16:375楼得分:0

or

select id, name, parentid from
（
select dwid as id, dwname as name, dwparentid as parentid, 0 as flag, dwid as orderid from 单位
union all
select *, 1 as flag, parentid from 职工
) a
order by orderid, flag, id

发表于：2007-04-17 14:17:206楼得分:0

lin_now（林:答案学一学,题目看一看,分接一接!) 和rockyljt（江濤)，都沒注意到，樓主不只是查詢，還要求排序的。

发表于：2007-04-17 14:19:027楼得分:10

修改下

select id, name, parentid from
（
select dwid as id, dwname as name, dwparentid as parentid, 0 as flag, dwid as orderid from 单位
union all
select *, 1 as flag, gzdw from 职工
) a
order by orderid, flag, id

发表于：2007-04-17 14:22:488楼得分:10

create table t1（
dwid int,
dwname varchar（10),
dwparentid char（1)
)
insert t1
select 1, 'aaa ', '0 ' union all
select 2, 'bbb ', '1 ' union all
select 3, 'ccc ', '1 ' union all
select 4, 'ddd ', '2 '

create table t2（
gzid int,
gzname varchar（10),
gzdw char（1)
)
insert t2
select 1, 'mmm ', '2 'union all
select 2, 'xxx ', '3 'union all
select 3, 'yyy ', '3 '

select dwid as 'id ',dwname as 'name ',dwparentid as 'parentid ' from t1
union all
select gzid,gzname,gzdw from t2

--result:
id name parentid
---------------------------------
1 aaa 0
2 bbb 1
3 ccc 1
4 ddd 2
1 mmm 2
2 xxx 3
3 yyy 3

--想要楼主那样的结果，排序好像还真不好排诶

发表于：2007-04-17 14:31:309楼得分:0

paoluo（一天到晚游泳的鱼)的写法是对的，学习：）

整理一下，借用了paoluo（一天到晚游泳的鱼)的写法：
create table t1（
dwid int,
dwname varchar（10),
dwparentid char（1)
)
insert t1
select 1, 'aaa ', '0 ' union all
select 2, 'bbb ', '1 ' union all
select 3, 'ccc ', '1 ' union all
select 4, 'ddd ', '2 '

create table t2（
gzid int,
gzname varchar（10),
gzdw char（1)
)
insert t2
select 1, 'mmm ', '2 'union all
select 2, 'xxx ', '3 'union all
select 3, 'yyy ', '3 '

select id, name, parentid from
（
select dwid as id, dwname as name, dwparentid as parentid, 0 as flag, dwid as orderid from t1
union all
select *, 1 as flag, gzdw from t2
) a
order by orderid, flag, id

--result：
id name parentid
--------------------------
1 aaa 0
2 bbb 1
1 mmm 2
3 ccc 1
2 xxx 3
3 yyy 3
4 ddd 2

发表于：2007-04-17 16:30:4210楼得分:0

union all

下一篇：求助!
上一篇： "2004-11-2 下午 8:37:31 "

其他资讯