I have a table with many columns among which I'd like to omit a few alone in my 'select' query. Something like select (* - columns_to_be_omitted) from myTable. Is there a way to do this, other than listing all the other columns in the query?
我有一个包含许多列的表，我想在“select”查询中单独省略其中的一些列。从myTable中选择(* - columns_to_be_forget)。除了列出查询中的所有其他列之外，是否还有其他方法可以做到这一点?
This is a one-time manual query, so I'm not that concerned about performance. Ease of use is the greater need here.
这是一个一次性的手动查询，所以我并不关心性能。在这里，易用性是更大的需要。
5 个解决方案
#1
2
You can't do that - the best you can do is use a tool like SQL Prompt if you're using SQL Server Management Studio.
您不能这样做——如果您使用的是SQL Server Management Studio，那么最好的方法就是使用SQL Prompt之类的工具。
SQL Prompt will allow you to do a SELECT * FROM MyTable and if you put your cursor after the "*" and press tab, SQL Prompt will enumerate all columns for your table, giving you a list of all columns in the table from which you can then remove those you're not interested in. Or you can open up a popup dialog box which allows you to pick any number of columns from that table which are then inserted into the SELECT statement.
SQL提示会让你做一个SELECT * FROM MyTable如果你把你的光标在“*”和按tab键,SQL提示将显示的所有列的表,给你一个表中的所有列的列表,您可以删除那些你不感兴趣。或者您可以打开一个弹出对话框，该对话框允许您从该表中选择任意数量的列，然后将这些列插入到SELECT语句中。
Too bad that's not part of SSMS out of the box :-(
太糟糕了，这还不是SSMS的一部分。
Marc
马克
#2
4
You can't do this in straight SQL. The best you can do is write a stored procedure (anonymous or otherwise) that constructs a dynamic SQL statement and execute that.
你不能直接用SQL来做。您可以做的最好的事情是编写一个存储过程(匿名或其他)，该过程构建一个动态SQL语句并执行该语句。
You can include all or include some but you can't include all and exclude some (barring weird vendor extensions that may exist with different databases). There's no such thing in MySQL.
您可以包含全部或包含部分，但不能包含全部或排除部分(除非存在不同数据库的奇怪供应商扩展)。MySQL中没有这种东西。
#3
1
I to this day cannot understand how M$ could ship an IDE and a management studio that doesn't have intellisense... I have now been corrected that 2008 has this, 2005 does not.
我到现在还不能理解，M$怎么可以装运一个IDE和一个没有智能感知的管理工作室……我现在被纠正了，2008年有这个，2005年没有。
If you have SQL Management Studio, then you can use the script select to new query window which will give you a template select statement which you can delete from. Just right click on the table.
如果您有SQL Management Studio，那么您可以使用脚本select to new query window，它将为您提供一个模板select语句，您可以从中删除它。只要右键点击表格。
Good luck :)
祝你好运:)
#4
0
There is no direct way to do. You can mention the invididual columns which you want in select query otherwise you can create a temprory table by giving selective column from your table and use that temporary table.
没有直接的办法。可以在select查询中提到invididual列，否则可以通过从表中提供选择列并使用临时表来创建一个临时表。
#5
0
You can dynamically constuct a list of columns using a query against the data dictionary. Then you could add your list of unwanted columns in a where-clause (like column-name not in ()). (Not sure how you could do that with the stored proc approach that cletus suggested.)
可以使用对数据字典的查询动态地构造列列表。然后，您可以在where子句中添加不想要的列列表(比如，在()中没有列名)。(不知道如何使用cletus建议的存储proc方法来实现这一点。)
#1
2
You can't do that - the best you can do is use a tool like SQL Prompt if you're using SQL Server Management Studio.
您不能这样做——如果您使用的是SQL Server Management Studio，那么最好的方法就是使用SQL Prompt之类的工具。
SQL Prompt will allow you to do a SELECT * FROM MyTable and if you put your cursor after the "*" and press tab, SQL Prompt will enumerate all columns for your table, giving you a list of all columns in the table from which you can then remove those you're not interested in. Or you can open up a popup dialog box which allows you to pick any number of columns from that table which are then inserted into the SELECT statement.
SQL提示会让你做一个SELECT * FROM MyTable如果你把你的光标在“*”和按tab键,SQL提示将显示的所有列的表,给你一个表中的所有列的列表,您可以删除那些你不感兴趣。或者您可以打开一个弹出对话框，该对话框允许您从该表中选择任意数量的列，然后将这些列插入到SELECT语句中。
Too bad that's not part of SSMS out of the box :-(
太糟糕了，这还不是SSMS的一部分。
Marc
马克
#2
4
You can't do this in straight SQL. The best you can do is write a stored procedure (anonymous or otherwise) that constructs a dynamic SQL statement and execute that.
你不能直接用SQL来做。您可以做的最好的事情是编写一个存储过程(匿名或其他)，该过程构建一个动态SQL语句并执行该语句。
You can include all or include some but you can't include all and exclude some (barring weird vendor extensions that may exist with different databases). There's no such thing in MySQL.
您可以包含全部或包含部分，但不能包含全部或排除部分(除非存在不同数据库的奇怪供应商扩展)。MySQL中没有这种东西。
#3
1
I to this day cannot understand how M$ could ship an IDE and a management studio that doesn't have intellisense... I have now been corrected that 2008 has this, 2005 does not.
我到现在还不能理解，M$怎么可以装运一个IDE和一个没有智能感知的管理工作室……我现在被纠正了，2008年有这个，2005年没有。
If you have SQL Management Studio, then you can use the script select to new query window which will give you a template select statement which you can delete from. Just right click on the table.
如果您有SQL Management Studio，那么您可以使用脚本select to new query window，它将为您提供一个模板select语句，您可以从中删除它。只要右键点击表格。
Good luck :)
祝你好运:)
#4
0
There is no direct way to do. You can mention the invididual columns which you want in select query otherwise you can create a temprory table by giving selective column from your table and use that temporary table.
没有直接的办法。可以在select查询中提到invididual列，否则可以通过从表中提供选择列并使用临时表来创建一个临时表。
#5
0
You can dynamically constuct a list of columns using a query against the data dictionary. Then you could add your list of unwanted columns in a where-clause (like column-name not in ()). (Not sure how you could do that with the stored proc approach that cletus suggested.)
可以使用对数据字典的查询动态地构造列列表。然后，您可以在where子句中添加不想要的列列表(比如，在()中没有列名)。(不知道如何使用cletus建议的存储proc方法来实现这一点。)