select a.* from a where not exists(select 1 from b where b.用户名=a.用户名 and b.日查询量= a.日订票量 and b.月查询量 = a.月订票量)select b.* from b where not exists(select 1 from a where a.用户名=b.用户名 and a.日订票量 = b.日查询量 and a.月订票量 = b.月查询量)select a.* from a where not exists(select 1 from b where b.用户名=a.用户名 and b.日查询量= a.日订票量 and b.月查询量 = a.月订票量) union all select b.* from b where not exists(select 1 from a where a.用户名=b.用户名 and a.日订票量 = b.日查询量 and a.月订票量 = b.月查询量)不知道上面三个你需要的是哪个?
select a.用户名,日订票量,月订票量,日查询量,月查询量 from a left outer join b on a.用户名 = b.用户名
if object_id('tb') is not null drop table tb goif object_id('tb1') is not null drop table tb1 gocreate table tb (username char(20) , daysum int , monthsum int ) insert into tb select 'aa' , 5 , 80 union all select 'bb' , 10 , 100 union all select 'cc' , 15 , 120 union all select 'dd' , 20 , 200create table tb1 (username char(20) , days int , months int ) insert into tb1 select 'aa' , 5 , 80 union all select 'ee' , 10 , 200 union all select 'ff' , 12 , 120 union all select 'cc' , 11 , 100select a.* , b.days , b.months from tb a , tb1 b where a.username in (select c.username from tb c where not exists(select 1 from tb1 d where d.username = c.username))drop table tb , tb1 结果 username daysum monthsum days months bb 10 100 5 80 dd 20 200 5 80 bb 10 100 10 200 dd 20 200 10 200 bb 10 100 12 120 dd 20 200 12 120 bb 10 100 11 100 dd 20 200 11 100
非常感谢你的回复,你的答案非常接近我的要求,但我想要的是所有用户数据,如上的aa,bb,cc,dd,ee,ff所有usrename的daysum monthsum days months;如果没有则用0代替,如bb没有days,months则把它的这两个值都设置为0,反之亦然,即显示结果为: aa 5 80 5 80 bb 10 100 0 0 ...... ff 0 0 12 120
--借用 4l 的数据use test goif object_id('tb') is not null drop table tb goif object_id('tb1') is not null drop table tb1 gocreate table tb (username varchar(5) , daysum int , monthsum int ) insert into tb select 'aa' , 5 , 80 union all select 'bb' , 10 , 100 union all select 'cc' , 15 , 120 union all select 'dd' , 20 , 200create table tb1 (username varchar(5) , days int , months int ) insert into tb1 select 'aa' , 5 , 80 union all select 'ee' , 10 , 200 union all select 'ff' , 12 , 120 union all select 'cc' , 11 , 100 goselect username=isnull(a.username,b.username) ,daysum=isnull(a.daysum,0) ,monthsum=isnull(a.monthsum,0) ,days=isnull(b.days,0) ,months=isnull(b.months,0) from tb a full join tb1 b on a.username=b.username /* username daysum monthsum days months ----------------------------------------------- aa 5 80 5 80 bb 10 100 0 0 cc 15 120 11 100 dd 20 200 0 0 ee 0 0 10 200 ff 0 0 12 120 */
union all
select b.* from b where not exists(select 1 from a where a.用户名=b.用户名 and a.日订票量 = b.日查询量 and a.月订票量 = b.月查询量)不知道上面三个你需要的是哪个?
from a
left outer join b
on a.用户名 = b.用户名
if object_id('tb') is not null drop table tb
goif object_id('tb1') is not null drop table tb1
gocreate table tb (username char(20) , daysum int , monthsum int )
insert into tb select 'aa' , 5 , 80
union all select 'bb' , 10 , 100
union all select 'cc' , 15 , 120
union all select 'dd' , 20 , 200create table tb1 (username char(20) , days int , months int )
insert into tb1 select 'aa' , 5 , 80
union all select 'ee' , 10 , 200
union all select 'ff' , 12 , 120
union all select 'cc' , 11 , 100select a.* , b.days , b.months from tb a , tb1 b where a.username in (select c.username from tb c where not exists(select 1 from tb1 d where d.username = c.username))drop table tb , tb1
结果
username daysum monthsum days months
bb 10 100 5 80
dd 20 200 5 80
bb 10 100 10 200
dd 20 200 10 200
bb 10 100 12 120
dd 20 200 12 120
bb 10 100 11 100
dd 20 200 11 100
非常感谢你的回复,你的答案非常接近我的要求,但我想要的是所有用户数据,如上的aa,bb,cc,dd,ee,ff所有usrename的daysum monthsum days months;如果没有则用0代替,如bb没有days,months则把它的这两个值都设置为0,反之亦然,即显示结果为:
aa 5 80 5 80
bb 10 100 0 0
......
ff 0 0 12 120
goif object_id('tb') is not null drop table tb
goif object_id('tb1') is not null drop table tb1
gocreate table tb (username varchar(5) , daysum int , monthsum int )
insert into tb select 'aa' , 5 , 80
union all select 'bb' , 10 , 100
union all select 'cc' , 15 , 120
union all select 'dd' , 20 , 200create table tb1 (username varchar(5) , days int , months int )
insert into tb1 select 'aa' , 5 , 80
union all select 'ee' , 10 , 200
union all select 'ff' , 12 , 120
union all select 'cc' , 11 , 100
goselect username=isnull(a.username,b.username)
,daysum=isnull(a.daysum,0)
,monthsum=isnull(a.monthsum,0)
,days=isnull(b.days,0)
,months=isnull(b.months,0)
from tb a full join tb1 b on a.username=b.username /*
username daysum monthsum days months
-----------------------------------------------
aa 5 80 5 80
bb 10 100 0 0
cc 15 120 11 100
dd 20 200 0 0
ee 0 0 10 200
ff 0 0 12 120
*/