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

问题描述

我有一张下表。

  / ---------------- -------------------- \ 
 | LocID |年|出生|死亡| Abc | 
 | ------------------------------------ | 
 | 1 | 2011 | 100 | 60 | 10 | 
 | ------------------------------------ | 
 | 1 | 2012 | 98 | 70 | 20 | 
 | ..... | 
 \ ------------------------------------ / 
 
  / --------------------- \ 
 |事件| 2011 | 2012 | 
 | --------------------- | 
 |出生| 100 | 98 | 
 | --------------------- | 
 |死亡| 60 | 70 | 
 | --------------------- | 
 | Abc | 10 | 20 | 
 \ --------------------- / 
  
该表可能包含更多的字段，基于各种要求...因此，行数将取决于字段数（忽略LOCID和YEAR）。列是常数。 
只有2年（年将被给予2012年以前，然后需要显示2011年和2012年）。 
 
 
 
基本上需要将列名称作为列值和列值作为列标题... 
 
 
 
任何帮助.... 
解决方案
为了获得所需的结果，您将需要当前数据从列到行，然后将年数据从行转移到列。
 
 
 
 MySQL没有PIVOT或UNPIVOT功能，因此您将需要使用 UNION ALL 查询来解除汇总，并使用 CASE 表达式来转动。
 
 
 
如果你有一个已知数量的值，那么你可以硬编码类似的值：
  select locid，
 event，
 max（case when year = 2011 then value end）`2011`，
 max当$ = 
 
（
 select LocId，Year，'Birth'event，Birth value 
 from yt 
 union all 
选择LocId，Year，'Death'事件，死亡价值
从yt 
联合全部
选择LocId，年，Abc事件，Abc值
从yt 
）d 
组由事件; 
  
请参阅 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 
其中c.table_name ='yt'
和c.column_name不在（'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 |活动| 2011 | 2012 | 
 ------------------------------- 
 | 1 | Abc | 10 | 20 | 
 | 1 |出生| 100 | 98 | 
 | 1 |死亡| 60 | 70 | 
  
 
I have a following table.
/------------------------------------\
| LocID | Year | Birth | Death | Abc |
|------------------------------------|
|  1    | 2011 | 100   | 60    | 10  |
|------------------------------------|
|  1    | 2012 | 98    | 70    | 20  |
|.....                               |
\------------------------------------/
I need the output to be (Condition LocID = 1)
/---------------------\
| Event | 2011 | 2012 |
|---------------------|
| Birth |  100 |  98  |
|---------------------|
| Death |  60  |  70  |
|---------------------|
| Abc   |  10  |  20  |
\---------------------/
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...

Any help....
解决方案
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 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;
See SQL Fiddle with Demo.

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;
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屋！