問題描述
如果我有一個像這樣的 MySQL 表:
<前>company_name 操作頁數-------------------------------公司 A 打印 3公司 A 打印 2公司 A 打印 3B 公司 EMAILB 公司打印 2B 公司打印 2B 公司打印 1公司 A 打印 3是否可以運行 MySQL 查詢來獲得這樣的輸出:
<前>company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages-------------------------------------------------------------公司A 0 0 1 3公司B 1 1 2 0這個想法是 pagecount 可以變化，所以輸出列的數量應該反映這一點，每個 action/pagecount 對一列，然后每個 company_name 的點擊次數.我不確定這是否稱為數據透視表，但有人建議這樣做?
這基本上是一個數據透視表.
可以在此處找到有關如何實現此目標的不錯教程:http://www.artfulsoftware.com/infotree/qrytip.php?id=78
我建議您閱讀這篇文章并根據您的需求調整此解決方案.
更新
在上面的鏈接目前不再可用之后，我覺得有必要為所有在這里搜索 mysql 數據透視答案的人提供一些額外的信息.它確實有大量的信息，我不會把所有的東西都放在這里(甚至更多，因為我只是不想復制他們的豐富知識)，但我會就如何處理pivot提供一些建議以最先提出問題的peku為例，通常以sql方式表.
也許鏈接很快就會回來，我會留意的.
電子表格方式...
為此目的，許多人只是使用 MSExcel、OpenOffice 或其他電子表格工具等工具.這是一個有效的解決方案，只需將數據復制到那里并使用 GUI 提供的工具來解決這個問題.
但是……這不是問題，它甚至可能導致一些缺點，例如如何將數據導入電子表格、有問題的縮放等等.
SQL 方式...
鑒于他的桌子看起來像這樣:
創建表`test_pivot`(`pid` bigint(20) NOT NULL AUTO_INCREMENT,`company_name` varchar(32) 默認為空，`action` varchar(16) 默認為空，`pagecount` bigint(20) 默認為空，主鍵(`pid`)) 引擎=MyISAM;現在查看他/她想要的表格:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages-------------------------------------------------------------公司A 0 0 1 3公司B 1 1 2 0行(EMAIL、PRINT x pages)類似于條件.主要分組是按company_name.
為了設置條件，使用CASE-語句.為了按某些內容分組，請使用 ... GROUP BY.
提供此數據透視表的基本 SQL 可能如下所示:
SELECT P.`company_name`,數數(案件WHEN P.`action`='EMAIL'那么 1否則為空結尾) 作為電子郵件"，數數(案件當 P.`action`='PRINT' AND P.`pagecount` = '1'THEN P.`pagecount`否則為空結尾) 作為打印 1 頁"，數數(案件當 P.`action`='PRINT' AND P.`pagecount` = '2'THEN P.`pagecount`否則為空結尾) 作為打印 2 頁"，數數(案件當 P.`action`='PRINT' AND P.`pagecount` = '3'THEN P.`pagecount`否則為空結尾) AS '打印 3 頁'FROM test_pivot PGROUP BY P.`company_name`;這應該會非常快地提供所需的結果.這種方法的主要缺點是，您希望數據透視表中的行越多，您需要在 SQL 語句中定義的條件就越多.
這也可以解決，因此人們傾向于使用準備好的語句、例程、計數器等.
有關此主題的一些其他鏈接:
- http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
- http://www.codeproject.com/文章/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
- http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
- https://codingsight.com/pivot-tables-in-mysql/
If I have a MySQL table looking something like this:
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
Is it possible to run a MySQL query to get output like this:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages ------------------------------------------------------------- CompanyA 0 0 1 3 CompanyB 1 1 2 0
The idea is that pagecount can vary so the output column amount should reflect that, one column for each action/pagecount pair and then number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?
This basically is a pivot table.
A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78
I advise reading this post and adapt this solution to your needs.
Update
After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won't put everything from there in here (even more since I just don't want to copy their vast knowledge), but I'll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.
Maybe the link comes back soon, I'll keep an eye out for it.
The spreadsheet way...
Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.
But... this wasn't the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.
The SQL way...
Given his table looks something like this:
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;
Now look into his/her desired table:
company_name EMAIL PRINT 1 pages PRINT 2 pages PRINT 3 pages
-------------------------------------------------------------
CompanyA 0 0 1 3
CompanyB 1 1 2 0
The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.
In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use ... GROUP BY.
The basic SQL providing this pivot can look something like this:
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`;
This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.
This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.
Some additional links about this topic:
- http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html
- http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
- http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf
- https://codingsight.com/pivot-tables-in-mysql/
這篇關于如何在 MySQL 中返回數據透視表輸出?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！