--查询 select userPid=isnull(a.userPid,b.userPid) ,useroperatename=isnull(a.useroperatename,b.useroperatename) ,userstatusname1=isnull(a.userstatusname,'') ,userstatusname2=isnull(b.userstatusname,'') ,userstatusoperat=case a.userstatusname when b.userstatusname then '同' else '不同' end from( select a.userPid,b.useroperatename,c.userstatusName from userdaystatus1 a join useroperate b on a.useroperateID=b.useroperateID join userstatusType c on a.userstatusID=c.userstatusID )a full join( select a.userPid,b.useroperatename,c.userstatusName from userdaystatus2 a join useroperate b on a.useroperateID=b.useroperateID join userstatusType c on a.userstatusID=c.userstatusID )b on a.userPid=b.userPid
--测试--测试数据 create table useroperate(useroperateID int,useroperatename varchar(10)) insert useroperate select 11,'张三' union all select 12,'李四' union all select 13,'王五'create table userstatusType(userstatusID int,userstatusName varchar(10)) insert userstatusType select 10,'停' union all select 11,'开' union all select 12,'半停'create table userdaystatus1(userPid int,useroperateID int,userstatusID int) insert userdaystatus1 select 1001,11,10 union all select 1002,11,10 union all select 1004,13,12create table userdaystatus2(userPid int,useroperateID int,userstatusID int) insert userdaystatus2 select 1001,11,10 union all select 1002,11,11 union all select 1005,12,10 go--查询 select userPid=isnull(a.userPid,b.userPid) ,useroperatename=isnull(a.useroperatename,b.useroperatename) ,userstatusname1=isnull(a.userstatusname,'') ,userstatusname2=isnull(b.userstatusname,'') ,userstatusoperat=case a.userstatusname when b.userstatusname then '同' else '不同' end from( select a.userPid,b.useroperatename,c.userstatusName from userdaystatus1 a join useroperate b on a.useroperateID=b.useroperateID join userstatusType c on a.userstatusID=c.userstatusID )a full join( select a.userPid,b.useroperatename,c.userstatusName from userdaystatus2 a join useroperate b on a.useroperateID=b.useroperateID join userstatusType c on a.userstatusID=c.userstatusID )b on a.userPid=b.userPid go--删除测试 drop table useroperate,userstatusType,userdaystatus1,userdaystatus2/*--测试结果 userPid useroperatename userstatusname1 userstatusname2 userstatusoperat ----------- --------------- --------------- --------------- ---------------- 1001 张三 停 停 同 1002 张三 停 开 不同 1004 王五 半停 不同 1005 李四 停 不同(所影响的行数为 4 行) --*/
select userPid=isnull(a.userPid,b.userPid)
,useroperatename=isnull(a.useroperatename,b.useroperatename)
,userstatusname1=isnull(a.userstatusname,'')
,userstatusname2=isnull(b.userstatusname,'')
,userstatusoperat=case a.userstatusname
when b.userstatusname then '同' else '不同' end
from(
select a.userPid,b.useroperatename,c.userstatusName
from userdaystatus1 a
join useroperate b on a.useroperateID=b.useroperateID
join userstatusType c on a.userstatusID=c.userstatusID
)a full join(
select a.userPid,b.useroperatename,c.userstatusName
from userdaystatus2 a
join useroperate b on a.useroperateID=b.useroperateID
join userstatusType c on a.userstatusID=c.userstatusID
)b on a.userPid=b.userPid
create table useroperate(useroperateID int,useroperatename varchar(10))
insert useroperate select 11,'张三'
union all select 12,'李四'
union all select 13,'王五'create table userstatusType(userstatusID int,userstatusName varchar(10))
insert userstatusType select 10,'停'
union all select 11,'开'
union all select 12,'半停'create table userdaystatus1(userPid int,useroperateID int,userstatusID int)
insert userdaystatus1 select 1001,11,10
union all select 1002,11,10
union all select 1004,13,12create table userdaystatus2(userPid int,useroperateID int,userstatusID int)
insert userdaystatus2 select 1001,11,10
union all select 1002,11,11
union all select 1005,12,10
go--查询
select userPid=isnull(a.userPid,b.userPid)
,useroperatename=isnull(a.useroperatename,b.useroperatename)
,userstatusname1=isnull(a.userstatusname,'')
,userstatusname2=isnull(b.userstatusname,'')
,userstatusoperat=case a.userstatusname
when b.userstatusname then '同' else '不同' end
from(
select a.userPid,b.useroperatename,c.userstatusName
from userdaystatus1 a
join useroperate b on a.useroperateID=b.useroperateID
join userstatusType c on a.userstatusID=c.userstatusID
)a full join(
select a.userPid,b.useroperatename,c.userstatusName
from userdaystatus2 a
join useroperate b on a.useroperateID=b.useroperateID
join userstatusType c on a.userstatusID=c.userstatusID
)b on a.userPid=b.userPid
go--删除测试
drop table useroperate,userstatusType,userdaystatus1,userdaystatus2/*--测试结果
userPid useroperatename userstatusname1 userstatusname2 userstatusoperat
----------- --------------- --------------- --------------- ----------------
1001 张三 停 停 同
1002 张三 停 开 不同
1004 王五 半停 不同
1005 李四 停 不同(所影响的行数为 4 行)
--*/