Jak Mogę zwrócić wyjście tabeli przestawnej w MySQL?
Jeśli mam tabelę MySQL wyglądającą mniej więcej tak:
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
Czy możliwe jest uruchomienie zapytania MySQL, aby uzyskać takie wyjście:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0
Chodzi o to, że pagecount
może się różnić, więc kwota kolumny wyjściowej powinna odzwierciedlać to, po jednej kolumnie dla każdej action
/pagecount
para, a następnie liczba trafień na company_name
. Nie jestem pewien, czy nazywa się to tabelą przestawną, ale ktoś to zasugerował?
9 answers
To w zasadzie jest tabelą przestawną.
Fajny samouczek Jak to osiągnąć można znaleźć tutaj: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
Radzę przeczytać ten post i dostosować To rozwiązanie do swoich potrzeb.
Aktualizacja
Po tym, jak powyższy link nie jest już dostępny czuję się zobowiązany do podania dodatkowych informacji dla wszystkich szukających tutaj odpowiedzi mysql pivot. Informatyka naprawdę miałem ogromną ilość informacji i nie będę umieszczać wszystkiego stamtąd tutaj (tym bardziej, że po prostu nie chcę kopiować ich ogromnej wiedzy), ale dam kilka rad, jak radzić sobie z tabelami przestawnymi w sposób sql ogólnie z przykładem z peku, który zadał pytanie w pierwszej kolejności.
Może link wróci wkrótce, będę miał na niego oko.
Sposób arkusza kalkulacyjnego...
Wiele osób po prostu korzysta z narzędzi takich jak MSExcel, OpenOffice lub inne arkusz kalkulacyjny-narzędzia do tego celu. Jest to poprawne rozwiązanie, wystarczy skopiować dane i użyć narzędzi oferowanych przez GUI, aby to rozwiązać.
Ale... to nie było pytanie, a to może nawet prowadzić do pewnych wad, takich jak jak, Jak uzyskać dane do arkusza kalkulacyjnego, problematyczne skalowanie i tak dalej.Sposób SQL...
Biorąc pod uwagę jego stół wygląda mniej więcej tak:
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;
Teraz zajrzyj do jego / jej pożądanej tabeli:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
Wiersze (EMAIL
, PRINT x pages
). Główne zgrupowanie to company_name
.
W celu ustalenia warunków to raczej krzyczy do korzystania z CASE
-oświadczenie. W celu grupowania przez coś, cóż, użyj ... GROUP BY
.
Podstawowy SQL dostarczający ten pivot może wyglądać mniej więcej tak:
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`;
Powinno to zapewnić pożądany rezultat bardzo szybko. Głównym minusem tego podejścia, im więcej wierszy chcesz w tabeli przestawnej, tym więcej warunków trzeba zdefiniować w SQL oświadczenie.
Z tym też można sobie poradzić, dlatego ludzie mają tendencję do używania gotowych deklaracji, procedur, liczników itp.
Kilka dodatkowych linków na ten temat temat:
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-01-04 19:00:59
Moje rozwiązanie jest w T-SQL bez żadnych pivotów:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-31 09:20:19
Dla MySQL można bezpośrednio umieścić warunki w funkcji SUM()
i będzie ona oceniana jako Boolean 0
lub 1
w ten sposób możesz liczyć na podstawie swoich kryteriów bez użycia IF/CASE
wypowiedzi
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-05-31 09:19:06
Dla dynamicznego obrotu użyj GROUP_CONCAT
z CONCAT
.
Funkcja GROUP_CONCAT łączy łańcuchy z grupy w jeden łańcuch z różnymi opcjami.
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-10 10:51:38
A stardard-SQL wersja z wykorzystaniem logiki boolean :
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;
SQL Fiddle.
Jak?TRUE OR NULL
Wydajność TRUE
.FALSE OR NULL
wydajność NULL
.NULL OR NULL
Wydajność NULL
.
Oraz COUNT
liczy tylko wartości inne niż null. Voilá.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-11-18 13:10:21
Prawidłowa odpowiedź brzmi:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-18 19:44:08
Istnieje narzędzie o nazwie MySQL Pivot Table generator, może pomóc w tworzeniu internetowej tabeli przestawnej, którą można później wyeksportować do Excela (jeśli chcesz). może działać, jeśli dane znajdują się w jednej tabeli lub w kilku tabelach .
Wystarczy podać źródło danych kolumn( obsługuje kolumny dynamiczne), wiersze, wartości w ciele tabeli oraz relację tabel (jeśli istnieją)
Strona główna tego narzędzia jest http://mysqlpivottable.net
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-01-20 19:19:42
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 ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-27 15:24:00
Jedną z opcji byłoby łączenie użycie CASE..WHEN
instrukcja jest redundantna w ramach agregacji dla bazy danych MySQL i biorąc pod uwagę potrzebne generowanie zapytań dynamicznie wraz z uzyskanie właściwego tytułu kolumny dla wyniku ustawionego jak w następującym bloku kodu:
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-11-17 15:02:50