那通过union是可以实现的 select staffid ,projectid from table1 union select staffid ,projectid from table2这样得到的结果中不会存在staffid和projectid都相同的记录的
如果需要查三列,第三列的列名不一样,字段中的值也不一样,我想保留表1中第三列的值,将表二中的第三列的值设为7,即 select staffid ,projectid ,staffrole from table1 union select staffid ,projectid,7 from table2 这时就出现了staffid值相同的情况了。
select a.*,b.staffrole from ( select staffid ,projectid from table1 union select staffid ,projectid from table2)a inner join table1 b on a.staffid=b.staffid and a.projectid=b.projectid
不好意思,写习惯了,这是sql server的语法 oracle应该是 select a.*,b.staffrole from ( select staffid ,projectid from table1 union select staffid ,projectid from table2)a,table1 b where a.staffid=b.staffid and a.projectid=b.projectid
如果表1为a,表2为b ,那么语句是:select a.*,b.staffrole from ( select staffid ,projectid from a union select staffid ,projectid from b)a,table1 b where a.staffid=b.staffid and a.projectid=b.projectid这样对吗? 但好像不行呀,那个括号后面的a,table1 b 是什么意思呀
昨天下班就回去了,没看到 select a.*,decode(b.staffrole,null,7,b.staffrole) staffrole from ( select staffid ,projectid from a union select staffid ,projectid from b)a,table1 b where a.staffid=b.staffid and a.projectid=b.projectid(+)这样应该符合你的要求吧? 在sql server可以使用case when实现,在oracle中提供了decode函数可以直接实现的
select staffid,serial,decode(a.staffrole,null,7,null) as staffrole from (select staffid,serial from a union select staffid,serial from b)
select staffid,serial,staffrole l3 from a where a.staffid||b.serial in (select staffid||serial from a intersect select staffid||serial from b) union select staffid,serial,7 l3 from b where a.staffid||b.serial in (select staffid||serial from b minus select staffid||serial from a) order by 1
union
select staffid ,projectid from table2
select staffid ,projectid from table1
union
select staffid ,projectid from table2这样得到的结果中不会存在staffid和projectid都相同的记录的
select staffid ,projectid ,staffrole from table1
union
select staffid ,projectid,7 from table2
这时就出现了staffid值相同的情况了。
select staffid ,projectid from table1
union
select staffid ,projectid from table2)a
inner join table1 b on a.staffid=b.staffid and a.projectid=b.projectid
oracle应该是
select a.*,b.staffrole from (
select staffid ,projectid from table1
union
select staffid ,projectid from table2)a,table1 b
where a.staffid=b.staffid and a.projectid=b.projectid
select staffid ,projectid from a
union
select staffid ,projectid from b)a,table1 b
where a.staffid=b.staffid and a.projectid=b.projectid这样对吗?
但好像不行呀,那个括号后面的a,table1 b 是什么意思呀
你可以直接把我刚才发的复制过去,不用自己多加a和b的,虽然语句我没有经过测试,不过应该没有错误的
staffid serial staffrole
1782 1042 2
1786 1042 3
1852 1042 5
表2
staffid serial bugrole
1782 1042 0
1783 1042 0
1784 1042 20
1785 1042 30
1786 1042 35
1787 1042 36想把两个表联合在一起,想要的结果是:
staffid serial staffrole
1782 1042 2
1783 1042 7
1784 1042 7
1785 1042 7
1786 1042 3
1787 1042 7
1852 1042 5
select a.*,decode(b.staffrole,null,7,b.staffrole) staffrole from (
select staffid ,projectid from a
union
select staffid ,projectid from b)a,table1 b
where a.staffid=b.staffid and a.projectid=b.projectid(+)这样应该符合你的要求吧?
在sql server可以使用case when实现,在oracle中提供了decode函数可以直接实现的
(select staffid,serial from a
union
select staffid,serial from b)
from a
where a.staffid||b.serial in
(select staffid||serial
from a
intersect
select staffid||serial
from b)
union
select staffid,serial,7 l3
from b
where a.staffid||b.serial in
(select staffid||serial
from b
minus
select staffid||serial
from a)
order by 1