SQL 选择 * 除外
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/413819/
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
SELECT * EXCEPT
提问by Glen Solsberry
Is there any RDBMS that implements something like SELECT * EXCEPT? What I'm after is getting all of the fields except a specific TEXT/BLOB field, and I'd like to just select everything else.
是否有任何 RDBMS 实现了类似的功能SELECT * EXCEPT？我所追求的是获取除特定 TEXT/BLOB 字段之外的所有字段，我只想选择其他所有字段。
Almost daily I complain to my coworkers that someone should implement this... It's terribly annoying that it doesn't exist.
我几乎每天都向我的同事抱怨应该有人实施这个......它不存在真是太烦人了。
Edit:I understand everyone's concern for SELECT *. I know the risks associated with SELECT *. However, this, at least in my situation, would not be used for any Production level code, or even Development level code; strictly for debugging, when I need to see all of the values easily.
编辑：我理解每个人对SELECT *. 我知道与SELECT *. 但是，至少在我的情况下，这不会用于任何生产级别的代码，甚至开发级别的代码；严格用于调试，当我需要轻松查看所有值时。
As I've stated in some of the comments, where I work is strictly a commandline shop, doing everything over ssh. This makes it difficult to use any gui tools (external connections to the database aren't allowed), etc etc.
正如我在一些评论中所说的那样，我工作的地方严格来说是一个命令行商店，一切都通过 ssh 完成。这使得很难使用任何 gui 工具（不允许与数据库的外部连接）等。
Thanks for the suggestions though.
谢谢你的建议。
采纳答案by Jasmine
As others have said, it is not a good idea to do this in a query because it is prone to issues when someone changes the table structure in the future. However, there is a way to do this... and I can't believe I'm actually suggesting this, but in the spirit of answering the ACTUAL question...
正如其他人所说，在查询中执行此操作不是一个好主意，因为将来有人更改表结构时很容易出现问题。但是，有一种方法可以做到这一点......我不敢相信我实际上是在建议这个，但本着回答实际问题的精神......
Do it with dynamic SQL... this does all the columns except the "description" column. You could easily turn this into a function or stored proc.
使用动态 SQL 执行此操作...这会执行除“描述”列之外的所有列。您可以轻松地将其转换为函数或存储过程。
declare @sql varchar(8000),
@table_id int,
@col_id int
set @sql = 'select '
select @table_id = id from sysobjects where name = 'MY_Table'
select @col_id = min(colid) from syscolumns where id = @table_id and name <> 'description'
while (@col_id is not null) begin
select @sql = @sql + name from syscolumns where id = @table_id and colid = @col_id
select @col_id = min(colid) from syscolumns where id = @table_id and colid > @col_id and name <> 'description'
if (@col_id is not null) set @sql = @sql + ','
print @sql
end
set @sql = @sql + ' from MY_table'
exec @sql
回答by Paul Dixon
Create a view on the table which doesn't include the blob columns
在不包含 blob 列的表上创建视图
回答by Lukasz Szozda
Is there any RDBMS that implements something like SELECT * EXCEPT?
是否有任何 RDBMS 实现了类似 SELECT * EXCEPT 的功能？
Yes, Google Big Query implements SELECT * EXCEPT:
是的，Google Big Query 实现了SELECT * EXCEPT：
A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output.
SELECT * EXCEPT 语句指定要从结果中排除的一个或多个列的名称。输出中省略了所有匹配的列名称。
WITH orders AS(
SELECT 5 as order_id,
"sprocket" as item_name,
200 as quantity
)
SELECT * EXCEPT (order_id)
FROM orders;
Output:
输出：
+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket | 200 |
+-----------+----------+
EDIT:
编辑：
H2 database also supports SELECT * EXCEPT (col1, col2, ...)syntax.
H2 数据库也支持SELECT * EXCEPT (col1, col2, ...)语法。
A wildcard expression in a SELECT statement. A wildcard expression represents all visible columns. Some columns can be excluded with optional EXCEPT clause.
SELECT 语句中的通配符表达式。通配符表达式代表所有可见的列。可以使用可选的 EXCEPT 子句排除某些列。
EDIT 2:
编辑2：
Hive supports: REGEX Column Specification
Hive 支持：REGEX 列规范
A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later releases if the configuration property hive.support.quoted.identifiers is set to none.
The following query selects all columns except ds and hr.
如果配置属性 hive.support.quoted.identifiers 设置为 none，则 SELECT 语句可以在 0.13.0 之前的 Hive 版本或 0.13.0 及更高版本中采用基于正则表达式的列规范。
以下查询选择除 ds 和 hr 之外的所有列。
SELECT `(ds|hr)?+.+` FROM sales
回答by billinkc
DB2 allows for this. Columns have an attribute/specifier of Hidden.
DB2 允许这样做。列的属性/说明符为Hidden。
From the syscolumns documentation
HIDDEN
CHAR(1) NOT NULL WITH DEFAULT 'N'
Indicates whether the column is implicitly hidden:P Partially hidden. The column is implicitly hidden from SELECT *.
N Not hidden. The column is visible to all SQL statements.
HIDDEN
CHAR(1) NOT NULL WITH DEFAULT 'N'
指示列是否隐式隐藏：P 部分隐藏。该列对 SELECT * 隐式隐藏。
N 不隐藏。该列对所有 SQL 语句都可见。
Create table documentationAs part of creating your column, you would specify the IMPLICITLY HIDDENmodifier
创建表文档作为创建列的一部分，您将指定IMPLICITLY HIDDEN修饰符
An example DDL from Implicitly Hidden Columnsfollows
来自示例DDL隐式隐藏的列如下
CREATE TABLE T1
(C1 SMALLINT NOT NULL,
C2 CHAR(10) IMPLICITLY HIDDEN,
C3 TIMESTAMP)
IN DB.TS;
Whether this capability is such a deal maker to drive the adoption of DB2 is left as an exercise to future readers.
这种能力是否能够推动 DB2 的采用，留给未来的读者作为练习。
回答by onedaywhen
Is there any RDBMS that implements something like SELECT * EXCEPT
是否有任何 RDBMS 实现了诸如 SELECT * EXCEPT 之类的东西
Yes! The truly relational language Tutorial Dallows projection to be expressed in terms of the attributes to be removed instead of the ones to be kept e.g.
是的！真正的关系语言教程 D允许根据要删除的属性而不是要保留的属性来表达投影，例如
my_relvar { ALL BUT description }
In fact, its equivalent to SQL's SELECT *is { ALL BUT }.
事实上，它与 SQL 的等价物SELECT *是{ ALL BUT }.
Your proposal for SQL is a worthy one but I heard it has already been put to the SQL standard's committee by the users' group and rejected by the vendor's group :(
你对 SQL 的提议是值得的，但我听说它已经被用户组提交给 SQL 标准委员会，但被供应商组拒绝了:(
It has also been explicitly requested for SQL Serverbut the request was closed as 'won't fix'.
它也已被明确请求用于 SQL Server，但该请求因“无法修复”而被关闭。
回答by Lukasz Szozda
Yes, finally there is :) SQL Standard 2016 defines Polymorphic Table Functions
是的，终于有了 :) SQL Standard 2016 定义了多态表函数
SQL:2016 introduces polymorphic table functions (PTF) that don't need to specify the result type upfront. Instead, they can provide a describe component procedure that determines the return type at run time. Neither the author of the PTF nor the user of the PTF need to declare the returned columns in advance.
PTFs as described by SQL:2016 are not yet available in any tested database.10 Interested readers may refer to the free technical report “Polymorphic table functions in SQL” released by ISO. The following are some of the examples discussed in the report:
CSVreader, which reads the header line of a CVS file to determine the number and names of the return columns
Pivot (actually unpivot), which turns column groups into rows (example: phonetype, phonenumber) -- me: no more harcoded strings :)
TopNplus, which passes through N rows per partition and one extra row with the totals of the remaining rows
SQL:2016 引入了不需要预先指定结果类型的多态表函数 (PTF)。相反，它们可以提供在运行时确定返回类型的描述组件过程。PTF 的作者和 PTF 的用户都不需要提前声明返回的列。
SQL:2016 中描述的 PTF 在任何经过测试的数据库中尚不可用。 10 感兴趣的读者可以参考 ISO 发布的免费技术报告“SQL 中的多态表函数”。以下是报告中讨论的一些示例：
CSVreader，它读取 CVS 文件的标题行以确定返回列的数量和名称
Pivot（实际上是 unpivot），它将列组转换为行（例如：phonetype、phonenumber）——我：不再有硬编码的字符串 :)
TopNplus，它通过每个分区的 N 行和一个额外的行以及剩余行的总数
Oracle 18cimplements this mechanism. 18c Skip_col Polymorphic Table Function Example Oracle Live SQLand Skip_col Polymorphic Table Function Example
Oracle 18c实现这一机制。18c Skip_col 多态表函数示例 Oracle Live SQL和Skip_col 多态表函数示例
This example shows how to skip data based on name/specific datatype:
此示例显示如何根据名称/特定数据类型跳过数据：
CREATE PACKAGE skip_col_pkg AS
-- OVERLOAD 1: Skip by name
FUNCTION skip_col(tab TABLE, col columns)
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
FUNCTION describe(tab IN OUT dbms_tf.table_t,
col dbms_tf.columns_t)
RETURN dbms_tf.describe_t;
-- OVERLOAD 2: Skip by type --
FUNCTION skip_col(tab TABLE,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN TABLE PIPELINED ROW POLYMORPHIC USING skip_col_pkg;
FUNCTION describe(tab IN OUT dbms_tf.table_t,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN dbms_tf.describe_t;
END skip_col_pkg;
and body:
与身体：
CREATE PACKAGE BODY skip_col_pkg AS
/* OVERLOAD 1: Skip by name
* NAME: skip_col_pkg.skip_col
* ALIAS: skip_col_by_name
*
* PARAMETERS:
* tab - The input table
* col - The name of the columns to drop from the output
*
* DESCRIPTION:
* This PTF removes all the input columns listed in col from the output
* of the PTF.
*/
FUNCTION describe(tab IN OUT dbms_tf.table_t,
col dbms_tf.columns_t)
RETURN dbms_tf.describe_t
AS
new_cols dbms_tf.columns_new_t;
col_id PLS_INTEGER := 1;
BEGIN
FOR i IN 1 .. tab.column.count() LOOP
FOR j IN 1 .. col.count() LOOP
tab.column(i).pass_through := tab.column(i).description.name != col(j);
EXIT WHEN NOT tab.column(i).pass_through;
END LOOP;
END LOOP;
RETURN NULL;
END;
/* OVERLOAD 2: Skip by type
* NAME: skip_col_pkg.skip_col
* ALIAS: skip_col_by_type
*
* PARAMETERS:
* tab - Input table
* type_name - A string representing the type of columns to skip
* flip - 'False' [default] => Match columns with given type_name
* otherwise => Ignore columns with given type_name
*
* DESCRIPTION:
* This PTF removes the given type of columns from the given table.
*/
FUNCTION describe(tab IN OUT dbms_tf.table_t,
type_name VARCHAR2,
flip VARCHAR2 DEFAULT 'False')
RETURN dbms_tf.describe_t
AS
typ CONSTANT VARCHAR2(1024) := upper(trim(type_name));
BEGIN
FOR i IN 1 .. tab.column.count() LOOP
tab.column(i).pass_through :=
CASE upper(substr(flip,1,1))
WHEN 'F' THEN dbms_tf.column_type_name(tab.column(i).description)
!=typ
ELSE dbms_tf.column_type_name(tab.column(i).description)
=typ
END /* case */;
END LOOP;
RETURN NULL;
END;
END skip_col_pkg;
And sample usage:
和示例用法：
-- skip number cols
SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number');
-- only number cols
SELECT * FROM skip_col_pkg.skip_col(scott.dept, 'number', flip => 'True')
-- skip defined columns
SELECT *
FROM skip_col_pkg.skip_col(scott.emp, columns(comm, hiredate, mgr))
WHERE deptno = 20;
I highly recommend to read entire example(creating standalone functions instead of package calls).
我强烈建议阅读整个示例（创建独立函数而不是包调用）。
You could easily overload skip method for example: skip columns that does not start/end with specific prefix/suffix.
您可以轻松重载跳过方法，例如：跳过不以特定前缀/后缀开头/结尾的列。
Related: How to Dynamically Change the Columns in a SQL Query By Chris Saxon
回答by Otávio Décio
Stay away from SELECT *, you are setting yourself for trouble. Alwaysspecify exactly which columns you want. It is in fact quite refreshing that the "feature" you are asking for doesn't exist.
远离 SELECT *，你是在给自己找麻烦。始终准确指定您想要的列。事实上，您要求的“功能”不存在，这令人耳目一新。
回答by Rowland Shaw
I believe the rationale for it not existing is that the author of a query should (for performance sake) only request what they're going to look at/need (and therefore know what columns to specify) -- if someone adds a couple more blobs in the future, you'd be pulling back potentially large fields you're not going to need.
我相信它不存在的理由是查询的作者应该（为了性能起见）只请求他们将要查看/需要的内容（因此知道要指定哪些列）——如果有人添加更多未来的 blob，您可能会撤回您不需要的潜在大字段。
回答by Michael Haren
As others are saying: SELECT * is a bad idea.
正如其他人所说： SELECT * 是一个坏主意。
Some reasons:
一些原因：
- Get only what you need (anything more is a waste)
- Indexing (index what you need and you can get it more quickly. If you ask for a bunch of non-indexed columns, too, your query plans will suffer.
- 只得到你需要的（更多的都是浪费）
- 索引（索引你需要的东西，你可以更快地得到它。如果你也请求一堆非索引列，你的查询计划将会受到影响。
回答by kzh
declare @sql nvarchar(max)
@table char(10)
set @sql = 'select '
set @table = 'table_name'
SELECT @sql = @sql + '[' + COLUMN_NAME + '],'
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME = @table
and COLUMN_NAME <> 'omitted_column_name'
SET @sql = substring(@sql,1,len(@sql)-1) + ' from ' + @table
EXEC (@sql);
