从MySQL选择表的最后N#列 [英] Select last N# of columns of a table from MySQL
问题描述
我有超过35列的表,前20列是固定的,并且每个表中的列号都不同.我需要从表中选择最后10列,例如,我该如何实现?就像此查询返回前20条记录
I have tables with more than 35 columns, the first 20 columns are fixed and the column number is different in each table. I need to select the last 10 columns for example from a table, how can I achieve that? Just like this query returns the top 20 records
select * from table1 limit 10;
我想对列做同样的事情,我的意思是在查询中返回列名称,然后在另一个查询中使用这些名称,例如:
I want to do the same with columns I mean return the column names in a query and then use those names in another query, something like:
SELECT (SELECT column_name FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table1' ) FROM table1;
推荐答案
您可以通过准备好的语句来实现.您的查询将如下所示:
You may achieve that with prepared statements. Your query will look like:
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:
-this will create an SQL with content like:
SELECT date,title FROM test
(在上面的示例中,我在选择中有2列,可以在此部分进行调整:ORDER BY ORDINAL_POSITION DESC LIMIT 10
)
(in sample above I had 2 column within selection, that can be adjusted in this part: ORDER BY ORDINAL_POSITION DESC LIMIT 10
)
因此,您所需要做的就是准备此语句.就我而言:
So all you need to do is to prepare this statement. In my case:
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)
这篇关于从MySQL选择表的最后N#列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!