Как создать одну строку вывода из нескольких строк ввода, используя MySQL [duplicate]

Question 1

Если у меня есть таблица MySQL, выглядящая примерно так:

company_name action pagecount ------------------------------- Company A PRINT 3 Company A PRINT 2 Company A PRINT 3 Company B EMAIL Company B PRINT 2 Company B PRINT 2 Company B PRINT 1 Company A PRINT 3

Возможно ли запустить запрос MySQL для получения вывода следующим образом:

company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0

Идея заключается в том, что pagecount может меняться, поэтому сумма столбца вывода должна отражать это, по одному столбцу для каждой пары action / pagecount, а затем количеству обращений на company_name. Я не уверен, что это называется сводной таблицей, но кто-то предложил это?

Question 2

Для динамического поворота используйте 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;

DEMO ЗДЕСЬ

Question 3

Question 4

Версия stardard-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 учитывает только ненулевые значения. Вуаля.

Question 5

[F1]

Question 6

Существует инструмент, называемый генератором таблицы MySQL Pivot, он может помочь вам создать сводную таблицу на основе Интернета, которую вы можете позже экспортировать в excel (если хотите). он может работать, если ваши данные находятся в одной таблице или в нескольких таблицах.

Все, что вам нужно сделать, это указать источник данных столбцов (он поддерживает динамические столбцы), строки, значения в тело таблицы и отношение таблицы (если они есть)

Главная страница этого инструмента - http://mysqlpivottable.net

Question 7

Question 8

Для MySQL вы можете напрямую установить условия в функцию SUM(), и она будет оценена как Boolean 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

Question 9

Правильный ответ:

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

Abhishek Gupta · Answer 1 · 15 August 2018 в 15:38

Для динамического поворота используйте 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;

DEMO ЗДЕСЬ

27

ответ дан Abhishek Gupta 15 August 2018 в 15:38

1

Производительность конкатенации строк ужасна ....... – Pacerier 2 April 2015 в 13:33
2

Pacerier, истинный человек, но для динамического поворота его одного из лучших подходов – Abhishek Gupta 19 August 2015 в 10:18
3

Это хорошо работает, если у вас есть много значений в действиях & quot; или ожидают, что этот список будет расти с течением времени, так как написание case-заявления для каждого значения может занять много времени и сложно поддерживать актуальность. – Patrick Murphy 2 February 2018 в 07:53

Erwin Brandstetter · Answer 2 · 15 August 2018 в 15:38

Версия stardard-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 учитывает только ненулевые значения. Вуаля.

16

ответ дан Erwin Brandstetter 15 August 2018 в 15:38

1

Этот режим имеет отличную производительность на больших таблицах – Marcelo Amorim 25 March 2015 в 08:02
2

@Erwin, Но как вы знаете, что есть три столбца? Что, если их 5? 10? 20? – Pacerier 2 April 2015 в 13:34
3

@Pacerier: Пример в вопросе, кажется, предполагает это. В любом случае SQL требует знать тип возврата. a полностью динамический запрос невозможен. Если количество столбцов вывода может варьироваться, вам нужно выполнить два шага: 1-й построить запрос, 2-й: выполнить его. – Erwin Brandstetter 2 April 2015 в 13:59

irba · Answer 3 · 15 August 2018 в 15:38

[F1]

2

ответ дан irba 15 August 2018 в 15:38

Peter Green · Answer 4 · 15 August 2018 в 15:38

Существует инструмент, называемый генератором таблицы MySQL Pivot, он может помочь вам создать сводную таблицу на основе Интернета, которую вы можете позже экспортировать в excel (если хотите). он может работать, если ваши данные находятся в одной таблице или в нескольких таблицах.

Все, что вам нужно сделать, это указать источник данных столбцов (он поддерживает динамические столбцы), строки, значения в тело таблицы и отношение таблицы (если они есть)

Главная страница этого инструмента - http://mysqlpivottable.net

shA.t · Answer 5 · 15 August 2018 в 15:38

Для MySQL вы можете напрямую установить условия в функцию SUM(), и она будет оценена как Boolean 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

50

ответ дан shA.t 15 August 2018 в 15:38

1

Это работает так же хорошо и в MySql! ... и даже рифмует ;-) – cars10m 27 September 2013 в 21:04
2

эпопея! Спасибо! – fubo 19 December 2013 в 16:15
3

Вау, это гораздо более элегантное решение, если оно работает одинаково. – cerd 26 September 2014 в 05:16
4

Это очень аккуратно. Вы знаете, совместимы ли стандарты на других платформах (например, Postgres)? – itsols 9 October 2014 в 18:37
5

@itsols Нет для него только для Mysql – M Khalid Junaid 10 October 2014 в 00:57
6

Это работает для меня даже на PostgreSQL. Я предпочитаю этот метод, кроме использования расширения кросс-таблицы в Postgres, поскольку это cleaner – itsols 10 October 2014 в 08:06
7

@itsols: Я добавил еще одну стандартную версию SQL . Postgres также имеет функцию crosstab(). – Erwin Brandstetter 17 November 2014 в 13:05
8

Работает также и для SQLite – SBF 9 January 2017 в 15:10

Talha · Answer 6 · 15 August 2018 в 15:38

Правильный ответ:

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

Это просто пример, который у вас был под рукой? Какова структура таблицы other_value? — Patrick Murphy, 2 February 2018 в 07:55

Как создать одну строку вывода из нескольких строк ввода, используя MySQL [duplicate]

6 ответов

DEMO

Другие вопросы по тегам:

Похожие вопросы: