我应该使用左连接、右连接和派生表、不同的表设计，还是需要编写代码?(复制)

Possible Duplicate:
MySQL pivot table

可能重复:MySQL数据透视表

Should I be using a LEFT JOIN using derived tables, a RIGHT JOIN using derived tables, a different table design, or do I need to query the table and write Python code to produce the desired output shown below?

我应该使用左连接使用派生表、右连接使用派生表、不同的表设计，还是需要查询表并编写Python代码以生成如下所示的所需输出?

The output I would like to get to would yield the following output example:

我希望得到的输出将产生以下输出示例:

==========================
| 2005   |  2006 |  2007 |  <--- These headings are not necessary
==========================
|   A    |  A    |  A    |
--------------------------
|  AA    |  AA   |  AA   |
--------------------------
|  BB    |  BB   |       |
--------------------------
|  C     |       |   C   |
--------------------------

The data for the query is in a two column table containing (symbol, year):

查询的数据在包含(符号，年份)的两列表中:

====================
|  Symbol |  Year  |
====================
|   A     |  2005  |
--------------------
|   AA    |  2005  |
--------------------
|   BB    |  2005  |
--------------------
|   C     |  2005  |
--------------------
|   A     |  2006  |
--------------------
|   AA    |  2006  |
--------------------
|   BB    |  2006  |
--------------------
|   A     |  2007  |
--------------------
|   AA    |  2007  |
--------------------
|   C     |  2007  |
--------------------

1 个解决方案

#1

SELECT MAX(CASE WHEN year = 2005 THEN Symbol ELSE NULL END) `2005`,
       MAX(CASE WHEN year = 2006 THEN Symbol ELSE NULL END) `2006`,
       MAX(CASE WHEN year = 2007 THEN Symbol ELSE NULL END) `2007`
FROM tableName
GROUP BY Symbol

SQLFiddle Demo
SQLFiddle演示

If you have unknown values of year, a Dynamic SQL Query is very much preferred on this.

如果您有未知的年份值，那么动态SQL查询在这一点上非常受欢迎。

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN year = ',
      year,
      ' THEN Symbol ELSE NULL end) AS ',
      CONCAT('`', year, '`')
    )
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ', @sql, ' 
                   FROM tableName 
                   GROUP BY Symbol');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo
SQLFiddle演示

if you want to show empty string instead of null value, COALESCE is required.

如果希望显示空字符串而不是空值，则需要合并。

SQLFiddle Demo
SQLFiddle演示
SQLFiddle Demo (Dynamic Query)
SQLFiddle演示(动态查询)

#1

SELECT MAX(CASE WHEN year = 2005 THEN Symbol ELSE NULL END) `2005`,
       MAX(CASE WHEN year = 2006 THEN Symbol ELSE NULL END) `2006`,
       MAX(CASE WHEN year = 2007 THEN Symbol ELSE NULL END) `2007`
FROM tableName
GROUP BY Symbol

SQLFiddle Demo
SQLFiddle演示