MySQL 如何选择sql中除一列之外的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14392126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select all columns in sql except one column?
提问by
Possible Duplicate:
SQL exclude a column using SELECT * [except columnA] FROM tableA?
Is selecting all columns except one column possible??
是否可以选择除一列之外的所有列？
here is all column names: id, name, address, age
这是所有列名：id、name、address、age
SELECT id, name, address from TBLUser
I don't want to use this select statement because the number of columns of my tables are different to each other.
我不想使用这个 select 语句，因为我的表的列数彼此不同。
采纳答案by Raj
declare @cols varchar(max), @sql varchar(max)
SELECT @cols = STUFF
(
(
SELECT DISTINCT '], [' + name
FROM sys.columns
where object_id = (
select top 1 object_id from sys.objects
where name = 'TBLUser'
)
and name not in ('age')
FOR XML PATH('')
), 1, 2, ''
) + ']'
select @sql = 'select ' + @cols + ' from TBLUser'
exec (@sql)
回答by ajmalmhd04
in sql*plus,
在 sql*plus 中，
the one way is to disable as follows:
一种方法是禁用如下：
sql> column age noprint
sql> SELECT * from TBLUser
then, you can revert using
然后，您可以使用
sql>column age off
or else you've to user dynamically with DBMS_SQL package.
否则你必须动态地使用 DBMS_SQL 包。
回答by Michael Eakins
How about:
怎么样：
SELECT * FROM sys.columns
WHERE Name <> N'Column To Exclude' and Object_ID = Object_ID(N'TBLUser')
This will return all columns except the one you wish to exclude.
这将返回除您要排除的列之外的所有列。
Expanded explanation:
扩展说明：
According to this explanation from SQL Cheat Sheet
根据 SQL Cheat Sheet 的解释
sys.columnsis a system table and is used for maintaining information on columns in a database. For every column added in database, a record is created in the sys.columns table. There is only one record for each column
sys.columns是一个系统表，用于维护数据库中列的信息。对于添加到数据库中的每一列，都会在 sys.columns 表中创建一条记录。每列只有一条记录
Name: The name of the column. This is unique within the table object.
名称：列的名称。这在表对象中是唯一的。
Object_id:object_id is unique identifier for table in which the column exists. We will use this column to join sys.columns with sys.tables in order to fetch columns in different tables.
Object_id:object_id 是该列所在表的唯一标识符。我们将使用此列将 sys.columns 与 sys.tables 连接起来，以获取不同表中的列。
We are selecting all results from sys.columns where the name is not equal to whatever you provide, replace 'Column To Exclude'with your column name. We are also requiring that the object_id equal the object_id you provide. object_id is a number that represents the table you want to filter out the one column from. In this ops case the table was TBLUser, in other uses it would be like this object_id(N'[dbo].[YourTable]'), you would replace [YourTable]with your own table name
我们正在从 sys.columns 中选择名称不等于您提供的任何结果的所有结果，替换'Column To Exclude'为您的列名。我们还要求 object_id 等于您提供的 object_id。object_id 是一个数字，表示您要从中过滤掉一列的表。在这个操作案例中，表是 TBLUser，在其他用途中它会是这样的object_id(N'[dbo].[YourTable]')，你可以[YourTable]用你自己的表名替换
