mysql表从后往前_从MySQL中选择表的最后N#列

您可以通过准备好的陈述实现这一点您的查询将如下所示：

SELECT

CONCAT('SELECT ',

GROUP_CONCAT(COLUMN_NAME),

' FROM test')

FROM

(SELECT

COLUMN_NAME,

ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_SCHEMA='test'

AND

TABLE_NAME='test'

ORDER BY

ORDINAL_POSITION DESC LIMIT 10) AS ord_desc

ORDER BY

ord_desc.ORDINAL_POSITION

– 这将创建一个内容如下的SQL：

SELECT date,title FROM test

(在上面的示例中,我在选择中有2列,可以在此部分进行调整：ORDER BY ORDINAL_POSITION DESC LIMIT 10)

所以你需要做的就是准备这个陈述.就我而言：

SQL：

mysql> set @sql=(SELECT CONCAT('SELECT ', GROUP_CONCAT(COLUMN_NAME), ' FROM test') FROM (SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='test' ORDER BY ORDINAL_POSITION DESC LIMIT 2) AS ord_desc ORDER BY ord_desc.ORDINAL_POSITION);

Query OK, 0 rows affected (0.02 sec)

准备：

mysql> prepare stmt from @sql;

Query OK, 0 rows affected (0.00 sec)

结果：

mysql> execute stmt;

+------------+--------------+

| date | title |

+------------+--------------+

| 2014-02-04 | my event 001 |

| 2014-02-04 | my event 002 |

| 2014-02-05 | my event 003 |

| 2014-02-05 | my event 004 |

| 2014-02-05 | my event 005 |

| 2014-02-07 | my event 006 |

| 2014-02-07 | my event 007 |

+------------+--------------+

7 rows in set (0.00 sec)