mySQL 多个表的 JOIN

Question

我在 MySQL-DB (InnoDB) 中有以下三个表

UserTab
ID | Name  | ---
------------------
 1 | Tom   |
 2 | Dick  |
 3 | Harry |

EventTab
ID | Name      | ---
------------------
 1 | Easter    |
 2 | Holidays  |
 3 | ThxGiving |
 4 | Christmas |

ParticipationTab
ID | UserID | EventID
---------------------
 1 | 1      | 1
 2 | 1      | 2
 3 | 1      | 3
 4 | 2      | 1
 5 | 2      | 4
 6 | 3      | 3  

我想通过查询获得以下结果:

QueryResultTab
UserTab.Name | EventTab.Name | NoPart | Names
-----------------------------------------------
 Tom         | Easter        | 2      | Tom, Dick
 Tom         | Holidays      | 1      | Tom
 Tom         | ThxGiving     | 2      | Tom, Harry
 Dick        | Easter        | 2      | Tom, Dick 
 Dick        | Christmas     | 1      | Dick
 Harry       | ThxGiving     | 2      | Tom, Harry

我确实知道 Count() 与 GROUP 结合来获取参与者的数量
我知道 group-concat 来获取“名称”。

SELECT Event, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ', ') as Names  
FROM  
(SELECT ID as UserID, Name FROM X_Users WHERE ConditionA) AS UserTab  
INNER JOIN  
(SELECT EventID, UserID FROM X_Participation WHERE ConditionB) AS ParticipationTab  
ON UserTab.UserID = ParticipationTab.UserID  
INNER JOIN  
(SELECT ID as EventID, Event FROM X_Events WHERE ConditionC) AS EventTab  
ON ParticipationTab.EventID = EventTab.EventID  
GROUP BY EventTab.EventID

这给了我:

ConcatTab
EventTab.Name | Names  
---------------------------  
Easter        | Tom, Dick  
Holidays      | Tom  
ThxGiving     | Tom, Harry  
Easter        | Tom, Dick  
Christmas     | Dick  
ThxGiving     | Tom, Harry  

如您所见，我了解 JOIN。也许我也可以使用 LEFT 或 RIGHT JOIN 来实现此目的。

对于其他部分，我使用此查询:

SELECT Name, Event, NoPart   
FROM (SELECT ID as UserID, Name FROM X_Users WHERE ConditionA) AS UserTab    
INNER JOIN (SELECT EventID, UserID FROM X_Participation WHERE ConditionB) AS PartTab   
ON UserTab.UserID = PartTab.UserID  
INNER JOIN (SELECT ID as EventID, Event FROM X_Events WHERE ConditionC) AS EvTab  
ON PartTab.EventID = EvTab.EventID  
INNER JOIN (SELECT EventID as CntID, COUNT(*) AS NoPart FROM X_Participation WHERE ConditionB) AS CntTab    
ON EvTab.EventID = CntTab.CntID  
ORDER BY UserTab.UserID

这给了我:

CountTab
UserTab.Name | EventTab.Name | NoPart 
--------------------------------------
 Tom         | Easter        | 2      
 Tom         | Holidays      | 1      
 Tom         | ThxGiving     | 2      
 Dick        | Easter        | 2      
 Dick        | Christmas     | 1      
 Harry       | ThxGiving     | 2      

但是如何将 ConcatTab 和 CountTab 组合/合并到 QueryResultTab 中？我想使用 mysql_fetch_assco() 逐行检索 PHP 中的结果表。 请不要告诉我 PDO 等，我知道。

另一个选项 - 我试图避免 - 是在 PHP 循环中执行此操作并使用大量小型 SQL 查询来实现结果。

Answer 1

根据您的示例数据，您需要参与表中的所有行以及维度信息。然后您需要该表的摘要。

以下是在 FROM 子句中使用子查询的方法:

SELECT u.name, e.name, p2.numPart, p2.Names
FROM X_Participation p INNER JOIN  
     X_Users u 
     ON u.UserID = p.UserID INNER JOIN  
     X_Events e
     ON p.EventID = e.EventID INNER JOIN
     (SELECT p2.EventId, COUNT(*) as numPart,
             GROUP_CONCAT(u2.name SEPARATOR ', ') as names
      FROM X_Participation p2 INNER JOIN  
           X_Users u2 
           ON u2.UserID = p2.UserID
      GROUP BY p2.EventId
     ) p2
     ON p2.EventId = p.EventId;

注释:

• 如果您要使用表别名(您应该这样做)，请使其更短，而不是比表名称长。
• 不必要时不要使用子查询。在实现子查询的 MySQL 中尤其如此。
• 您可以在外部查询的 WHERE 子句中添加其他条件。