我知道。
SELECT * FROM Table
以下代码将列出表中的所有列,但我希望按字母顺序列出列。
假设我有三个列,“name”,“age”和“sex”。
我想以以下格式组织列:
|age| |name| |sex|
使用SQL可以实现这个吗?
SELECT * FROM Table
以下代码将列出表中的所有列,但我希望按字母顺序列出列。
假设我有三个列,“name”,“age”和“sex”。
我想以以下格式组织列:
|age| |name| |sex|
使用SQL可以实现这个吗?
这将生成一个查询,在选择语句中按字母顺序排列所有列。
DECLARE @QUERY VARCHAR(2000)
DECLARE @TABLENAME VARCHAR(50) = '<YOU_TABLE>'
SET @QUERY = 'SELECT '
SELECT @QUERY = @QUERY + Column_name + ',
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
ORDER BY Column_name
SET @QUERY = LEFT(@QUERY, LEN(@QUERY) - 4) + '
FROM '+ @TABLENAME
PRINT @QUERY
EXEC(@QUERY)
是的,也不是。
SQL本身并不关心列的输出顺序,但是,如果你使用:
select age, name, sex from ...
如果你执行这些语句,你会发现它们可能按照那个顺序输出(尽管我不确定 SQL 标准是否要求这样做)。
现在,你可能不希望这样做,但有时生活就是不公平的:-)
你还有另一种可能性,就是使用数据库管理系统数据定义表来动态构建查询。这是非可移植的,但大多数数据库管理系统都提供这些表(例如 DB/2 的 SYSIBM.SYSCOLUMNS
),你可以按照一定的顺序从中选择列名。像这样:
select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;
然后您使用该查询的结果构建真实查询:
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应该尽可能高效地返回数据。
SELECT *
时,SQL-92 标准规定列按照它们在表中的序号升序引用。相关章节为4.8(列)和7.9(查询规范)。 - onedaywhenSELECT *
,也无法对列名进行排序。SELECT age, name, sex FROM
。在SQL层面上,这并不重要。这也不会影响任何客户端代码对象。
如果这很重要,那么在向客户端呈现数据时进行排序。
很抱歉,事情就是这样...
SELECT *
时,列按照它们在表中的序号升序引用。相关章节为4.8(列)和7.9(查询规范)。我不知道是否有任何供应商扩展标准,允许以任何其他顺序返回列,可能是因为通常不鼓励使用SELECT *
。FROM
子句中引用单个表时,此方法才有效。如果引用了两个表,则SELECT *
将按照序数位置顺序返回第一个表的列,然后是第二个表的列,因此完整结果集的列可能不是按字母顺序排列的。如果你只是想在SQL Server中查找一个列
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTableThatCouldMaybeNeedNormalising'
order by COLUMN_NAME
您可以只指定要选择的列:
SELECT age, name, sex FROM Table
列将按照您在查询中指定的顺序显示。
ORDER BY COLUMN_NAME ASC;
SELECT *
语句。DELIMITER ;;
DROP PROCEDURE IF EXISTS ALPHABETISE_TABLE_COLUMNS;
CREATE PROCEDURE ALPHABETISE_TABLE_COLUMNS(IN database_name VARCHAR(64), IN table_name_string VARCHAR(64), IN index_name_string VARCHAR(64))
BEGIN
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE col_name VARCHAR(30) DEFAULT "";
DECLARE col_datatype VARCHAR(10) DEFAULT "";
DECLARE previous_col VARCHAR(30) DEFAULT col_name;
SELECT COUNT(*)
FROM
(SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = table_name_string) AS TEMP
INTO n;
SET @Q= CONCAT('ALTER TABLE `',database_name,'`.`',table_name_string,'` CHANGE COLUMN `',index_name_string,'` `',index_name_string,'` BIGINT(20) NOT NULL FIRST');
PREPARE exe FROM @Q;
EXECUTE exe;
DEALLOCATE PREPARE exe;
SET n = n-1;
SET i=1;
WHILE i<n DO
SELECT COLUMN_NAME FROM
(SELECT COLUMN_NAME, @row_num:= @row_num + 1 as ind_rows
FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c
WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string
ORDER BY COLUMN_NAME ASC) as TEMP
WHERE ind_rows = i
INTO col_name;
SELECT DATA_TYPE
FROM
(SELECT DATA_TYPE, @row_num:= @row_num + 1 as ind_rows
FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c
WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string
ORDER BY COLUMN_NAME ASC) as TEMP
WHERE ind_rows = i
INTO col_datatype;
IF i = 1 THEN
SET previous_col = index_name_string;
ELSE
SELECT COLUMN_NAME
FROM
(SELECT COLUMN_NAME, @row_num:= @row_num + 1 as ind_rows
FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @row_num:= 0 AS num) AS c
WHERE TABLE_NAME = table_name_string AND COLUMN_NAME <> index_name_string
ORDER BY COLUMN_NAME ASC) as TEMP
WHERE ind_rows = i-1
INTO previous_col;
END IF;
IF col_datatype = 'varchar' THEN
SET col_datatype = 'TEXT';
END IF;
select col_name, previous_col;
IF col_name <> index_name_string OR index_name_string = '' THEN
SET @Q= CONCAT('ALTER TABLE `',database_name,'`.`',table_name_string,'` CHANGE COLUMN `',col_name,'` `',col_name,'` ',col_datatype,' NULL DEFAULT NULL AFTER `',previous_col,'`');
PREPARE exe FROM @Q;
EXECUTE exe;
DEALLOCATE PREPARE exe;
END IF;
SET i = i + 1;
END WHILE;
END;
;;
DELIMITER ;
# NOTE: ASSUMES INDEX IS BIGINT(20), IF OTHER PLEASE ADAPT IN LINE 22 TO MEET DATATYPE
#
# CALL ALPHABETISE_TABLE_COLUMNS('database_name', 'column_name', 'index_name')
是的。可以使用以下命令实现。
SELECT column_name FROM user_tab_cols WHERE table_name=UPPER('Your_Table_Name') order by column_name;
它将按字母顺序显示您表中的所有列。