mySQL 多连接选择结果作为单行中的列

Question

我昨天一直在努力解决这个问题。我已将联接简化到最低限度，因为它会产生非常奇怪的结果。

它现在正在执行我“期望”它执行的操作，但不是我希望它执行的操作...理想情况下，我想要一个返回 day_1、day_2 和 day_3 的行，如果它们是在 event_attendee_days 内的行。

SELECT event_attendees.id, event_attendees.name, event_account.name AS company,

case event_attendee_days.days_id
when '1' then "25th"
ELSE "-"
end AS day_1,

case event_attendee_days.days_id
when '2' then "26th"
ELSE "-"
end AS day_2,

case event_attendee_days.days_id
when '3' then "27th"
ELSE "-"
end AS day_3,

event_account.email, event_account.telephone, acct_type, acct, address1, address2,     address3, address4, postcode, business_type, business_desc, signup

FROM event_attendees
JOIN event_account ON event_attendees.acct_id = event_account.id
LEFT JOIN event_attendee_days ON event_attendees.id = event_attendee_days.user_id

WHERE event_attendees.id = "1019"

ORDER BY event_attendees.acct_id

当前生成(在此示例中我跳过了不需要的文件。)

id   |   day_1  |   day_2  |   day_3
1019 |   25th   |   -      |   -
1019 |   -      |   26th   |   -
1019 |   -      |   -      |   27th

不管怎样

id   |   day_1  |   day_2  |   day_3  | ...
1019 |   25th   |   26th   |   27th   | ...
1020 |   -      |   -      |   27th   | ...

等我有过一些不同的尝试，这是迄今为止我最接近的一次。只需要将结果压缩到一行而不丢失信息:-)

谢谢

Answer 1

试一试:

SELECT
    id,
    MAX(day_1) AS day_1,
    MAX(day_2) AS day_2,
    MAX(day_3) AS day_3
FROM
    event
GROUP BY
    id;

I made an SQL Fiddle here, feel free to test it here

不过，您仍然需要手动输入所需的所有日期。

希望对你有帮助

PS:顺便说一下，在我的 fiddle 上，那个表被命名为“事件”