SQL 数据库联查
表A
Qid AQEid AText QCategory
778 SH01 sada 3
779 SH02 5
780 SHO3 6
780 SH04 6
表B
AQEid Qid title
SH01 778
SH02 779 A
SH02-1 779 B
SH02-2 779 C
SH03 780 E
SH04 780 F
SH05 780 G
SH06 780 H
我现在需要得到这样一个结果集
Qid AText QCategory title
778 sada 3
779 5 A
780 6 E、F
说明：当表A QCategory=3(可以理解为：一问一答)时，Qid所对应表B的title为空值，因为答案就是表A中的AText字段值，所以等于我不需要这个字段的值，当表A QCategory=5或QCategory=6时(可以理解为：单选或多选)，我需要的答案在表B中，表B是所有问题的所有选项表，我需要联查表B找到需要的答案即(title字段值)，如果QCategory=6多选需要将查到的数据title拼接为字符串。求一条SQL语句如何能满足需求
SQL 数据库 需求
[解决办法]
--> 测试数据：@表A
declare @表A table([Qid] int,[AQEid] varchar(4),[AText] varchar(4),[QCategory] int)
insert @表A
select 778,'SH01','sada',3 union all
select 779,'SH02','',5 union all
select 780,'SHO3','',6 union all
select 780,'SH04','',6
--> 测试数据：@表B
declare @表B table([AQEid] varchar(6),[Qid] int,[title] varchar(1))
insert @表B
select 'SH01',778,null union all
select 'SH02',779,'A' union all
select 'SH02-1',779,'B' union all
select 'SH02-2',779,'C' union all
select 'SHO3',780,'E' union all
select 'SH04',780,'F' union all
select 'SH05',780,'G' union all
select 'SH06',780,'H'
;WITH maco AS
(
select a.*,ISNULL(b.title,'') AS title from @表A a LEFT JOIN @表B b ON a.AQEid = b.AQEid
)
select
[Qid],AText,QCategory,
[title]=stuff((select ','+[title] from maco where [Qid]=t.[Qid] for xml path('')), 1, 1, '')
from maco t
group by [Qid],AText,QCategory
/*
Qid AText QCategory title
----------- ----- ----------- -----------
778 sada 3
779 5 A
780 6 E,F
*/
select [qid],atext,qcategory,[title]=stuff((select ','+[title] from (select a.*,isnull(b.title,'') as title from 表A a left join 表B b on a.aqeid = b.aqeid) aa where [qid]=t.[qid] for xml path('')), 1, 1, '') from (select a.*,isnull(b.title,'') as title from 表A a left join 表B b on a.aqeid = b.aqeid) t group by [qid],atext,qcategory
