多个表的mySQL连接

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()逐行检索结果表。请不要跟我说PDO等等，我知道的。

另一种选择--我尽量避免的--是在PHP循环中执行，并使用许多微小的SQL查询来实现结果。

Answer 1

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

下面是一种在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;

备注：

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