I have 100 columns in a table and I want to list 99 columns except a parti开发者_如何转开发cular one.
How to exclude that columns name?
The Tutorial D relational database query language does allow a projection to be expressed in terms of the attributes to be removed using ALL BUT
however there is no such equivalent syntax in SQL that allows you to do this. You need to explicitly list the specific ones that you want.
You could use a View if you commonly need this same set of columns.
SELECT column_1, column_2, column_3,
/* ...the list of columns 4-97, not shown in this example... */,
column_98, column_99
FROM table
There is no easier way to do this (possibly by design): you need to specifically list each of the columns you want to retrieve.
Although it's a hassle to write, this is actually a Good Thing: using SELECT *
in production code is discouraged (both for performance and maintainability reasons) - see e.g. this question.
You cannot.
Theoretically, you might involve dynamic sql here - some procedure will loop through all columns of your table (like, all_tabs_columns
dictionary view in Oracle lists all columns of all tables), and form up you a query of select c1, c2 c3 ... c99 from tab
, but I strongly discourage you of doing this. Will save you several lines of code, but make it hard to maintain. :)
Try this.
DROP TABLE #MY_TEMP_TABLE
CREATE TABLE #MY_TEMP_TABLE
(
Column_1 int NULL,
Column_2 varchar(10) NULL,
Column_3 datetime NULL
)
INSERT INTO #MY_TEMP_TABLE(Column_1, Column_2, Column_3)
SELECT 1, 'a', GETDATE()
UNION SELECT 2, 'b', GETDATE()
UNION SELECT 3, 'c', GETDATE()
UNION SELECT 4, 'd', GETDATE()
DECLARE @dSQL nvarchar(1000)
SELECT @dSQL = 'SELECT TOP 10 '
SELECT @dSQL = @dSQL + LEFT(nst.ColumnList, LEN(nst.ColumnList)-1) --AS 'List'
FROM (SELECT so.id
FROM TEMPDB..SYSOBJECTS so
WHERE so.name LIKE '#MY_TEMP_TABLE%'
AND so.type = 'u') so
CROSS APPLY (SELECT sc.Name + ', '
FROM TEMPDB..SYSCOLUMNS sc
WHERE sc.id = so.id
AND sc.colid <> 3
FOR XML PATH('')) nst
(ColumnList)
SELECT @dSQL = @dSQL + ' FROM #MY_TEMP_TABLE'
EXEC sp_executesql @dSQL
Much faster query solution! In bold text, add your table name and column name to be remove. You can add as many fields as you want to remove.
DECLARE @query nvarchar(max)=(SELECT string_agg(COLUMN_NAME,',') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='YOURTABLENAME'
AND COLUMN_NAME NOT IN ('REMOVEDCOLOUMN',.........))
SET @query ='select '+@query+' FROM YOURTABLENAME' EXEC sp_executesql @query;
It's little touches like this that make BiqQuery much more pleasant to use than SSMS.
You can simply use
select * except (columnToRemove,....) from table
精彩评论