我有两个表A 问卷题库表 - ResearchContent(调查内容),ResearchCode(内容编码)
B 调查记录表 - PeopleCode(接受调查者编码),ResearchCode(内容编码),OutCome(结果)我想得到这个的结果表A中有四个问题.
ResearchCode ResearchContent
1 n1?
2 n2?
3 n3?
4 n4?表B中只回答了一个问题
PeopleCode ResearchCode OutCome
0001 3 是我想查询出People - 0001 对所有问题的回答结果,包括没回答的和已回答的.
两个表做个联合查询得出以下表:例如:
ResearchCode ResearchContent PeopleCode OutCome
1 n1? 0001 null
2 n2? 0001 null
3 n3? 0001 是
4 n4? 0001 null感觉不是很复杂,自己写半天也写不出.只好麻烦大家了.请帮个忙,Thank you so much
B 调查记录表 - PeopleCode(接受调查者编码),ResearchCode(内容编码),OutCome(结果)我想得到这个的结果表A中有四个问题.
ResearchCode ResearchContent
1 n1?
2 n2?
3 n3?
4 n4?表B中只回答了一个问题
PeopleCode ResearchCode OutCome
0001 3 是我想查询出People - 0001 对所有问题的回答结果,包括没回答的和已回答的.
两个表做个联合查询得出以下表:例如:
ResearchCode ResearchContent PeopleCode OutCome
1 n1? 0001 null
2 n2? 0001 null
3 n3? 0001 是
4 n4? 0001 null感觉不是很复杂,自己写半天也写不出.只好麻烦大家了.请帮个忙,Thank you so much
from A,B
where A.ResearchCode = B.ResearchCode)
unit
(select * from A)
--创建环境create table A(ResearchCode int,ResearchContent varchar(1000))
insert into A select 1,'调查1'
union select 2,'调查2'
union select 3,'调查3'
union select 4,'调查4'create table B(ResearchCode int,PeopleCode int, OutCome varchar(1000))
insert into B select 1,0001,NULL
union select 2,0001,NULL
union select 3,0001,'是'
union select 4,0001,NULL--执行语句select B.ResearchCode,A.ResearchContent,B.PeopleCode,B.OutCome
from A inner join B on A.ResearchCode = B.ResearchCode--(所影响的行数为 4 行)
ResearchCode ResearchContent PeopleCode OutCome
1 调查1 1 NULL
2 调查2 1 NULL
3 调查3 1 是
4 调查4 1 NULL
from A inner join B on A.ResearchCode = B.ResearchCode
where B.PeopleCode = 0001
insert into A select 1,'调查1'
union select 2,'调查2'
union select 3,'调查3'
union select 4,'调查4'create table B(ResearchCode int,PeopleCode int, OutCome varchar(1000))
insert into B select 3,0001,'是'--执行语句declare @PeopleCode int
set @PeopleCode = 0001
select ResearchCode,ResearchContent,PeopleCode,OutCome from
(select A.ResearchCode,A.ResearchContent,isnull(PeopleCode,@PeopleCode) as PeopleCode,B.OutCome
from A left join B on A.ResearchCode = B.ResearchCode) as c
where c.PeopleCode = @PeopleCode--(所影响的行数为 4 行)ResearchCode ResearchContent PeopleCode OutCome
1 调查1 1 NULL
2 调查2 1 NULL
3 调查3 1 是
4 调查4 1 NULL