SQL按字母顺序列出所有列名称 [英] SQL Listing all column names alphabetically

问题描述

我知道

SELECT * FROM Table

将列出表中的所有列，但我有兴趣按字母顺序列出这些列.

will list all columns in the table, but I am interested in listing the columns in alphabetical order.

说，我有三列，分别是姓名"，年龄"和性别".

Say, I have three columns, "name", "age" and "sex".

我希望各列采用以下格式

I want the columns organized in the format

|age| |name| |sex|

是否可以使用SQL做到这一点?

Is it possible to do this with SQL?

推荐答案

是，否:-)

SQL本身并不关心列的排列顺序，但是，如果您要使用的话:

SQL itself doesn't care what order the columns come out in but, if you were to use:

select age, name, sex from ...

您会发现它们可能按此顺序出现(尽管我不确定SQL标准是否要求这样做).

you'd find that they probably came out in that order (though I'm not sure SQL standards mandate this).

现在您可能不想这样做，但是有时候生活不公平:-)

Now you may not want to do that but sometimes life isn't fair :-)

您还可以使用DBMS数据定义表动态构建查询.这是不可移植的，但是大多数DBMS都提供了这些表(例如DB/2的SYSIBM.SYSCOLUMNS)，您可以从那里以有序的方式选择列名.像这样:

You also have the other possibility of using the DBMS data definition tables to dynamically construct a query. This is non-portable but most DBMS' supply these table (such as DB/2's SYSIBM.SYSCOLUMNS) and you can select the column names from there in an ordered fashion. Something like:

select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;

然后，您使用 that 查询的结果来构造实际查询:

Then you use the results of that query to construct the real query:

query1 = "select column_name from sysibm.syscolumns" +
         " where owner = 'pax' and table_name = 'movies'" +
         " order by column_name"
rs = exec(query1)
query2 = "select"
sep = " "
foreach colm in rs:
    query2 += sep + colm["column_name"]
    sep = ", "
query2 += " from movies order by rating"
rs = exec(query2)
// Now you have the rs recordset with sorted columns.

但是，您实际上应该认真检查所有选择*的查询-在大多数情况下，这是不必要且效率低下的.数据的呈现应该由呈现层完成，而不是DBMS本身-应该让DBMS以尽可能有效的方式返回数据.

However, you really should critically examine all queries that select * - in the vast majority of cases, it's unnecessary and inefficient. And presentation of the data is something that should probably be done by the presentation layer, not the DBMS itself - the DBMS should be left to return the data in as efficient a manner as possible.

这篇关于SQL按字母顺序列出所有列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！