select empid,name,birthday from emp union select empid,income,outcome from salary
楼主理解的有问题吧 union / union all 是把两个或者多个结果集纵向连接,而不是用来横向关联表的
如果必须是这两个表查询,唯一有意义的应该是select a.empid,a.name,a.birthday,b.income,b.outcome from emp a left join salary b on a.empid = b.empid
emp表中有empid,name,birthday列,salary表中有empid,income,outcome列create table emp(empid int ,name varchar(50),birthday varchar(50)) insert emp select 1,'lilei','20110507' union all select 1,'lilei','20110507' union all select 2,'hanmeimei','20110708' create table salary(empid int ,income varchar(50),outcome varchar(50))insert salary select 1,'50','10' union all select 1,'30','20' union all select 2,'100','80' select a.empid,max(name),max(birthday),max(income),max(outcome) from emp a,salary b where a.empid=b.empid group by a.empid1 lilei 20110507 50 20 2 hanmeimei 20110708 100 80
select empid,[name],birthday from emp union select empid,income,outcome from salary--如果用union all则可能出现重复
自己手工列不就行了,你的意思不会是两个empid只出现一个吧?
⊙﹏⊙b汗 确实是这样,不过貌似不是用union实现……
union 要查询出来的字段名都要一样的啊,否则就有可能出现Convert错误
用自然连接吧。。 select emp.empid,name,birthday,income,outcome from emp,salary where emp.empid=salary.empid
字段名可以不一样,“在使用 UNION 组合的结果集中的相应列、或个别查询中使用的任意列的子集必须具有相同数据类型,并且两种数据类型之间必须存在可能的隐性数据转换,或提供了显式转换。”
union
select empid,income,outcome from salary
union / union all 是把两个或者多个结果集纵向连接,而不是用来横向关联表的
from emp a left join salary b on a.empid = b.empid
emp表中有empid,name,birthday列,salary表中有empid,income,outcome列create table emp(empid int ,name varchar(50),birthday varchar(50))
insert emp
select 1,'lilei','20110507'
union all
select 1,'lilei','20110507'
union all
select 2,'hanmeimei','20110708'
create table salary(empid int ,income varchar(50),outcome varchar(50))insert salary
select 1,'50','10'
union all
select 1,'30','20'
union all
select 2,'100','80'
select a.empid,max(name),max(birthday),max(income),max(outcome)
from emp a,salary b where a.empid=b.empid group by a.empid1 lilei 20110507 50 20
2 hanmeimei 20110708 100 80
select empid,[name],birthday from emp
union
select empid,income,outcome from salary--如果用union all则可能出现重复
select emp.empid,name,birthday,income,outcome
from emp,salary
where emp.empid=salary.empid