两张表:职责表、计划表
职责表
username useraccount flagid isdel avg_jcr
张三 0001 200101 0 90
王二 0002 200101 1 80
李四 0003 200101 0 50
张三 0001 200102 0 70
郑大 0005 200101 0 60
计划表username useraccount flagid isdel avg_jcr
张三 0001 200101 0 10
王二 0002 200101 0 13
李四 0003 200101 1 20
张三 0001 200103 0 43
周先 0004 200101 0 60想用 username useraccount flagid isdel 做两个表的关联username useraccount flagid 相等isdel=0的时候显示的结果加上在职责表中isdel=0但计划表中没有此项
再加上计划表中isdel=0但职责表中没有此项如下结果:
username useraccount flagid isdel 职责 计划
张三 0001 200101 0 90 10
郑大 0005 200101 0 60 null
周先 0004 200101 0 null 60
职责表
username useraccount flagid isdel avg_jcr
张三 0001 200101 0 90
王二 0002 200101 1 80
李四 0003 200101 0 50
张三 0001 200102 0 70
郑大 0005 200101 0 60
计划表username useraccount flagid isdel avg_jcr
张三 0001 200101 0 10
王二 0002 200101 0 13
李四 0003 200101 1 20
张三 0001 200103 0 43
周先 0004 200101 0 60想用 username useraccount flagid isdel 做两个表的关联username useraccount flagid 相等isdel=0的时候显示的结果加上在职责表中isdel=0但计划表中没有此项
再加上计划表中isdel=0但职责表中没有此项如下结果:
username useraccount flagid isdel 职责 计划
张三 0001 200101 0 90 10
郑大 0005 200101 0 60 null
周先 0004 200101 0 null 60
SELECT userDuty.username,userDuty.useraccount,userDuty.flagid,userDuty.isdel,userDuty.avg_jcr,userplan.avg_jcr
FROM UserDuty JOIN UserPlan
ON UserDuty.useraccount = UserPlan.useraccount
AND UserDuty.username = UserPlan.username
AND UserDuty.flagid = UserPlan.flagid
AND UserDuty.isdel = 0
AND UserPlan.isdel = 0
union ALL // 求出username useraccount flagid 相等isdel=0的时候显示的结果SELECT UserPlan.username,UserPlan.useraccount,UserPlan.flagid,UserPlan.isdel,UserPlan.avg_jcr,userduty.avg_jcr
FROM UserPlan JOIN
(
SELECT DISTINCT UserPlan.useraccount
FROM UserPlan WHERE UserPlan.useraccount NOT IN
(SELECT DISTINCT userDuty.useraccount FROM userDuty)
AND UserPlan.isdel=0
) AS userList ON userList.useraccount = UserPlan.useraccount
LEFT JOIN userduty
ON userList.useraccount = userduty.useraccountunion ALL //在职责表中isdel=0但计划表中没有此项SELECT userDuty.username,userDuty.useraccount,userDuty.flagid,userDuty.isdel,userplan.avg_jcr,userDuty.avg_jcr
FROM userDuty JOIN
(
SELECT DISTINCT userDuty.useraccount
FROM userDuty WHERE userDuty.useraccount NOT IN
(SELECT DISTINCT userplan.useraccount FROM userplan)
AND userDuty.isdel=0
) AS userList ON userList.useraccount = userDuty.useraccount
LEFT JOIN userplan
ON userList.useraccount = userplan.useraccount //计划表中isdel=0但职责表中没有此项
=========================
张三 0001 200101 0 90 10
周先 0004 200101 0 60 NULL
郑大 0005 200101 0 NULL 60
=========================题目很简单,但是楼主的字段命名太。。
insert into @职责表 select '张三',1,200101,0,90
union all select '王二',2,200101,1,80
union all select '李四',3,200101,0,50
union all select '张三',1,200102,0,70
union all select '郑大',5,200101,0,60
declare @计划表 table (username nvarchar(10),useraccount int,flagid int,isdel int,avg_jcr int)
insert into @计划表 select '张三',1,200101,0,10
union all select '王二',2,200101,0,13
union all select '李四',3,200101,1,20
union all select '张三',1,200103,0,43
union all select '周先',4,200101,0,60
select a.username,a.useraccount,a.isdel,a.avg_jcr as 职责,b.avg_jcr 计划 from @职责表 a join @计划表 b on a.username=b.username and a.useraccount=b.useraccount and
a.flagid=b.flagid and a.isdel=0 and b.isdel=0
union all
select a.username,a.useraccount,a.isdel,a.avg_jcr as 职责,null from @职责表 a where not exists (select 1 from @计划表 b where a.username=b.username) and a.isdel=0
union all
select a.username,a.useraccount,a.isdel,null as 职责,a.avg_jcr 计划 from @职责表 a where not exists (select 1 from @计划表 b where a.username=b.username) and a.isdel=0
username useraccount isdel 职责 计划
---------- ----------- ----------- ----------- -----------
张三 1 0 90 10
郑大 5 0 60 NULL
郑大 5 0 NULL 60(3 行受影响)
insert into @职责表 select '张三',1,200101,0,90
union all select '王二',2,200101,1,80
union all select '李四',3,200101,0,50
union all select '张三',1,200102,0,70
union all select '郑大',5,200101,0,60
declare @计划表 table (username nvarchar(10),useraccount int,flagid int,isdel int,avg_jcr int)
insert into @计划表 select '张三',1,200101,0,10
union all select '王二',2,200101,0,13
union all select '李四',3,200101,1,20
union all select '张三',1,200103,0,43
union all select '周先',4,200101,0,60
select a.username,a.useraccount,a.isdel,a.avg_jcr as 职责,b.avg_jcr 计划 from @职责表 a join @计划表 b on a.username=b.username and a.useraccount=b.useraccount and
a.flagid=b.flagid and a.isdel=0 and b.isdel=0
union all
select a.username,a.useraccount,a.isdel,a.avg_jcr as 职责,null from @职责表 a where not exists (select 1 from @计划表 b where a.username=b.username) and a.isdel=0
union all
select a.username,a.useraccount,a.isdel,null as 职责,a.avg_jcr 计划 from @计划表 a where not exists (select 1 from @职责表 b where a.username=b.username) and a.isdel=0username useraccount isdel 职责 计划
---------- ----------- ----------- ----------- -----------
张三 1 0 90 10
郑大 5 0 60 NULL
周先 4 0 NULL 60(3 行受影响)