我们都知道要从表中选择所有列,我们可以使用
SELECT * FROM tableA
有没有办法在不指定所有列的情况下从表中排除列?
SELECT * [except columnA] FROM tableA
我知道的唯一方法是手动指定所有列并排除不需要的列。这真的很耗时,所以我正在寻找节省时间和精力的方法,以及如果表有更多/更少的列,将来的维护。
SELECT DISTINCT *
除了键列可以在没有其他人创建的重复行的情况下工作
你可以这样尝试:
/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable
不。
维护简单的最佳实践是仅指定所需的列。
至少有两个原因:
这使您的客户端和数据库之间的合同稳定。相同的数据,每次
性能,涵盖指标
编辑(2011 年 7 月):
如果您从对象资源管理器中拖动表的 Columns
节点,它会在查询窗口中为您放置一个列的 CSV 列表,从而实现您的目标之一
如果您不想手动编写每个列名,您可以像这样在 SSMS 中右键单击 table 或 view 来使用 Script Table As
:
https://i.stack.imgur.com/hjxWj.png
然后您将在新查询编辑器窗口中获得整个选择查询,然后删除不需要的列,如下所示:
https://i.stack.imgur.com/g1gLL.png
完毕
在 SQL (SQL Server) 中自动执行此操作的方法是:
declare @cols varchar(max), @query varchar(max);
SELECT @cols = STUFF
(
(
SELECT DISTINCT '], [' + name
FROM sys.columns
where object_id = (
select top 1 object_id from sys.objects
where name = 'MyTable'
)
and name not in ('ColumnIDontWant1', 'ColumnIDontWant2')
FOR XML PATH('')
), 1, 2, ''
) + ']';
SELECT @query = 'select ' + @cols + ' from MyTable';
EXEC (@query);
row_number()
的结果附加到每一行,然后通过 row_number
执行连接,然后从结果中选择 除 row_number
之外的所有内容的加入。
您可以创建一个包含您希望选择的列的视图,然后您只需从视图中select *
...
像 BigQuery 这样的现代 SQL 方言提出了一个很好的解决方案。
SELECT * EXCEPT(ColumnNameX, [ColumnNameY, ...])
FROM TableA
这是一种非常强大的 SQL 语法,可以避免由于表列名更改而需要一直更新的一长列列。而目前的SQL Server实现中缺少这个功能,很可惜。希望有一天,Microsoft Azure 对数据科学家更加友好。
数据科学家喜欢快速选择缩短查询并删除一些列(由于重复或任何其他原因)。
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-modifiers
是的,这是可能的(但不推荐)。
CREATE TABLE contact (contactid int, name varchar(100), dob datetime)
INSERT INTO contact SELECT 1, 'Joe', '1974-01-01'
DECLARE @columns varchar(8000)
SELECT @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'contact' AND COLUMN_NAME <> 'dob'
ORDER BY ORDINAL_POSITION
EXEC ('SELECT ' + @columns + ' FROM contact')
代码说明:
声明一个变量来存储一个逗号分隔的列名列表。这默认为 NULL。使用系统视图来确定表中列的名称。使用 SELECT @variable = @variable + ... FROM 连接列名。这种类型的 SELECT 不返回结果集。这可能是未记录的行为,但适用于 SQL Server 的每个版本。作为替代方案,您可以使用 SET @variable = (SELECT ... FOR XML PATH('')) 来连接字符串。仅当这不是第一个列名时,才使用 ISNULL 函数前置逗号。使用 QUOTENAME 函数来支持列名中的空格和标点符号。使用 WHERE 子句隐藏我们不想看到的列。使用 EXEC (@variable),也称为动态 SQL,在运行时解析列名。这是必需的,因为我们在编译时不知道列名。
SELECT @columns FROM contact
就像其他人所说的那样,没有办法做到这一点,但是如果您使用的是 Sql Server,我使用的一个技巧是将输出更改为逗号分隔,然后执行
select top 1 * from table
并从输出窗口中剪切整个列列表。然后,您可以选择您想要的列,而无需全部输入。
基本上,你不能做你想做的事——但你可以得到正确的工具来帮助你让事情变得更容易。
如果您查看 Red-Gate 的 SQL Prompt,您可以键入“SELECT * FROM MyTable”,然后将光标移回“*”之后,然后按 <TAB>展开字段列表,并删除您不需要的那几个字段。
这不是一个完美的解决方案 - 但一个非常好的解决方案! :-) 太糟糕了 MS SQL Server Management Studio 的 Intellisense 仍然不够智能,无法提供此功能.......
马克
不,没有办法做到这一点。如果您的情况可行,也许您可以创建自定义视图
编辑可能是如果您的数据库支持执行动态 sql,您可以编写一个 SP 并将您不想看到的列传递给它,让它动态创建查询并将结果返回给您。我认为这至少在 SQL Server 中是可行的
DECLARE @SQL VARCHAR(max), @TableName sysname = 'YourTableName'
SELECT @SQL = COALESCE(@SQL + ', ', '') + Name
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@TableName)
AND name NOT IN ('Not This', 'Or that');
SELECT @SQL = 'SELECT ' + @SQL + ' FROM ' + @TableName
EXEC (@SQL)
更新:
如果您更频繁地使用它,您还可以创建一个存储过程来处理此任务。在此示例中,我使用了 SQL Server 2016+ 上可用的内置 STRING_SPLIT(),但如果您需要,有大量示例说明如何在 SO 上手动创建它。
CREATE PROCEDURE [usp_select_without]
@schema_name sysname = N'dbo',
@table_name sysname,
@list_of_columns_excluded nvarchar(max),
@separator nchar(1) = N','
AS
BEGIN
DECLARE
@SQL nvarchar(max),
@full_table_name nvarchar(max) = CONCAT(@schema_name, N'.', @table_name);
SELECT @SQL = COALESCE(@SQL + ', ', '') + QUOTENAME([Name])
FROM sys.columns sc
LEFT JOIN STRING_SPLIT(@list_of_columns_excluded, @separator) ss ON sc.[name] = ss.[value]
WHERE sc.OBJECT_ID = OBJECT_ID(@full_table_name, N'u')
AND ss.[value] IS NULL;
SELECT @SQL = N'SELECT ' + @SQL + N' FROM ' + @full_table_name;
EXEC(@SQL)
END
然后只是:
EXEC [usp_select_without]
@table_name = N'Test_Table',
@list_of_columns_excluded = N'ID, Date, Name';
如果您使用的是 SQL Server Management Studio,请执行以下操作:
输入您想要的表格名称并选择它 按 Alt+F1 o/p 显示表格中的列。选择所需的列 在您的选择查询中复制并粘贴这些列 触发查询。
享受。
总之你不能这样做,但我不同意上面的所有评论,有“一些”场景你可以合法地使用 * 当你创建一个嵌套查询以便从整个列表中选择一个特定范围时(例如分页)当您在内部完成时,为什么要在外部选择语句上指定每一列?
在 SQL Management Studio 中,您可以在对象资源管理器中展开列,然后将 Columns
树项拖到查询窗口中以获取以逗号分隔的列列表。
如果您想排除敏感的大小写列,例如密码,我这样做是为了隐藏值:
SELECT * , "" as password FROM tableName;
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
这个
如果我们谈论的是过程,它使用这个技巧来生成一个新的查询并立即执行它:
SELECT LISTAGG((column_name), ', ') WITHIN GROUP (ORDER BY column_id)
INTO var_list_of_columns
FROM ALL_TAB_COLUMNS
WHERE table_name = 'PUT_HERE_YOUR_TABLE'
AND column_name NOT IN ('dont_want_this_column','neither_this_one','etc_column');
有没有办法在不指定所有列的情况下从表中排除列?
以通常的方式使用声明性 SQL,不。
我认为您提出的语法是值得的和好的。事实上,关系数据库语言“Tutorial D”具有非常相似的语法,其中关键字 ALL BUT
后跟一组属性(列)。
但是,SQL 的 SELECT *
已经受到了很多批评(@Guffa 的回答是一个典型的反对意见),所以我认为 SELECT ALL BUT
不会很快进入 SQL 标准。
我认为最好的“解决方法”是创建一个 VIEW
,其中只包含您想要的列,然后是 SELECT * FROM ThatView
。
select *
有问题的人 - 对于需要通用处理数据表的临时内容和程序非常有用,但对于构建(缺乏更好的词)“纯”查询。尽管如此,不符合 ANSI 标准并不意味着微软不能像许多其他事情一样将其添加到他们的方言中,但我怀疑他们是否会这样做。
解决此问题的最佳方法是使用视图,您可以使用所需列创建视图并从中检索数据
example
mysql> SELECT * FROM calls;
+----+------------+---------+
| id | date | user_id |
+----+------------+---------+
| 1 | 2016-06-22 | 1 |
| 2 | 2016-06-22 | NULL |
| 3 | 2016-06-22 | NULL |
| 4 | 2016-06-23 | 2 |
| 5 | 2016-06-23 | 1 |
| 6 | 2016-06-23 | 1 |
| 7 | 2016-06-23 | NULL |
+----+------------+---------+
7 rows in set (0.06 sec)
mysql> CREATE VIEW C_VIEW AS
-> SELECT id,date from calls;
Query OK, 0 rows affected (0.20 sec)
mysql> select * from C_VIEW;
+----+------------+
| id | date |
+----+------------+
| 1 | 2016-06-22 |
| 2 | 2016-06-22 |
| 3 | 2016-06-22 |
| 4 | 2016-06-23 |
| 5 | 2016-06-23 |
| 6 | 2016-06-23 |
| 7 | 2016-06-23 |
+----+------------+
7 rows in set (0.00 sec)
我不知道有任何数据库支持这个(SQL Server、MySQL、Oracle、PostgreSQL)。它绝对不是 SQL 标准的一部分,所以我认为你必须只指定你想要的列。
您当然可以动态构建 SQL 语句并让服务器执行它。但这为 SQL 注入开辟了可能性。
Postgres sql 有办法做到这一点
信息模式黑客方式
SELECT 'SELECT ' || array_to_string(ARRAY(SELECT 'o' || '.' || c.column_name
FROM information_schema.columns As c
WHERE table_name = 'officepark'
AND c.column_name NOT IN('officeparkid', 'contractor')
), ',') || ' FROM officepark As o' As sqlstmt
以上针对我的特定示例表 - 生成一个看起来像这样的 sql 语句
SELECT o.officepark,o.owner,o.squarefootage FROM officepark As o
我知道这有点老了,但我刚刚遇到了同样的问题并正在寻找答案。然后我让一位高级开发人员向我展示了一个非常简单的技巧。
如果您使用的是 Management Studio 查询编辑器,请展开数据库,然后展开您从中选择的表,以便您可以看到列文件夹。
在您的选择语句中,只需突出显示上面引用的列文件夹并将其拖放到查询窗口中。它将粘贴表的所有列,然后只需从列列表中删除标识列...
SELECT * Except(tableName.ColumnName) FROM ...
好吧,一个常见的最佳实践是指定你想要的列,而不是仅仅指定 *.因此,您应该只说明您希望您的选择返回哪些字段。
在对象资源管理器中右键单击表,选择前 1000 行
它将列出所有列而不是 *.然后删除不需要的列。应该比自己打字快得多。
然后当你觉得这工作有点多时,获取 Red Gate 的 SQL Prompt,然后输入 ssf from tbl,转到 * 并再次单击 tab。
一位同事建议了一个不错的选择:
在前面的查询(生成或获取数据的地方)中执行 SELECT INTO 到表中(完成后将删除该表)。这将为您创建结构。
将脚本作为 CREATE 执行到新的查询窗口。
删除不需要的列。将剩余的列格式化为 1 行并粘贴为列列表。
删除您创建的表。
完毕...
这对我们帮助很大。
我经常在这种情况下使用的东西:
declare @colnames varchar(max)=''
select @colnames=@colnames+','+name from syscolumns where object_id(tablename)=id and name not in (column3,column4)
SET @colnames=RIGHT(@colnames,LEN(@colnames)-1)
@colnames
看起来像 column1,column2,column5
我这样做了,它工作得很好(5.5.41版):
# prepare column list using info from a table of choice
SET @dyn_colums = (SELECT REPLACE(
GROUP_CONCAT(`COLUMN_NAME`), ',column_name_to_remove','')
FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE
`TABLE_SCHEMA`='database_name' AND `TABLE_NAME`='table_name');
# set sql command using prepared columns
SET @sql = CONCAT("SELECT ", @dyn_colums, " FROM table_name");
# prepare and execute
PREPARE statement FROM @sql;
EXECUTE statement;
有时同一个程序必须处理不同的数据库结构。所以我不能在程序中使用列列表来避免 select
语句中的错误。
*
为我提供了所有可选字段。我在使用前检查数据表中是否存在字段。这就是我在 select
中使用 *
的原因。
这就是我处理排除字段的方式:
Dim da As New SqlDataAdapter("select * from table", cn)
da.FillSchema(dt, SchemaType.Source)
Dim fieldlist As String = ""
For Each DC As DataColumn In DT.Columns
If DC.ColumnName.ToLower <> excludefield Then
fieldlist = fieldlist & DC.Columnname & ","
End If
Next
在 Hive Sql 中,您可以这样做:
set hive.support.quoted.identifiers=none;
select
`(unwanted_col1|unwanted_col2|unwanted_col3)?+.+`
from database.table
这给了你剩下的cols
day
和 day_hour
两列,(day|day_hour)?+.+
仍将匹配 day_hour
列。那是因为正则表达式引擎渴望 |
。尽管将顺序更改为 (day_hour|day)?+.+
可以解决此问题,但更好的方法是使用负前瞻 (?!(day|day_hour)$).+
。
当使用视图而不是真实表格时,BartoszX 提出的答案(存储过程)对我不起作用。
这个想法和下面的代码(除了我的修复)属于 BartoszX。
为了使其适用于表和视图,请使用以下代码:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[select_without]
@schema_name sysname = N'dbo',
@table_name sysname,
@list_of_columns_excluded nvarchar(max),
@separator nchar(1) = N','
AS
BEGIN
DECLARE
@SQL nvarchar(max),
@full_table_name nvarchar(max) = CONCAT(@schema_name, N'.', @table_name);
SELECT @SQL = COALESCE(@SQL + ', ', '') + QUOTENAME([Name])
FROM sys.columns sc
LEFT JOIN STRING_SPLIT(@list_of_columns_excluded, @separator) ss ON sc.[name] = ss.[value]
WHERE sc.OBJECT_ID = OBJECT_ID(@full_table_name)
AND ss.[value] IS NULL;
SELECT @SQL = N'SELECT ' + @SQL + N' FROM ' + @full_table_name;
EXEC(@SQL)
END
GO
我知道这个问题很老了,但我希望这仍然会有所帮助。答案的灵感来自 SQL Server Forums 的讨论。您可以将其设为 stored procedure。也可以修改为添加多个除字段。
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = COALESCE(@SQL + ', ', ' ' ) + name from sys.columns where name not in ('colName1','colName2') and object_id = (Select id from sysobjects where name = 'tblName')
SELECT @SQL = 'SELECT ' + @SQL + ' FROM ' + 'tblName'
EXEC sp_executesql @SQL
不定期副业成功案例分享
SELECT table1.ID AS table1ID ...
,IIRC。