Сводный отчет с MySQL и переполнением стека
У меня есть 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
Я не имею большого представления о том, как создать сводный отчет из php, используя таблицу MySQL и подготовленный оператор. Кроме того, я должен достичь этого с конца php (не с SQL-запроса конца MySQL).
Я понятия не имею, как этого добиться, хотя я знаю базовый запрос MySQL и могу получать и извлекать обычные данные MySQL. Но если я смогу получить практическое представление о данных из трех таблиц выше, я смогу перейти к любой большой и сложной структуре данных.
Решение
Вы должны динамически генерировать столбцы для поворота, вы можете использовать это в 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;
Объяснение:
# 1 часть
Эта часть генерирует 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
# 2 часть
Эта часть генерирует и выполняет основной запрос, который генерирует вывод.
И вывод (для меня):
+--------------+----------+-------+--------+
| 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 виден в текущем соединении БД, так что вы можете использовать его.
Другие решения
Других решений пока нет …