MySQL 中的动态查询 [英] Dynamic Query in MySQL

问题描述

我有一张下表.

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

我需要输出为 (Condition LocID = 1)

I need the output to be (Condition LocID = 1)

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

根据各种要求，表格可能包含更多字段...因此行数将取决于字段数(忽略 LOCID 和 YEAR).列是恒定的.仅2年(Year will give for ex 2012 is given,然后需要显示2011和2012).

The table may contain more fields based on various requirements... Hence the number of Rows will depend upon the number of fields (ignoring LOCID and YEAR). Columns is constant. Only for 2 years (Year will be given For ex 2012 is given, then need to display 2011 and 2012).

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

Essentially need to make column name as row values and column value as Column heading...

任何帮助....

推荐答案

为了得到你想要的结果，你需要unpivot当前数据从列到行，然后再pivotyear 数据从行变成列.

In order to get the result that you want, you will need to both unpivot the current data from columns into rows and then pivot the year data from rows into columns.

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

MySQL does not have a PIVOT or UNPIVOT function, so you will need to use a UNION ALL query to unpivot and an aggregate function with a CASE expression to pivot.

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

If you have a known number of values, then you can hard-code values similar to this:

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.代码将类似于以下内容:

But if you are going to have an unknown number of values, then you will need to use a prepared statement to generate dynamic SQL. The code will be similar to the following:

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.两个查询的结果是:

See SQL Fiddle with Demo. The result for both queries is:

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

这篇关于MySQL 中的动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！