MySQL 事件联盟加入 3 个表 - MySQL Events League JOIN 3 Tables

我有表格：

事件

   -----+-------------------+    |身份证 |事件名称 |    -----+-------------------+    |1 |保龄球 |    |2 |测验 |    |3 |飞镖 |    |4 |泳池 |    |5 |寻宝 |    |6 |斯诺克 |    -----+-------------------+

竞争对手

   ------+------------------+    |身份证 |竞争对手名称 |    ------+------------------+    |1 |Microsoft |    |2 |英国广播公司 |    |3 |脸书 |    ------+------------------+

结果

   ------+---------------+---------+-------------+    |身份证 |竞争对手编号 |事件标识 | 事件得分 |    ------+---------------+---------+-------------+    |1 |1 | 1 |  12 |    |2 |1 | 2 |  11 |    |3 |1 | 3 |  23 |    |4 |2 | 1 |  66 |    |5 |2 | 2 |  12 |    |6 |2 | 3 |  11 |    |7 |2 | 4 |  23 |    |8 |2 | 5 |  66 |    |3 |2 | 6 |  23 |    |4 |3 | 1 |  66 |    |5 |3 | 2 |  12 |    |6 |3 | 3 |  11 |    |7 |3 | 4 |  23 |    |8 |3 | 6 |  66 |    ------+---------------+---------+-------------+

我想从中实现这样的输出：

   --------------+---------+---------+--------+------+--------------+----------    |竞争对手 |保龄球 |测验 | 飞镖 |泳池 |寻宝 |斯诺克 |    --------------+---------+---------+--------+------+--------------+----------    |Microsoft |12 | 11 |  23 | 0 |0 |0 |    |英国广播公司 |66 | 12 |  11 | 23 |66 |23 |    |脸书 |66 | 12 |  11 | 23 |0 |66 |    --------------+---------+---------+--------+------+--------------+----------

我已经尝试了各种连接和嵌套 php，但我就是无法破解它。我什至不确定这是否可能。我的SQL知识不是很好，我尝试了一些教程，但它们没有涵盖这种查询。

SELECT   competitors.competitorName                          AS competitor,
         SUM(IF(results.eventId = 1, results.eventScore, 0)) AS Bowling,
         SUM(IF(results.eventId = 2, results.eventScore, 0)) AS Quiz,
         -- etc.
FROM     competitors
  LEFT JOIN results ON results.competitorId = competitors.id
GROUP BY competitors.id

此请求通常没问题，但每次在表事件中添加新条目时都需要修改。

SELECT
    comp.competitorName as competitor,
    rbowling.eventScore as Bowling,
    rquiz.eventScore as Quiz,
    rdarts.eventScore as Darts,
    rpool.eventScore as Pool,
    rtreasure.eventScore as Treasure_Hunt,
    rsnooker.eventScore as Snooker
FROM competitors comp
LEFT JOIN result rbowling ON (rbowling.eventId = 1 AND comp.id = rbowling.competitorId)
LEFT JOIN result rquiz ON (rquiz.eventId = 2 AND comp.id = rquiz.competitorId)
LEFT JOIN result rdarts ON (rdarts.eventId = 3 AND comp.id = rdarts.competitorId)
LEFT JOIN result rpool ON (rpool.eventId = 4 AND comp.id = rpool.competitorId)
LEFT JOIN result rtreasure ON (rtreasure.eventId = 5 AND comp.id = rtreasure.competitorId)
LEFT JOIN result rsnooker ON (rsnooker.eventId = 6 AND comp.id = rsnooker.competitorId)
GROUP BY comp.id;

你可以像这样通过php创建查询

<?php
$events = array('Assume this is array of events table with id,eventName as key');
$column = array();
foreach($events as $event)
{
     $column[] = 'SUM(IF(results.eventId = '.$event['id'].', results.eventScore, 0)) AS '.$event['eventName'];
}
$sql = 'SELECT   competitors.competitorName AS competitor,';
$sql .= implode(',',$column);
$sql .= ' FROM competitors';
$sql .= ' LEFT JOIN results ON results.competitorId = competitors.id';
$sql .= ' GROUP BY competitors.id';
//Put $sql into mysql_query then

然后，动态获取行

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    foreach($row as $k=>$v) 
    {
         //Here you get Column Name as $k  and data value as $v
    }
}

你基本上在寻找一个数据透视表。这里有一个例子。

您要查找的查询是这个：

SELECT r.eventScore, e.eventName, c.competitorName
FROM results r
LEFT JOIN events e ON e.id = r.eventId
LEFT JOIN competitors c ON c.id = r.competitorId

通过此查询，您将获得每个结果的基础事件和竞争对手名称。然后你必须在PHP中管理数据以创建一个你想要的表......但我想这很难实现...

我最好分别对竞争对手和赛事进行选择，然后获得所有分数结果并创建一个这样的表格：

// we have $competitors array containing id => name
// we have $events array containing id => name
// we have $results array containing competitorId => [ eventId => score ]
echo '<table>';
echo '<tr><td>Competitors</td>';
foreach($events as $e)
    echo '<td>'.$e.'</td>';
echo '</tr>';
foreach($results as $cId => $val) {
    echo '<tr><td>'.$competitors[$cId].'</td>';
    foreach($events as $eId => $name) {
        echo '<td>'.$val[$eId].'</td>';
    }
    echo '</tr>';
}
echo '</table>';

但这只是一个快速且不是很好的解决方案，我最好采用一个查询的方式。