如果表有 100 多列，如何从表中选择某些特定列 [英] How to select some particular columns from a table if the table has more than 100 columns

问题描述

我需要从表格的 107 列中选择 90 列.

I need to select 90 columns out of 107 columns from my table.

是否可以编写 select * except( column1,column2,..) from table 或任何其他方式来仅获取特定列，或者我需要在 select 语句中写入所有 90 列?

Is it possible to write select * except( column1,column2,..) from table or any other way to get specific columns only, or I need to write all the 90 columns in select statement?

推荐答案

您可以生成列列表:

select  name + ', '
from    sys.columns
where   object_id = object_id('YourTable')
        and name not in ('column1', 'column2')

可以使用动态 SQL 即时执行此操作:

It's possible to do this on the fly with dynamic SQL:

declare @columns varchar(max)

select  @columns = case when @columns is null then '' else @columns + ', ' end +
            quotename(name)
from    sys.columns
where   object_id = object_id('YourTable')
        and name not in ('column1', 'column2')

declare @query varchar(max)
set @query = 'select ' + @columns + ' from YourTable'
exec (@query)

这篇关于如果表有 100 多列，如何从表中选择某些特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！