时间戳

首页 > 解决方案 > 带有联合的 TSQL 东西

tsql - 带有联合的 TSQL 东西

问题描述

编辑

对不起，我没有很好地解释自己。

我使用 UNION 的原因是从数据库中的表单中提取这些数据。该表格由多个不同的问题和答案组成，因此取决于问题 - 我需要加入相同的表格，但使用不同的链接。

所以下面是一个更好的例子

   select  ra.AgreementId, sub.Name

from RentAgreement ra

left join
(
--Dogs
Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(u.Description,' (Dog)') 'Name'
from  AllocationApplication aa
inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (16)
inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId in (119) /*DogBreed*/
inner join SystemFormQuestionSetup qs on qs.FormQuestionSetupId = q.FormQuestionSetupId
inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
inner join SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
where aa.ApplicationTypeId = 1 


union all

--Cats
Select AA.Reference, AA.ApplicationId,AA.ContactGroupId,e.AgreementId, CONCAT(d.TextResponse,' (Cat)') 'Name'
from  AllocationApplication aa
inner join ContactGroupLink l on l.ContactGroupId = aa.ContactGroupId and l.EffectiveToDate is null
inner join RentAgreementEpisode e on e.AgreementEpisodeId = l.LinkId and l.LinkTypeId = 2
inner join SystemFormResponse sfr on sfr.FormId = aa.ApplicationId AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
inner join SystemFormPage p on p.FormTemplateId = sfr.FormTemplateId AND p.FormPageId = 6 /*(Pet Register Page)*/
inner join SystemFormSection s on s.FormPageId = p.FormPageId AND s.FormSectionId = 6 /*Pet Details */
Inner join SystemFormGroup g ON g.FormSectionId = s.FormSectionId and FormGroupId in (17)
inner join SystemFormQuestion q on q.FormGroupId = g.FormGroupId and q.EffectiveToDate is null and q.FormQuestionId = 132 /*CatName*/
inner join SystemFormResponseAnswer a on a.FormQuestionId = q.FormQuestionId and a.FormResponseId = sfr.FormResponseId
inner join  SystemFormResponseDetail d on d.FormResponseAnswerId = a.FormResponseAnswerId
where aa.ApplicationTypeId = 1 
) sub on sub.AgreementId = ra.AgreementId

where ra.AgreementId = 1775

这样做的目的是将我的狗和猫拉回来。结果集如下所示。

所以这个特殊的协议 - 有 2 条狗（前两行从第一个 UNION 和 1 只猫拉出。取决于宠物的类型 - 对于狗，他们希望返回品种 - 对于猫，只是动物的名称.

在此处输入图像描述

我想从下面的代码中实现的只是两列和 1 行。第 1 列是协议 ID，第二列是名称，但在此示例中，所有三个宠物都位于一个逗号分隔的行中，例如 - Lhasa Apso（狗）、Pug（狗）、PussPuss（猫）

非常感谢

标签： tsqlsql-server-2016

解决方案

根据我们的查询，似乎整个事情可以缩写为以下内容：

SELECT e.AgreementId,
       d.TextResponse + '(Dog), ' + d.TextResponse + 'Dog(2)' AS [name]
FROM AllocationApplication aa
     INNER JOIN ContactGroupLink l ON l.ContactGroupId = aa.ContactGroupId
                                  AND l.EffectiveToDate IS NULL
     INNER JOIN RentAgreementEpisode e ON e.AgreementEpisodeId = l.LinkId
                                      AND l.LinkTypeId = 2
     INNER JOIN SystemFormResponse sfr ON sfr.FormId = aa.ApplicationId
                                      AND sfr.FormTemplateId = 5 /*(systemformtype 5 = Pet Register)*/
     INNER JOIN SystemFormPage p ON p.FormTemplateId = sfr.FormTemplateId
                                AND p.FormPageId = 6 /*(Pet Register Page)*/
     INNER JOIN SystemFormSection s ON s.FormPageId = p.FormPageId
                                   AND s.FormSectionId = 6 /*Pet Details */
     INNER JOIN SystemFormGroup g ON g.FormSectionId = s.FormSectionId
                                 AND FormGroupId IN (16, 17, 18, 19, 20, 25, 26)
     INNER JOIN SystemFormQuestion q ON q.FormGroupId = g.FormGroupId
                                    AND q.EffectiveToDate IS NULL
                                    AND q.FormQuestionId = 118 /*DogName*/
     INNER JOIN SystemFormResponseAnswer a ON a.FormQuestionId = q.FormQuestionId
                                          AND a.FormResponseId = sfr.FormResponseId
     INNER JOIN SystemFormResponseDetail d ON d.FormResponseAnswerId = a.FormResponseAnswerId
WHERE aa.ApplicationTypeId = 1
  AND e.AgreementId = 1775;

不需要UNION ALLor STUFF。除非我们缺少（很多）信息。

推荐阅读