复杂报表需要最优结构的join查询

Question

我正在为我的SaaS构建一个自定义报告模块，并且需要一些关于我正在构建的查询(MySQL和PHP)的建议。我正在动态构建一个表，返回的每一条记录都是该表上的一行。它动态地连接许多不同的表，除了我需要连接的3个表之外，所有的表都工作得很好。

所有其他表都是1对1的左连接。例如，如果我查询employee表，并试图找到department_id，它就会连接到很棒的dory。它会像这样加入：

First | Last  | Dept
John  | Smith | Toy Department

让这件事变得复杂的是，额外的3个表有一个1-多连接。同样重要的是要指出，当我进行这个连接时，它可以从2-50行数据中提取任何数据。例如，如果我正在查询employee表，并且需要加入打卡入/出表，那么结果将如下所示：

First | Last  | Time
John  | Smith | 8:00 am
John  | Smith | 12:00 pm
John  | Smith | 1:00 pm
John  | Smith | 5:00 pm

如果我做了一个右连接，名字/姓氏将为null，但我仍然有多行。

我想将它们保留在一行的原因是，对于我在拉取报告时设置的表，当它命中需要使用3个1-多个表中的一个时，它需要留在相同的数据单元格中(这不是我所能控制的，它只能发生)：

<table>
    <tr>
        <td>John</td>
        <td>Smith</td>
        <td>8:00 am<br />12:00 pm<br />..... </td>
    </tr>
</table>

我真的希望避免访问那个单元格，然后运行另一个查询来提取所有数据。原因是，我可以运行一个包含数千行和25+列的报告，它将运行25x1000行的相同查询。

当数据返回时，我希望是这样的：

First | Last  | Time_1  | Time_2   | Time_3  | Time_4
John  | Smith | 8:00 am | 12:00 pm | 1:00 pm | 5:00 pm

我的一些想法是在PHP语言中运行一个while循环，并为MySQL动态构建连接，但我遇到了一个问题，即我需要提供多少次连接该表才能获得正确的列。从技术上讲我可以这样做：

for($i = 1; $i < 50; ++$i) {
    $sql_fields .= "time_clock_{$i}.time AS Time_{$i},";
    $joins .= "LEFT JOIN time_clock AS time_clock_{$i} ON time_clock_{$i}.employee_id = emp.id";
}
$sql_fields = rtrim($sql_fields,',');

mysql_query("SELECT emp.field_blah, emp.field_blah2, {$sql_fields} FROM employees AS emp {$joins} WHERE condition = blah");

我只是担心这真的会导致MySQL的一些性能问题，以及我在那里遇到的所有疯狂的连接。谁有更好的主意？在这一点上，我会考虑任何事情。

Answer 1

不是的。

构建两个查询。一个是用1:1的元素拉动所有的人；另一个是拉动你所有的时间。然后循环遍历PHP中的时间，将它们填充到people的时间数组中。

Answer 2

<?php
    $query = mysql_query("SELECT .... FROM employees LEFT JOIN.... LEFT JOIN..."); //even include the one-many tables
    $i = 0;
    while($rs = mysql_fetch_array($query)) { //note I'm using array not assoc
        // echo the 1:1 table data
        // when I hit the 1:many field do something like this, yes $j will get reset when the pointer hits the next row....I need more time to think....
        $j = 1;
        while($j) {
            if($rs[$i + $j]['id'] == $rs[$i]['id']) {
                echo $rs[$i + $j]['Time'];
                ++$skip_fields;
            } else {
                $j = 0;
            }
            ++$j;
        }
        // then use $skip_fields to skip $j number of rows that the duplicate employee ids are on and decrement the value as the pointer moves on.
        ++$i;
    }
?>

Nevermind.....using数值只命中列，不命中行.....哦，好吧。:)