现在想要实现下面效果,不知道如何实现纠结了很久.....
Table1表如下:
postId sup_id realName postType
1 12 Tr 1
2 13 jk 1
3 14 pl 1Table2表如下:
postId sup_id realName postType
1 12 Tr 2
2 13 gk 2
3 14 hj 2怎么样通过union把两表的结果链接起来然后实现下面的结果 postId sup_id realName postType
1 12 Tr 2
2 13 jk 1
3 14 pl 1
2 13 gk 2
3 14 hj 2
Table1表如下:
postId sup_id realName postType
1 12 Tr 1
2 13 jk 1
3 14 pl 1Table2表如下:
postId sup_id realName postType
1 12 Tr 2
2 13 gk 2
3 14 hj 2怎么样通过union把两表的结果链接起来然后实现下面的结果 postId sup_id realName postType
1 12 Tr 2
2 13 jk 1
3 14 pl 1
2 13 gk 2
3 14 hj 2
if object_id('[Table1]') is not null drop table [Table1]
go
create table [Table1] (postId int,sup_id int,realName nvarchar(4),postType int)
insert into [Table1]
select 1,12,'Tr',1 union all
select 2,13,'jk',1 union all
select 3,14,'pl',1if object_id('[Table2]') is not null drop table [Table2]
go
create table [Table2] (postId int,sup_id int,realName nvarchar(4),postType int)
insert into [Table2]
select 1,12,'Tr',2 union all
select 2,13,'gk',2 union all
select 3,14,'hj',2select * from [Table1]
select * from [Table2]select postid,sup_id,realname,MAX(posttype)
from (
select postid,sup_id,realname,posttype from Table1
union
select postid,sup_id,realname,posttype from Table2 ) T
group by postid,sup_id,realname/*
1 12 Tr 2
2 13 gk 2
2 13 jk 1
3 14 hj 2
3 14 pl 1*/
as posttype from (select postid,sup_id,realname from Table1
union
select postid,sup_id,realname from Table2)a
where Not Exists(Select 1 from table2
Where table1.postid = table2.postid
And table1.sup_id = table2.sup_id
And table1.realName = table2.realName)
Union
select * from table2