mysql如何动态查询,MySQL中的动态查询

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;

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 |