mysql - 从mysql中的字符串创建列

Question

所以我想做的是左连接这个表，它会定义问题表中的问题，并检查响应表。我写了左连接来将每个问题定义为它自己的列，但它不起作用。有什么想法吗？

select *
FROM Leads l
/*JOIN Projects P ON L.projectid=P.projectid
JOIN Leads LE ON LE.LISTID=L.listid
JOIN Calls CA ON CA.LEADID=LE.leadid*/

join Lists li on (li.Listid=l.Listid)
JOIN Projects P ON (Li.projectid=P.projectid)
join Calls ca on (ca.leadid=l.leadid)

LEFT JOIN (
SELECT 
leadid,
Authorized=MIN(CASE qname WHEN  'Authorized' THEN RESPONSE END),
ReplacementName=MIN(CASE qname WHEN 'ReplacementName' THEN RESPONSE END),
SpokeToGroup=MIN(CASE qname WHEN 'SpokeToGroup' THEN RESPONSE END),
SpokeToName=MIN(CASE qname WHEN 'SpokeToName' THEN RESPONSE END),
SpokeToTitle=MIN(CASE qname WHEN 'SpokeToTitle' THEN RESPONSE END),
WishToRecieve=MIN(CASE qname WHEN 'WishToRecieve' THEN RESPONSE END),
Question01=MIN(CASE qname WHEN 'Question01' THEN RESPONSE END),
... -- lots of other fields
MAGExpiration=MIN(CASE qname WHEN 'MAGExpiration' THEN RESPONSE END),
SourceGroup=MIN(CASE qname WHEN 'SourceGroup' THEN RESPONSE END),
SourceID=MIN(CASE qname WHEN 'SourceID' THEN RESPONSE END),
ClientOtherID=MIN(CASE qname WHEN 'ClientOtherID' THEN RESPONSE END)

from Questions q join Responses r on (r.questionid=q.questionid)
)

Answer 1

您的子查询缺少 GROUP BY:

GROUP BY leadid

您正在使用所有聚合函数，但您没有按任何方式分组。

您还应该在 CASE 语句中包含一个 ELSE。

您可能有更好的方法来执行此操作。如果您使用的是 SQL Server，则可以使用 PIVOT 函数更轻松地执行此操作。由于您有太多试图转换的字段，因此您应该考虑在 sql server 中使用动态 SQL 并在 MySQL 中使用准备好的语句。

编辑 #1，您似乎还遗漏了一些关键项目:

select *
FROM Leads l
/*JOIN Projects P ON L.projectid=P.projectid
JOIN Leads LE ON LE.LISTID=L.listid
JOIN Calls CA ON CA.LEADID=LE.leadid*/

join Lists li on (li.Listid=l.Listid)
JOIN Projects P ON (Li.projectid=P.projectid)
join Calls ca on (ca.leadid=l.leadid)
LEFT JOIN 
(
  SELECT 
    leadid,
    MIN(CASE qname WHEN  'Authorized' THEN RESPONSE END) as Authorized,
    MIN(CASE qname WHEN 'ReplacementName' THEN RESPONSE END) as ReplacementName,
    MIN(CASE qname WHEN 'SpokeToGroup' THEN RESPONSE END) as SpokeToGroup,
    MIN(CASE qname WHEN 'SpokeToName' THEN RESPONSE END) as SpokeToName,
    MIN(CASE qname WHEN 'SpokeToTitle' THEN RESPONSE END) as SpokeToTitle,
    MIN(CASE qname WHEN 'WishToRecieve' THEN RESPONSE END) as WishToRecieve,
    MIN(CASE qname WHEN 'Question01' THEN RESPONSE END) as Question01,
    ... -- lots of other fields
    MIN(CASE qname WHEN 'MAGExpiration' THEN RESPONSE END) as MAGExpiration,
    MIN(CASE qname WHEN 'SourceGroup' THEN RESPONSE END) as SourceGroup,
    MIN(CASE qname WHEN 'SourceID' THEN RESPONSE END) as SourceID,
    MIN(CASE qname WHEN 'ClientOtherID' THEN RESPONSE END) as ClientOtherID
  from Questions q 
  join Responses r 
    on (r.questionid=q.questionid)
  group by leadid
) qr  -- an alias for the subquery
  on l.leadid = qr.leadid  -- the on for the join