php - 基于 mysql 查询的报告生成

Question

我有这 3 个表，即 form、form_responses、metrics，结构如下

form
 ->id
 ->phone
 ->calldatetime


form_reponses
 ->id
 ->form_id
 ->metrics_id
 ->response


 metrics
  ->id
  ->description
  ->question

我想用这样的格式制作报告

 |Metrics Description|Metrics Question|Phone1|Phone2|Phone3|Phone4
 |___________________|________________|______|______|______|______
 | Sample            |  Sample        | Yes  | Yes  | Yes  | Yes

仅通过 mysql 查询就可以得到这个输出吗？请注意，Phone1、Phone2、Phone3... 是水平缩放的。最初我需要在 excel 文件中输出我已经尝试过使用 Laravel PHP 和 http://www.maatwebsite.nl/laravel-excel/docs

$query = "SELECT id, phone FROM qcv.forms WHERE calldatetime >= '$from' AND calldatetime <= '$to' ORDER BY id ASC LIMIT 250 ;";
$phone = DB::connection('mysql')->select($query);

$metrics = Metric::all();
$metric_start = 10;

$start = "D";
$count = 10;
foreach ($phone as $key => $value2) // Populate Phone Numbers Horizontally
{
    $sheet->cell($start.'9', $value2->phone);


    // This will fill the responses for each number
    foreach ($metrics as $key => $value)
    {
       $responses = FormResponses::where('form_id', '=', $value2->id)->where('metrics_id', '=', $value->id)->get();
        $sheet->cell($start.$count, $responses[0]->response);
       $count++;
    }
    $start++;
    $count = 10;
}

foreach ($metrics as $key => $value) // Populate Metrics Vertically
{
   $sheet->cell('C'.$metric_start, $value->question);
   $sheet->cell('B'.$metric_start, $value->description);
   $sheet->cell('A'.$metric_start, $value->metrics_name);
   $metric_start++;
}

但是好像这个方法真的很慢，尤其是在处理的时候，所以我想知道我是否可以单独在 mysql 命令中输出？

Answer 1

要使用 SQL 在一对多关系中获取每行的多个子记录，您必须使用子查询:

SELECT
    m.description,
    m.question,
    (select phone from form f1 where f1.id = m.id and ...
        /* some other unique criteria */) as Phone1,
    (select phone from form f2 where f2.id = m.id and ...
        /* some other unique criteria */) as Phone2,
    (select phone from form f3 where f3.id = m.id and ...
        /* some other unique criteria */) as Phone3,
    (select phone from form f4 where f4.id = m.id and ...
        /* some other unique criteria */) as Phone4
FROM metrics m

但是...您可能没有任何列以这种方式唯一标识每个表单...并且您的 SQL 引擎可能不允许子查询的第 3 级嵌套(这是从中单独选择记录的另一种方式同一张 table )。

所以这是另一种可行的变体。它应该稍微少一些代码和更少的数据库连接，所以它应该表现得更好，即使你觉得它不太直观。这是 SQL 部分:

SELECT
    m.description,
    m.question,
    f.phone
FROM metrics m
INNER JOIN form f ON f.id = m.id

然后在 PHP 中:

$lastid = '';
$phone_count = 0;
foreach ($record as $key => $value) {
    $phone[$phone_count] = $value->phone;
    $phone_count++;
    if ($lastid != $value->id) {
        // new record
        $sheet->cell ( /* whatever */ );
        $phone_count = 0;
    }
    $lastid = $value->id;
}