php - 使用 MySQL 和 PHP 的数据透视报告

Question

我有 3 个 mysql 表，如下所示:表-01:公司

id | comp_name | year
------------------------
 1 | Novartis  | 2015
 2 | Roche     | 2015
 3 | Glaxco    | 2015

表-02:产品

id | fullname
 1 | Paracetamol
 2 | Eesomaprazol 
 3 | Phenobuterol

表03:出价

id | comp_id | prod_id | rate | year
 1 |       1 |       1 |  2.0 | 2015
 2 |       1 |       2 |  4.2 | 2015
 3 |       1 |       3 |  2.3 | 2015
 4 |       2 |       1 |  3.0 | 2015
 5 |       2 |       2 |  4.0 | 2015
 6 |       2 |       3 |  2.5 | 2015
 7 |       3 |       1 |  2.3 | 2015
 8 |       3 |       2 |  4.5 | 2015
 9 |       3 |       3 |  2.8 | 2015

从上面的三个表中，我想得到以下由 php 获取的汇总表:

fullname     | Novartis | Roche | Glaxco
Paracetamol  |      2.0 |   3.0 | 2.3
Eesomaprazol |      4.2 |   4.0 | 4.5 
Phenobuterol |      2.3 |   2.5 | 2.8

我不太了解如何使用 MySQL 表和准备好的语句从 php 创建数据透视报告。另外，我必须从 php 端实现这一点(而不是从 MySQL 端的 SQL 查询)。

我不知道如何实现这一点，虽然我知道基本的 MySQL 查询并且可以获取和获取正常的 MySQL 数据。但是，如果我能从以上三个表的数据中得到一个实用的想法，我就可以从任何大而复杂的数据结构出发。

Answer 1

您必须动态生成要转换的列，您可以在 mysql 中使用它:

#1st part
SET @sql = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT('sum(case when comp_name = ''', comp_name, ''' then rate else 0 end) as ',
                    replace(comp_name, ' ', ''))
)
INTO @sql
FROM company;

#2nd part
SET @sql = CONCAT('SELECT
  fullname,
  ', @sql, '
FROM bid
  JOIN product ON product.id = bid.prod_id
  JOIN company ON company.id = bid.comp_id
GROUP BY fullname');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

---

解释:

#1st 部分

这部分生成 SQL 以对比率求和并生成列标题，因此在 @sql 中您将拥有:

sum(case when comp_name = 'Novartis' then rate else 0 end) as Novartis,sum(case when comp_name = 'Roche' then rate else 0 end) as Roche,sum(case when comp_name = 'Glaxco' then rate else 0 end) as Glaxco

#2nd 部分

这部分生成并执行主查询，生成输出。

---

和输出(对我来说):

+--------------+----------+-------+--------+
| fullname     | Novartis | Roche | Glaxco |
+--------------+----------+-------+--------+
| Eesomaprazol |      4.2 |     4 |    4.5 |
| Paracetamol  |        2 |     3 |    2.3 |
| Phenobuterol |      2.3 |   2.5 |    2.8 |
+--------------+----------+-------+--------+

---

PHP 代码(我想你正在使用 PDO):

$dbh = new PDO($dsn, $user, $password);
$dbh->query("SET @sql = NULL");
$dbh->query("SELECT GROUP_CONCAT(
    DISTINCT CONCAT('sum(case when comp_name = ''', comp_name, ''' then rate else 0 end) as ',
                    replace(comp_name, ' ', ''))
    )
    INTO @sql
    FROM company"
);
$dbh->query("SET @sql = CONCAT('SELECT
      fullname,
      ', @sql, '
    FROM bid
      JOIN product ON product.id = bid.prod_id
      JOIN company ON company.id = bid.comp_id
    GROUP BY fullname')"
);
$dbh->query("PREPARE stmt FROM @sql");
$result = $dbh->query("EXECUTE stmt")->fetchAll(PDO::FETCH_ASSOC);
$dbh->query("DEALLOCATE PREPARE stmt");

print_r($result);

并输出:

Array
(
    [0] => Array
        (
            [fullname] => Eesomaprazol
            [Novartis] => 4.2
            [Roche] => 4
            [Glaxco] => 4.5
        )

    [1] => Array
        (
            [fullname] => Paracetamol
            [Novartis] => 2
            [Roche] => 3
            [Glaxco] => 2.3
        )

    [2] => Array
        (
            [fullname] => Phenobuterol
            [Novartis] => 2.3
            [Roche] => 2.5
            [Glaxco] => 2.8
        )

)

变量@sql在当前数据库连接中是可见的，所以你可以使用它。