Я опубликовал вопрос, который у меня был раньше, в представлении данных определенным образом, и после двух сообщений я смог получить достаточно информации, чтобы понять, что мне нужно, это сводная таблица с динамическими столбцами.
Я смог создать статическую сводную таблицу с большим эффектом, но для серьезных сообщений они бесполезны. Вот что я сделал до сих пор:
select `title` as `Payment Method`, sum(case when MONTH(t1.`month_payment`) = 1 then amount else 0 end) Jan, sum(case when MONTH(t1.`month_payment`) = 2 then amount else 0 end) Feb, sum(case when MONTH(t1.`month_payment`) = 3 then amount else 0 end) Mar, sum(case when MONTH(t1.`month_payment`) = 4 then amount else 0 end) Apr, sum(case when MONTH(t1.`month_payment`) = 5 then amount else 0 end) May, sum(case when MONTH(t1.`month_payment`) = 6 then amount else 0 end) Jun, sum(case when MONTH(t1.`month_payment`) = 7 then amount else 0 end) Jul, sum(case when MONTH(t1.`month_payment`) = 8 then amount else 0 end) Aug, sum(case when MONTH(t1.`month_payment`) = 9 then amount else 0 end) Sep, sum(case when MONTH(t1.`month_payment`) = 10 then amount else 0 end) Oct, sum(case when MONTH(t1.`month_payment`) = 11 then amount else 0 end) Nov, sum(case when MONTH(t1.`month_payment`) = 12 then amount else 0 end) `Dec` from record_payment t1 join setting_payment_method ON setting_payment_method.id = t1.method_id where `month_payment` >= '$date_from' and `month_payment` <= '$date_to' group by title with rollup это дает мне статический отчет за период с января по декабрь, но проблема в том, что пользователь ищет с января 2015 года по декабрь 2017 года, таблица суммирует и суммирует январь 2015, 2016 и 2017 все в январе, который отображается пользователю что неправильно.
Я также создал статический стержень для годового отчета:
select `title` as `Payment Method`, sum(case when YEAR(t1.`month_payment`) = 2013 then amount else 0 end) `2013`, sum(case when YEAR(t1.`month_payment`) = 2014 then amount else 0 end) `2014`, sum(case when YEAR(t1.`month_payment`) = 2015 then amount else 0 end) `2015`, sum(case when YEAR(t1.`month_payment`) = 2016 then amount else 0 end) `2016`, sum(case when YEAR(t1.`month_payment`) = 2017 then amount else 0 end) `2017` from record_payment t1 join setting_payment_method ON setting_payment_method.id = t1.method_id where `month_payment` >= '$date_from' and `month_payment` <= '$date_to' group by title with rollup
но иногда вам нужны отчеты, которые дают вам разбивку года в месяцах в течение длительного периода (например, 12+), чтобы вы могли видеть тренд.
Моя попытка сделать автоматическую ось была не настолько успешной:
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'sum(case when month_payment = ''', month_payment, ''' then amount end) AS `', month_payment, '`' ) ) INTO @sql FROM record_payment; SET @sql = CONCAT('SELECT `title` as `Payment Method`, ', @sql, ' FROM record_payment t1 join setting_payment_method ON setting_payment_method.id = t1.method_id where `month_payment` >= '$date_from' and `month_payment` <= '$date_to' group by title with rollup'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Любая помощь приветствуется.
Я предполагаю, что ваш столбец month_payment является DATE (кстати, должен публиковать SHOW CREATE TABLE когда вы задаете вопросы SQL, поэтому нам не нужно гадать).
Но ваш первый запрос не форматирует это в год / месяц. Это также не ограничивает диапазон дат.
SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ', EXTRACT(YEAR_MONTH FROM month_payment), ' THEN AMOUNT END) AS `', EXTRACT(YEAR_MONTH FROM month_payment), '`' ) ) INTO @sql FROM record_payment WHERE month_payment BETWEEN ? AND ?
См. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_extract
Несмотря на предложение Бармара, нет причин помещать это в хранимую процедуру. Вы отметили свой вопрос PHP, и вы можете сделать это на PHP просто отлично:
<?php ...get a PDO connection... $sql = " SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN EXTRACT(YEAR_MONTH FROM month_payment) = ', EXTRACT(YEAR_MONTH FROM month_payment), ' THEN AMOUNT END) AS `', EXTRACT(YEAR_MONTH FROM month_payment), '`' ) ) AS `pivot_columns` FROM record_payment WHERE month_payment BETWEEN ? AND ? "; $stmt = $pdo->prepare($sql); $date_from = '2017-01-01'; $date_to = '2017-08-01'; $stmt->execute([$date_from, $date_to]); $row = $stmt->fetch(); $stmt->closeCursor(); $pivot_columns = $row['pivot_columns']; $sql = " SELECT title AS `Payment Method`, {$pivot_columns} FROM record_payment t1 JOIN setting_payment_method spm ON spm.id = t1.method_id WHERE month_payment BETWEEN ? AND ? GROUP BY title WITH ROLLUP "; echo $sql; $stmt = $pdo->prepare($sql); $stmt->execute([$date_from, $date_to]); $results = $stmt->fetchAll(); $stmt->closeCursor(); print_r($results);