两表生成一表
怎样把下面两表:
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
[解决办法]
修改下
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
[解决办法]
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
---------------------------------
1AAA0
2BBB1
3CCC1
4DDD2
1MMM2
2XXX3
3YYY3
--想要楼主那样的结果，排序好像还真不好排诶
