postgresql 如何从结果中排除一列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33073114/
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 exclude one column from result?
提问by SMW
I have a table with columns
我有一个带列的表
a,b,c,d,e,f,g,...,z
Is it possible to write a simple query that will gives all columns except awithout manualy specifing all other columns?
是否可以编写一个简单的查询来提供所有列，除非a手动指定所有其他列？
something that will be equivelant to:
相当于：
Select b,c,d,e,f,g,h,...z
from TableG
回答by japzdivino
To answer your question, you cannot do that directly, HOWEVERi found a solution for you.
要回答您的问题，您不能直接这样做，但是我为您找到了解决方案。
The SELECTstatement of SQL when using Physical tablecan only do SELECT *that will return all columns and SELECT Column1, Column2, Column3...for specific columns, there is no WHEREcondition that will exclude 1 column name in the SELECTstatement of SQL. How ever you can manipulate the table and the data the way you wanted it using temporary table
该SELECTSQL语句的时候使用物理表只能这样做SELECT *，将返回所有列，并SELECT Column1, Column2, Column3...针对特定的列，也没有WHERE条件将在排除1列名SELECTSQL声明。您如何使用临时表以您想要的方式操作表和数据
Solution:
解决方案：
- Insert into temp table
- Drop the column that you want to exclude from the temp table
Select all data from temp table.
SELECT * INTO #TempTable FROM TableG ALTER TABLE #TempTable DROP COLUMN a SELECT * FROM #TempTable DROP TABLE #TempTable
- 插入临时表
- 删除要从临时表中排除的列
从临时表中选择所有数据。
SELECT * INTO #TempTable FROM TableG ALTER TABLE #TempTable DROP COLUMN a SELECT * FROM #TempTable DROP TABLE #TempTable
I found the solution here: SQL exclude a column using SELECT * [except columnA] FROM tableA?
我在这里找到了解决方案：SQL exclude a column using SELECT * [except columnA] FROM tableA?
回答by Sham Sunder
/************************************************************
Function To Split Strings
************************************************************/
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.SplitString') IS NOT NULL
BEGIN
DROP FUNCTION dbo.SplitString
END
GO
create function [dbo].[SplitString]
(
@String varchar(max),
@Delimiter char(1)
)
returns @SplittedValues table
(
str_item varchar(100) primary key
)
as
begin
declare @SplitLength int
while len(@String) > 0
begin
select @SplitLength = (case charindex(@Delimiter,@String) when 0 then
len(@String) else charindex(@Delimiter,@String) -1 end)
insert into @SplittedValues
select substring(@String,1,@SplitLength)
select @String = (case (len(@String) - @SplitLength) when 0 then ''
else right(@String, len(@String) - @SplitLength - 1) end)
end
return
end
GO
/************************************************************
Function To Get columns names excluding some of them
************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.get_columns') IS NOT NULL
BEGIN
DROP FUNCTION dbo.get_columns
END
GO
CREATE FUNCTION dbo.get_columns
(
@table_name varchar(100),
@excluded_column_names varchar(100),
@delimter char(1)
)
RETURNS varchar(4000)
AS
BEGIN
declare @cols varchar(4000)
select @cols = COALESCE(@cols+',' ,'') + name
from sys.columns
where object_id=object_id(@table_name)
and name not in (select str_item from dbo.SplitString(@excluded_column_names,@delimter))
return @cols
END
GO
/************************************************************
Function To Get columns names excluding some of them
************************************************************/
declare @qry nvarchar(max)
set @qry = ' select ' + dbo.get_columns('TableName','Exclude_col_1,Exclude_col_2,Exclude_col_3',',')
+ ' from TableName'
+ ' where condition'
EXECUTE sp_executesql @qry
GO
