连接多个表时如何使用GROUP BY连接字符串？

Siddiq Baig 发表于 Dev

西迪克·拜格

我要加入多个表，其中我希望根据以下内容将一个列值插入行TechnicianName：

我有4个表easy_tbljobcard，easy_tbltechnician并easy_tblproblem和easy_tbltechnicianMaster
我TechnicianName从存在的easy_tbltechnicianMaster地方进入第二栏technicianIdeasy_tbltechnician
我想STUFF在查询（p.ProblemReported）的第三列中

当前的SQL语句：

 SELECT j.CardID, 
      , (SELECT TechnicianName FROM easy_tbltechnicianMaster WHERE TechnicianID = t.technicianID) AS TechnicianName
      , p.ProblemReported 
 FROM easy_tbljobcard AS j 
 JOIN easy_technician AS t ON t.CardID = j.CardID  
 LEFT JOIN easy_tblproblem AS p ON p.CardID = t.CardID

查询结果：

╔══════════╦══════════════════╦═══════════════════╗
║  CardID  ║  TechnicianName  ║  ProblemReported  ║
╠══════════╬══════════════════╬═══════════════════╣
║    1     ║      AKBAR       ║     PROBLEM A     ║
║    1     ║      AKBAR       ║     PROBLEM B     ║
║    1     ║      AKBAR       ║     PROBLEM C     ║
║    1     ║      ASANKA      ║     PROBLEM A     ║
║    1     ║      ASANKA      ║     PROBLEM B     ║
║    1     ║      ASANKA      ║     PROBLEM C     ║
╚══════════╩══════════════════╩═══════════════════╝

上面的结果应转换为：

╔══════════╦══════════════════╦═════════════════════════════════╗
║  CardID  ║  TechnicianName  ║         ProblemReported         ║
╠══════════╬══════════════════╬═════════════════════════════════╣
║    1     ║      AKBAR       ║ PROBLEM A, PROBLEM B, PROBLEM C ║
║    1     ║      ASANKA      ║ PROBLEM A, PROBLEM B, PROBLEM C ║
╚══════════╩══════════════════╩═════════════════════════════════╝

连接多个表时如何执行此操作？

SQLFiddle

钾盐

您可以指定CTE –公用表表达式来存储您的临时结果：

with cteTbl ( CardID
            , TechName
            , problemReported ) as ( 
select j.CardID
     , p.ProblemReported
     , ( select TechnicianName
         from easy_tbltechnicianMaster
         where TechnicianID =  t.technicianID ) as TechName
from easy_tbljobcard as j 
join easy_technician as t on t.CardID = j.CardID  
left join easy_tblproblem as p  on p.CardID = t.CardID )

然后select从它和连接具有相同的所有列值t.techName，并t.CardID在一排用for xml path('')，之后更换第一个逗号,有stuff：

select t.CardID
     , t.TechName
     , stuff( ( select ', ' + ProblemReported
                from cteTbl
                where TechName = t.TechName
                order by ProblemReported
                for xml path('') ), 1, 1, '') AS ProblemReported
from cteTbl t
group by t.TechName
       , t.CardID

SQLFiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。