MySQL按DESC顺序动态按主键顺序 [英] MySQL order by primary key dynamically in DESC order

问题描述

我正尝试根据其主键列以降序查询表.

I am trying to query a table in descending order based on its primary key column.

这是查询:

SELECT * FROM fdmsus_demo.vitalstats
ORDER BY 
( SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')
  AND (`TABLE_NAME` = 'vitalstats')
  AND (`COLUMN_KEY` = 'PRI') 
 ) DESC LIMIT 10; 

我希望该查询以降序返回行.但是，它没有按预期工作.我觉得DESC没有被应用到查询中. 我以这种方式编写查询的原因是，我想在我的Java代码中使用此查询，它将对参数进行函数化，例如:

I am expecting this query to return rows in descending order. However it is not working as expected. I feel DESC is not being applied to the query. The reason I am writing query in this fashion is, I wanted to use this query in my Java code and it will parameterized function like:

public void myFunction(String dbName, String tableName);

因为在运行时我将不知道什么是表的主键.但是，我仅提供2个参数dbname和tablename.因此，使用以下子查询获取主键列名称:

Because at run time I will not be knowing what will be the primary key of a table. However I am supplying only 2 parameters dbname and tablename. Hence fetching primary key column name using following sub-query:

( SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')
  AND (`TABLE_NAME` = 'vitalstats')
  AND (`COLUMN_KEY` = 'PRI') 
 )

以上查询等同于:

SELECT * FROM fdmsus_demo.vitalstats ORDER BY VitalsMasterKey DESC LIMIT 10;

返回的结果按预期的降序排列.

Which returns result in descending order as expected.

任何人都可以帮助我更正查询并以降序获取输出.任何帮助将不胜感激.

Can anyone please help me in correcting the query and get output in descending order. Any help will be appreciated.

谢谢.

推荐答案

您必须编写prepare语句

You have to write prepare statement

SET @col = '';
SELECT `COLUMN_NAME` INTO @col
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')
  AND (`TABLE_NAME` = 'vitalstats')
  AND (`COLUMN_KEY` = 'PRI');
SET @q = CONCAT('SELECT * FROM fdmsus_demo.vitalstats ORDER BY ',@col,' DESC LIMIT 10');
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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