Сводная таблица MySQL
Если у меня MySQL таблица выглядит примерно так:
название страницы действия компании ------------------------------- Компания А ПРИНТ 3 Компания А ПРИНТ 2 Компания А ПРИНТ 3 Компания B EMAIL Компания Б ПРИНТ 2 Компания Б ПРИНТ 2 Компания Б ПРИНТ 1 Компания А ПРИНТ 3
Можно ли выполнить запрос MySQL, чтобы получить вывод, как это:
название компании EMAIL PRINT 1 страница PRINT 2 страницы PRINT 3 страницы -------------------------------------------------- ----------- Компания A 0 0 1 3 CompanyB 1 1 2 0
Идея в том, что pagecount может варьироваться, так что количество выходных столбцов должно отражать это, один столбец для каждого action/pagecount пара, а затем количество хитов в company_name, Я не уверен, что это называется сводной таблицей, но кто-то предложил это?
11 ответов
Это в основном сводная таблица.
Хороший учебник о том, как этого добиться, можно найти здесь: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Я советую прочитать этот пост и адаптировать это решение к вашим потребностям.
Обновить
После того, как ссылка выше в настоящее время больше не доступна, я чувствую себя обязанным предоставить некоторую дополнительную информацию для всех вас, ищущих ответы на сводку mysql здесь. В нем действительно было огромное количество информации, и я не буду помещать все оттуда сюда (даже больше, так как я просто не хочу копировать их обширные знания), но я дам несколько советов о том, как справиться с Pivot Таблицы SQL путь, как правило, с примером из peku, который задал вопрос в первую очередь.
Возможно ссылка скоро вернется, я буду следить за этим.
Способ работы с электронными таблицами...
Многие люди просто используют такой инструмент, как MSExcel, OpenOffice или другие инструменты для работы с электронными таблицами. Это правильное решение, просто скопируйте данные туда и используйте инструменты, которые предлагает GUI, чтобы решить эту проблему.
Но... это был не вопрос, и это может даже привести к некоторым недостаткам, таким как, как получить данные в электронную таблицу, проблематичное масштабирование и так далее.
SQL путь...
Учитывая, что его таблица выглядит примерно так:
CREATE TABLE `test_pivot` (
`pid` bigint(20) NOT NULL AUTO_INCREMENT,
`company_name` varchar(32) DEFAULT NULL,
`action` varchar(16) DEFAULT NULL,
`pagecount` bigint(20) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=MyISAM;
Теперь загляните в его / ее желаемую таблицу:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
Строки (EMAIL, PRINT x pages) напоминают условия. Основная группировка по company_name,
Для того, чтобы установить условия, это скорее крики для использования CASE -заявление. Чтобы что-то сгруппировать, ну, используйте... GROUP BY,
Базовый SQL, обеспечивающий эту опору, может выглядеть примерно так:
SELECT P.`company_name`,
COUNT(
CASE
WHEN P.`action`='EMAIL'
THEN 1
ELSE NULL
END
) AS 'EMAIL',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '1'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 1 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '2'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 2 pages',
COUNT(
CASE
WHEN P.`action`='PRINT' AND P.`pagecount` = '3'
THEN P.`pagecount`
ELSE NULL
END
) AS 'PRINT 3 pages'
FROM test_pivot P
GROUP BY P.`company_name`;
Это должно обеспечить желаемый результат очень быстро. Основным недостатком этого подхода является то, что чем больше строк вы хотите разместить в сводной таблице, тем больше условий вы должны определить в своем операторе SQL.
С этим тоже можно бороться, поэтому люди склонны использовать готовые операторы, процедуры, счетчики и тому подобное.
Некоторые дополнительные ссылки на эту тему:
Мое решение в T-SQL без каких-либо опорных точек:
SELECT
CompanyName,
SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM
Company
GROUP BY
CompanyName
Для MySQL вы можете непосредственно поставить условия в SUM() функция, и она будет оценена как логическая 0 или же 1 и, таким образом, вы можете рассчитывать на основе ваших критериев без использования IF/CASE заявления
SELECT
company_name,
SUM(action = 'EMAIL')AS Email,
SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name
DEMO
Для динамического поворота используйте GROUP_CONCAT с CONCAT, Функция GROUP_CONCAT объединяет строки из группы в одну строку с различными параметрами.
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN action = "',
action,'" AND ',
(CASE WHEN pagecount IS NOT NULL
THEN CONCAT("pagecount = ",pagecount)
ELSE pagecount IS NULL END),
' THEN 1 ELSE 0 end) AS ',
action, IFNULL(pagecount,'')
)
)
INTO @sql
FROM
t;
SET @sql = CONCAT('SELECT company_name, ', @sql, '
FROM t
GROUP BY company_name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Версия стандартного SQL с использованием логической логики:
SELECT company_name
, COUNT(action = 'EMAIL' OR NULL) AS "Email"
, COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
, COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
, COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM tbl
GROUP BY company_name;
Как?
TRUE OR NULL доходность TRUE, FALSE OR NULL доходность NULL, NULL OR NULL доходность NULL,
А также COUNT учитывает только ненулевые значения. Вуаля.
Правильный ответ:
select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id
Существует инструмент под названием MySQL Pivot Table Generator, который может помочь вам создать сводную таблицу на основе веб-интерфейса, которую вы сможете позже экспортировать в Excel(если хотите). это может работать, если ваши данные находятся в одной таблице или в нескольких таблицах.
Все, что вам нужно сделать, это указать источник данных для столбцов (он поддерживает динамические столбцы), строки, значения в теле таблицы и отношения таблицы (если они есть) 
Домашняя страница этого инструмента http://mysqlpivottable.net/
select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E
from
(select t2.name as name,
case when t2.prodid = 1 then t2.counts
else 0 end prod_A,
case when t2.prodid = 2 then t2.counts
else 0 end prod_B,
case when t2.prodid = 3 then t2.counts
else 0 end prod_C,
case when t2.prodid = 4 then t2.counts
else 0 end prod_D,
case when t2.prodid = "5" then t2.counts
else 0 end prod_E
from
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3
group by t3.name ;
Один из вариантов - комбинировать использованиеCASE..WHENОператор избыточен в агрегации для базы данных MySQL и учитывает необходимость генерации запроса динамически вместе с получением правильного заголовка столбца для набора результатов, как в следующем блоке кода:
SET @sql = NULL;
SELECT GROUP_CONCAT(
CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
)
INTO @sql
FROM
(
SELECT DISTINCT `action`,
IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
FROM `tab`
ORDER BY CONCAT(action,pc0)
) t;
SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Используйте запрос с условным агрегированием
Вот запрос:
SELECT
company_name,
SUM(CASE WHEN action = 'EMAIL' THEN 1 ELSE 0 END) AS EMAIL,
SUM(CASE WHEN action = 'PRINT' AND pagecount = 1 THEN 1 ELSE 0 END) AS `PRINT 1 pages`,
SUM(CASE WHEN action = 'PRINT' AND pagecount = 2 THEN 1 ELSE 0 END) AS `PRINT 2 pages`,
SUM(CASE WHEN action = 'PRINT' AND pagecount = 3 THEN 1 ELSE 0 END) AS `PRINT 3 pages`
FROM
your_table_name
GROUP BY
company_name;
Просто заменитеyour_table_nameс фактическим именем вашей таблицы MySQL.
Что будет делать Query?
его запрос даст вам желаемый результат, где каждый столбец представляет пару действие/количество страниц, а количество обращений на имя компании правильно подсчитывается.
Надеюсь, поможет.
SELECT company_name, SUM(CASE WHEN ACTION = 'Email' THEN 1 ELSE 0 END) AS "Email",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 1 THEN 1 ELSE 0 END) AS "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;