mysql - MySQL 中的动态查询

Question

我有下表。

/------------------------------------\
| LocID | Year | Birth | Death | Abc |
|------------------------------------|
|  1    | 2011 | 100   | 60    | 10  |
|------------------------------------|
|  1    | 2012 | 98    | 70    | 20  |
|.....                               |
\------------------------------------/

我需要输出为(条件 LocID = 1)

/---------------------\
| Event | 2011 | 2012 |
|---------------------|
| Birth |  100 |  98  |
|---------------------|
| Death |  60  |  70  |
|---------------------|
| Abc   |  10  |  20  |
\---------------------/

根据不同的要求，表格可能包含更多字段...因此行数将取决于字段数(忽略 LOCID 和 YEAR)。列是恒定的。只限2年(Year会给出，ex 2012给出，则需要显示2011和2012)。

本质上需要将列名作为行值，列值作为列标题...

任何帮助....

Answer 1

为了获得您想要的结果，您需要将当前数据从列反透视转换为行，然后将年数据从行转换为列.

MySQL 没有 PIVOT 或 UNPIVOT 函数，因此您需要使用 UNION ALL 查询来逆透视，并使用带有 CASE 表达式的聚合函数来进行透视。

如果您有已知数量的值，那么您可以像这样对值进行硬编码:

select locid,
  event,
  max(case when year = 2011 then value end) `2011`,
  max(case when year = 2012 then value end) `2012`
from
(
  select LocId, Year, 'Birth' event, Birth value
  from yt
  union all
  select LocId, Year, 'Death' event, Death value
  from yt
  union all
  select LocId, Year, 'Abc' event, Abc value
  from yt
) d
group by locid, event;

参见 SQL Fiddle with Demo .

但是如果您要有未知数量的值，那么您将需要使用准备好的语句来生成动态 SQL。代码将类似于以下内容:

SET @sql = NULL;
SET @sqlUnpiv = NULL;
SET @sqlPiv = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select locid, year, ''',
      c.column_name,
      ''' as event, ',
      c.column_name,
      ' as value 
      from yt '
    ) SEPARATOR ' UNION ALL '
  ) INTO @sqlUnpiv
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('LocId', 'Year')
order by c.ordinal_position;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN year = ',
      year,
      ' THEN value else null END) AS `',
      year, '`'
    )
  ) INTO @sqlPiv
FROM yt;

SET @sql 
  = CONCAT('SELECT locid,
              event, ', @sqlPiv, ' 
            from 
            ( ',  @sqlUnpiv, ' ) d
            group by locid, event');

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

参见 SQL Fiddle with Demo .两个查询的结果是:

| LOCID | EVENT | 2011 | 2012 |
-------------------------------
|     1 |   Abc |   10 |   20 |
|     1 | Birth |  100 |   98 |
|     1 | Death |   60 |   70 |