SQL 如何选择表格中除一列之外的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29095281/
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 the columns of a table except one column?
提问by Giri Prasad
How to select all the columns of a table except one column?
如何选择表格中除一列之外的所有列？
I have nearly 259 columns I cant mention 258 columns in SELECTstatement.
我有将近 259 列我不能在SELECT声明中提到 258 列。
Is there any other way to do it?
有没有其他方法可以做到？
采纳答案by Rahul Tripathi
You can use this approach to get the data from all the columns except one:-
您可以使用这种方法从除一列之外的所有列中获取数据：-
- Insert all the data into a temporary table
- Then drop the column which you dont want from the temporary table
- Fetch the data from the temporary table(This will not contain the data of the removed column)
- Drop the temporary table
- 将所有数据插入临时表
- 然后从临时表中删除您不想要的列
- 从临时表中获取数据（这将不包含删除列的数据）
- 删除临时表
Something like this:
像这样的东西：
SELECT * INTO #TemporaryTable FROM YourTableName
ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove
SELECT * FROM #TemporaryTable
DROP TABLE #TemporaryTable
回答by TommCatt
Create a view. Yes, in the view creation statement, you will have to list each...and...every...field...by...name.
创建视图。是的，在视图创建语句中，您必须列出每个...和...每个...字段...按...名称。
Once.
一次。
Then just select * from viewnameafter that.
然后就select * from viewname在那之后。
回答by ps_prakash02
You can get the column name details from sys.columns table
您可以从 sys.columns 表中获取列名详细信息
Try the following query:
尝试以下查询：
SELECT * FROM SYS.COLUMNS
WHERE object_id = OBJECT_ID('dbo.TableName')
AND [Name] <> 'ColumnName'
DECLARE @sql as VARCHAR(8000)
SET @sql = 'SELECT '
SELECT @sql += [Name] + ', ' FROM SYS.COLUMNS
WHERE object_id = OBJECT_ID('dbo.TableName')
AND [Name] <> 'ColumnName'
SELECT @sql += ' FROM Dbo.TableName'
EXEC(@sql)
回答by Sagar Joon
There are lot of options available , one of them is :
有很多可用的选项，其中之一是：
CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
ALTER TABLE temp_tb DROP col_x;
SELECT * FROM temp_tb;
Here the col_x is the column which u dont want to include in select statement.
这里 col_x 是您不想包含在 select 语句中的列。
Take a look at this question : Select all columns except one in MySQL?
看看这个问题：在 MySQL 中选择除一列之外的所有列？
回答by Fatore
This is not a generic solution, but some databases allow you to use regular expressions to specify the columns.
这不是通用的解决方案，但某些数据库允许您使用正则表达式来指定列。
For instance, in the case of Hive, the following query selects all columns except ds and hr:
例如，在 Hive 的情况下，以下查询选择除 ds 和 hr 之外的所有列：
SELECT `(ds|hr)?+.+` FROM sales
回答by Ashish Gupta
I just wanted to echo @Luann's comment as I use this approach always.
我只是想回应@Luann 的评论，因为我总是使用这种方法。
Just right click on the table > Script table as > Select to > New Query window.
只需右键单击表 > 脚本表为 > 选择到 > 新建查询窗口。
You will see the select query. Just take out the column you want to exclude and you have your preferred select query.
您将看到选择查询。只需取出要排除的列，即可获得首选的选择查询。
回答by Sergio Pisoni
Without creating new table you can do simply (e.g with mysqli):
无需创建新表，您可以简单地执行（例如使用 mysqli）：
- get all columns
- loop through all columns and remove wich you want
- make your query
- 获取所有列
- 遍历所有列并删除您想要的
- 提出您的查询
$r = mysqli_query('SELECT column_name FROM information_schema.columns WHERE table_name = table_to_query');
$c = count($r); while($c--) if($r[$c]['column_name'] != 'column_to_remove_from_query') $a[] = $r[$c]['column_name']; else unset($r[$c]);
$r = mysqli_query('SELECT ' . implode(',', $a) . ' FROM table_to_query');
回答by Vlad Bezden
If you are using DataGripyou can do the following:
如果您使用的是DataGrip，您可以执行以下操作：
- Enter your SELECT statement
SELECT * FROM <your_table>; - Put your cursor over
*and pressAlt+Enter - You will get pop up menu with
Expand column listoption - Click on it and it will convert
*with full list of columns - Now you can remove columns that you don't need
- 输入您的 SELECT 语句
SELECT * FROM <your_table>; - 将光标放在上面
*并按下Alt+Enter - 您将获得带有
Expand column list选项的弹出菜单 - 单击它，它将
*使用完整的列列表进行转换 - 现在您可以删除不需要的列
回答by Omid Nasri
Try the following query:
尝试以下查询：
DECLARE @Temp NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Temp = '';
SELECT @Temp = @Temp + COLUMN_NAME + ', ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='Person' AND COLUMN_NAME NOT IN ('Id')
SET @SQL = 'SELECT ' + SUBSTRING(@Temp, 0, LEN(@Temp)) +' FROM [Person]';
EXECUTE SP_EXECUTESQL @SQL;
回答by yasir kk
Only one way to achieve this giving column name. There is no other method found. You must have to list all column name
只有一种方法可以实现此给定列名。没有找到其他方法。您必须列出所有列名
