SQL исключает столбец, используя SELECT * [кроме columnA] FROM tableA?

Question

SQL исключает столбец, используя SELECT * [кроме columnA] FROM tableA?

Мы все знаем, что для выбора всех столбцов из таблицы мы можем использовать

SELECT * FROM tableA

Есть ли способ исключить столбцы из таблицы без указания всех столбцов?

SELECT * [except columnA] FROM tableA

Единственный известный мне способ - вручную указать все столбцы и исключить ненужные столбцы. Это действительно отнимает много времени, поэтому я ищу способы сэкономить время и усилия на этом, а также будущее обслуживание, если в таблице будет больше / меньше столбцов.

Спасибо!

1145

sql sql-server t-sql

Источник

08 апр '09 в 09:15

47 ответов

Другие вопросы по тегам sql sql-server t-sql

user194297 11 ноя '09 в 00:35 2009-11-11 00:35 · Answer 1 · 2009-11-11 00:35

Я согласен со всеми... но если бы я собирался сделать что-то подобное, я мог бы сделать это так:

/* 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

605

Источник

user194297 11 ноя '09 в 00:35

user27535 08 апр '09 в 09:19 2009-04-08 09:19 · Answer 2 · 2009-04-08 09:19

Нет.

Лучшая практика обслуживания - указывать только необходимые столбцы.

Как минимум 2 причины:

Это делает ваш контракт между клиентом и базой данных стабильным. Одни и те же данные, каждый раз
Производительность, покрытие показателей

Изменить (июль 2011 года):

Если вы перетащите из обозревателя объектов Columns узел для таблицы, он помещает список CSV-столбцов в окне запросов для вас, что достигает одной из ваших целей

user1369235 13 июл '12 в 07:03 2012-07-13 07:03 · Answer 3 · 2012-07-13 07:03

Если вы не хотите писать каждое имя столбца вручную, вы можете использовать Script Table As щелкнув правой кнопкой мыши по таблице или представлению в SSMS следующим образом:

Затем вы получите весь запрос select в окне New Query Editor, а затем удалите ненужный столбец, например:

Готово

user3856046 04 ноя '19 в 23:56 2019-11-04 23:56 · Answer 4 · 2019-11-04 23:56

Современный диалект SQL, такой как BigQuery, предлагает отличное решение

ВЫБЕРИТЕ * ИСКЛЮЧЕНИЕ (ИмяСтолбца X, [ИмяСтолбца Y, ...])

Это очень мощный синтаксис SQL, позволяющий избежать длинного списка столбцов, которые необходимо постоянно обновлять из-за изменения имени столбца таблицы. И эта функциональность отсутствует в текущей реализации SQL Server, что очень жаль. Надеюсь, что однажды Microsoft Azure станет более удобной для специалистов по данным.

Специалистам по обработке данных нравится иметь возможность быстро сократить запрос и удалить некоторые столбцы (из-за дублирования или по любой другой причине).

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

user844320 14 июл '11 в 10:19 2011-07-14 10:19 · Answer 5 · 2011-07-14 10:19

Автоматический способ сделать это в 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);

user7293 08 апр '09 в 09:33 2009-04-08 09:33 · Answer 6 · 2009-04-08 09:33

Вы можете создать представление, содержащее столбцы, которые вы хотите выбрать, затем вы можете просто выбрать * из представления...

56

Источник

user7293 08 апр '09 в 09:33

user63264 13 апр '10 в 09:58 2010-04-13 09:58 · Answer 7 · 2010-04-13 09:58

Да, это возможно (но не рекомендуется).

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, для разрешения имен столбцов во время выполнения. Это необходимо, потому что мы не знаем имен столбцов во время компиляции.

user56092 08 апр '09 в 09:21 2009-04-08 09:21 · Answer 8 · 2009-04-08 09:21

Как и другие говорили, что сделать это невозможно, но если вы используете Sql Server, уловка, которую я использую, состоит в том, чтобы изменить вывод на запятую, а затем

select top 1 * from table

и вырезать весь список столбцов из окна вывода. Затем вы можете выбрать, какие столбцы вы хотите, не вводя их все.

user13302 08 апр '09 в 12:37 2009-04-08 12:37 · Answer 9 · 2009-04-08 12:37

По сути, вы не можете делать то, что хотели бы, но вы можете получить правильные инструменты, которые помогут вам сделать вещи немного проще.

Если вы посмотрите на SQL-запрос Red-Gate, вы можете набрать "SELECT * FROM MyTable", а затем переместить курсор назад после "*" и нажать , чтобы развернуть список полей и удалить те несколько полей, которые вы не нужно

Это не идеальное решение, но чертовски хорошее!:-) Жаль, что Intellisense в MS SQL Server Management Studio все еще недостаточно интеллектуален, чтобы предлагать эту функцию.......

Марк

user10159774 12 ноя '19 в 12:02 2019-11-12 12:02 · Answer 10 · 2019-11-12 12:02

Если вы хотите исключить столбец с чувствительным регистром, например пароль, я делаю это, чтобы скрыть значение:

SELECT * , "" as password FROM tableName;

12

Источник

user10159774 12 ноя '19 в 12:02

user5243515 16 окт '17 в 15:58 2017-10-16 15:58 · Answer 11 · 2017-10-16 15:58

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)

ОБНОВИТЬ:

Вы также можете создать хранимую процедуру для решения этой задачи, если будете использовать ее чаще. В этом примере я использовал встроенную функцию STRING_SPLIT(), которая доступна в SQL Server 2016+, но, если вам нужно, есть множество примеров того, как создать его вручную в 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';

user74857 08 апр '09 в 09:19 2009-04-08 09:19 · Answer 12 · 2009-04-08 09:19

Нет, нет способа сделать это. может быть, вы можете создать собственные представления, если это возможно в вашей ситуации

РЕДАКТИРОВАТЬ Может быть, если ваша БД поддерживает выполнение динамического SQL, вы могли бы написать SP и передать столбцы, которые вы не хотите видеть, и позволить ему динамически создавать запрос и возвращать результат вам. Я думаю, что это выполнимо в SQL Server по крайней мере

user919669 30 авг '11 в 12:16 2011-08-30 12:16 · Answer 13 · 2011-08-30 12:16

Если вы используете SQL Server Management Studio, выполните следующие действия:

Введите желаемое имя таблицы и выберите его
Нажмите Alt+F1
o / p показывает столбцы в таблице.
Выберите нужные столбцы
Скопируйте и вставьте их в ваш запрос выбора
Запустите запрос.

Наслаждаться.

9

Источник

user919669 30 авг '11 в 12:16

user6491713 28 ноя '22 в 16:43 2022-11-28 16:43 · Answer 14 · 2022-11-28 16:43

На самом деле снежинка только что выпущена excludeтак что теперь вы просто:

SELECT * EXCLUDE [columnA,columnB,...] FROM tableA

9

Источник

user6491713 28 ноя '22 в 16:43

user3899080 30 июн '16 в 12:37 2016-06-30 12:37 · Answer 15 · 2016-06-30 12:37

Лучший способ решить эту проблему - использовать представление, вы можете создать представление с необходимыми столбцами и получить данные из него.

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)

user109000 06 май '10 в 19:03 2010-05-06 19:03 · Answer 16 · 2010-05-06 19:03

Таким образом, вы не можете сделать это, но я не согласен со всеми комментариями, приведенными выше, существуют "сценарии", в которых вы можете законно использовать * при создании вложенного запроса для выбора определенного диапазона из целого списка (например, paging) почему в мире захочется указать каждый столбец во внешнем операторе select, когда вы сделали это во внутреннем?

user3393089 15 фев '16 в 11:39 2016-02-15 11:39 · Answer 17 · 2016-02-15 11:39

Postgres sql имеет способ сделать это

Пожалуйста, обратитесь: http://www.postgresonline.com/journal/archives/41-How-to-SELECT-ALL-EXCEPT-some-columns-in-a-table.html

Информационная схема Hack Way

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-оператор, который выглядит следующим образом

ВЫБРАТЬ o.officepark,o.owner,o.squarefootage ОТ офисного парка As o

user52201 19 май '10 в 07:55 2010-05-19 07:55 · Answer 18 · 2010-05-19 07:55

В SQL Management Studio можно развернуть столбцы в обозревателе объектов, а затем перетащить Columns Элемент дерева в окне запроса, чтобы получить список столбцов через запятую.

8

Источник

user52201 19 май '10 в 07:55

user3661092 21 май '14 в 13:49 2014-05-21 13:49 · Answer 19 · 2014-05-21 13:49

Если мы говорим о процедурах, он работает с этим трюком, чтобы сгенерировать новый запрос и выполнить его немедленно:

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');

7

Источник

user3661092 21 май '14 в 13:49

user15354 24 мар '11 в 12:48 2011-03-24 12:48 · Answer 20 · 2011-03-24 12:48

Есть ли способ исключить столбцы из таблицы без указания всех столбцов?

Использование декларативного SQL обычным способом, нет.

Я думаю, что ваш предложенный синтаксис достоин и хорош. На самом деле язык реляционной базы данных "Учебник D" имеет очень похожий синтаксис, где ключевые слова ALL BUT сопровождаются набором атрибутов (столбцов).

Тем не менее, SQL SELECT * уже много раздражает (ответ @Guffa здесь - типичное возражение), так что я не думаю, SELECT ALL BUT скоро попадет в стандарт SQL.

Я думаю, что лучший способ обойти это создать VIEW только столбцы, которые вы хотите, то SELECT * FROM ThatView,

user23562 08 апр '09 в 09:19 2009-04-08 09:19 · Answer 21 · 2009-04-08 09:19

Я не знаю ни одной базы данных, которая бы поддерживала это (SQL Server, MySQL, Oracle, PostgreSQL). Это определенно не является частью стандартов SQL, поэтому я думаю, что вы должны указывать только те столбцы, которые вам нужны.

Конечно, вы можете динамически построить оператор SQL и выполнить его на сервере. Но это открывает возможность для инъекций SQL.

user277337 24 мар '11 в 12:57 2011-03-24 12:57 · Answer 22 · 2011-03-24 12:57

Щелкните правой кнопкой мыши таблицу в проводнике объектов, выберите первые 1000 строк

В нем будут перечислены все столбцы, а не *. Затем удалите ненужные столбцы. Должно быть намного быстрее, чем печатать самостоятельно.

Затем, когда вы почувствуете, что это слишком сложная работа, получите SQL-запрос от Red Gate и введите ssf из tbl, перейдите к * и снова нажмите вкладку.

user3666215 22 май '14 в 17:41 2014-05-22 17:41 · Answer 23 · 2014-05-22 17:41

Я знаю, что это немного старо, но я только что столкнулся с той же проблемой и искал ответ. Затем старший разработчик показал мне очень простой трюк.

Если вы используете редактор запросов Management Studio, разверните базу данных, затем разверните таблицу, из которой вы выбираете, чтобы увидеть папку столбцов.

В вашем операторе select просто выделите указанную выше папку столбцов и перетащите ее в окно запроса. Он вставит все столбцы таблицы, а затем просто удалит столбец идентификаторов из списка столбцов...

user76161 08 апр '09 в 09:24 2009-04-08 09:24 · Answer 24 · 2009-04-08 09:24

Что ж, это обычная лучшая практика - указывать, какие столбцы вы хотите, вместо того, чтобы просто указывать *. Поэтому вы должны просто указать, какие поля вы хотите, чтобы ваш выбор возвращал.

3

Источник

user76161 08 апр '09 в 09:24

user1007139 02 фев '12 в 13:02 2012-02-02 13:02 · Answer 25 · 2012-02-02 13:02

Колледж посоветовал хорошую альтернативу:

Сделайте SELECT INTO в предыдущем запросе (где вы генерируете или получаете данные) в таблицу (которую вы удалите, когда закончите). Это создаст структуру для вас.
Сделайте скрипт как CREATE для нового окна запроса.
Удалите ненужные столбцы. Отформатируйте оставшиеся столбцы в 1 строку и вставьте в качестве списка столбцов.
Удалить таблицу, которую вы создали.

Готово...

Это нам очень помогло.

user1487875 02 май '15 в 10:17 2015-05-02 10:17 · Answer 26 · 2015-05-02 10:17

Я сделал это так, и все работает отлично (версия 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;

user4627531 03 мар '15 в 11:52 2015-03-03 11:52 · Answer 27 · 2015-03-03 11:52

Вот что я часто использую для этого случая:

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

2

Источник

user4627531 03 мар '15 в 11:52

user8712683 23 июн '20 в 11:57 2020-06-23 11:57 · Answer 28 · 2020-06-23 11:57

В Hive Sql вы можете сделать это:

set hive.support.quoted.identifiers=none;
select 
    `(unwanted_col1|unwanted_col2|unwanted_col3)?+.+`
from database.table

это дает вам остальные cols

2

Источник

user8712683 23 июн '20 в 11:57

user5396869 01 окт '15 в 10:19 2015-10-01 10:19 · Answer 29 · 2015-10-01 10:19

Иногда одна и та же программа должна обрабатывать разные структуры базы данных. Поэтому я не мог использовать список столбцов в программе, чтобы избежать ошибок в 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

29 мар '21 в 19:03 2021-03-29 19:03 · Answer 30 · 2021-03-29 19:03

Попробуй это...

      =QUERY(TRANSPOSE(QUERY('Data'!A1:AH,"SELECT * ",1)),"SELECT * WHERE Col1 <> 'Column 
 name'",1)

1

Источник

29 мар '21 в 19:03