mysql - 如何在我的查询中添加一个新列以显示小于当前日期的数据？

Question

我在下面看到的当前查询显示了用户详细信息和他们已注册的事件，但是在他们参加了一个事件之后，我想要另一列显示 WHERE Start_Date < CURDATE() 以便在他们当前的事件之间进行拆分已经报名参加和参加过的事件。有什么想法可以将其添加到以下查询中吗？

SELECT users.username, users.user_firstname, users.user_lastname, users.user_role,
       GROUP_CONCAT( Events.Event_Name ) AS Event_Names, COUNT(Events.Event_Name) AS Event_Count
FROM users LEFT JOIN 
     user_events
     ON users.id = user_events.id LEFT JOIN
     Events
     ON Events.Event_ID = user_events.Event_ID
GROUP BY users.username, users.user_firstname, users.user_lastname, users.user_role

理想情况下:

| username| user_firstname | user_lastname | user_role | Event_Name |PreEve                               
| M       | jo             | mccann        | employee  | baking,run | fight

Answer 1

这是你想要的吗？

SELECT u.username, u.user_firstname, u.user_lastname, u.user_role,
       GROUP_CONCAT(CASE WHEN e.Start_Date < CURDATE() THEN e.Event_Name END) AS Previous_Event_Names, 
       GROUP_CONCAT(CASE WHEN e.Start_Date >= CURDATE() THEN e.Event_Name END) AS Future_Event_Names, 
       COUNT(e.Event_Name) AS Event_Count
FROM users u LEFT JOIN 
     user_events ue
     ON u.id = ue.id LEFT JOIN
     Events e
     ON e.Event_ID = ue.Event_ID
GROUP BY u.username, u.user_firstname, u.user_lastname, u.user_role;

GROUP_CONCAT() 忽略 NULL 值。