Как в sql выбрать все столбцы кроме одного

Предикаты ALL, DISTINCT, DISTINCTROW и TOP

Эти предикаты задают записи, выбираемые с помощью запросов SQL.

Синтаксис

SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM таблица

Инструкция SELECT, содержащая эти предикаты, состоит из следующих частей:

Используется по умолчанию, если вы не указываете ни один из предикатов. Ядро СУБД Microsoft Access выбирает все записи, которые удовлетворяют условиям в инструкции SQL. Следующие два примера эквивалентны и возвращает все записи из таблицы Employees:

SELECT ALL *
FROM Employees
ORDER BY EmployeeID;
SELECT *
FROM Employees
ORDER BY EmployeeID;

Исключает записи, содержащие повторяющиеся данные в выбранных полях. Для включения в результаты запроса значения каждого из полей, перечисленных в инструкции SELECT, должны быть уникальными. Например, у нескольких сотрудников, перечисленных в таблице Employees, могут быть одинаковые фамилии. Если две записи содержат «Глазков» в поле LastName, следующая инструкция SQL возвращает только одну запись, содержащую значение «Глазков»:

SELECT DISTINCT LastName
FROM Employees;

Если опустить DISTINCT, этот запрос возвратит обе записи с фамилией «Глазков».

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

Выходные данные запроса, использующего DISTINCT, не является обновляемыми и не отражают изменения, внесенные другими пользователями.

Пропускает данные, основанные на всех повторяющихся записях, а не только на повторяющихся полях. Например, можно создать запрос, который объединяет таблицы Customers и Orders в поле CustomerID. Таблица Customers не содержит повторяющихся полей CustomerID, но таблица Orders делает это, так как у каждого клиента может быть много заказов. В следующей инструкции SQL показано, как можно использовать DISTINCTROW для создания списка компаний, имеющих по крайней мере один заказ, но без каких-либо сведений об этих заказах:

SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName;

Если опустить DISTINCTROW, этот запрос создаст несколько строк для каждой компании, от которой поступало более одного заказа.

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

TOP n [PERCENT]

Возвращает записи, относящиеся к верхней или нижней части диапазона, заданного предложением ORDER BY. Предположим, что вы хотите получить имена 25 лучших студентов из группы 1994 г.:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 2003
ORDER BY GradePointAverage DESC;

Если не включить предложение ORDER BY, запрос вернет из таблицы Students произвольный набор, включающий 25 записей, которые удовлетворяют предложению WHERE.

Предикат TOP не выбирает между равными значениями. Если в предыдущем примере двадцать пятый и двадцать шестой средний балл совпадают, запрос вернет 26 записей.

Вы также можете использовать зарезервированное слово PERCENT для возвращения определенного процента записей из верхней или нижней части диапазона, заданного предложением ORDER BY. Предположим, что вместо 25 лучших студентов вы хотите получить 10 процентов худших студентов группы:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 2003
ORDER BY GradePointAverage ASC;

Предикат ASC позволяет вернуть нижние значения. Значение после TOP должно быть целым числом без знака.

TOP не влияет на возможность обновления запроса.

Имя таблицы, из которой извлекаются записи.

Как выводить в запросе все столбцы кроме одного, не перечисляя их?

Другими словами, как исключить столбец с известным именем из результата запроса select * from Table?

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

Допустим, требуется выбрать все столбцы из таблицы Laptop кроме столбца code. Чтобы вывести все столбцы таблицы, достаточно написать

 SELECT * FROM Laptop;

Но чтобы исключить из списка столбец code, мы вынуждены перечислить все остальные столбцы:

 SELECT model, speed, ram, hd, price, screen FROM Laptop;

Было бы неплохо, если мы могли написать что-то типа

 SELECT *[^code] FROM Laptop;

Подобная возможность позволила бы нам избежать рутинной работы с вероятностью ошибиться при наборе и пригодилась бы при динамическом формировании запроса, когда заранее не известна ни таблица, ни число столбцов в ней.

Но, увы, такой возможности у нас нет. Зато мы можем сформировать список столбцов с помощью вспомогательного скрипта, чтобы в дальнейшем использовать его результаты в запросах. Это мы можем сделать, используя стандартное представление метаданных, которое называется информационной схемой:

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code');

Предикат NOT IN мы используем для того, чтобы можно было в перспективе исключать не один, а несколько столбцов. Теперь нам нужно получить результат в виде строки символов, представляющей собой разделенный запятыми перечень столбцов. Для этого нам уже придется использовать нестандартные средства.

SQL Server

В частности, для SQL Server мы можем это сделать при помощи конструкции FOR XML PATH, заменив попутно с помощью функции REPLACE пробел между именами столбцов на запятые:

 SELECT REPLACE( (SELECT COLUMN_NAME AS 'data()' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION FOR XML PATH('')) ,' ',', ');

В принципе, мы в состоянии сформировать требуемый оператор целиком с тем, чтобы его можно было динамически использовать в коде приложения:

 SELECT 'SELECT ' +REPLACE( (SELECT COLUMN_NAME AS 'data()' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION FOR XML PATH('')) ,' ',', ') + ' FROM Laptop';

Функция string_agg, появившаяся в SQL Server 2017, позволяет упростить логику запроса, заменив преобразование к XML:

 SELECT 'SELECT ' + (SELECT string_agg(COLUMN_NAME,', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ) + ' FROM Laptop';

MySQL

В случае MySQL мы можем воспользоваться специальной агрегатной функцией GROUP_CONCAT:

 SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'computers' AND TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION;

Следует отметить, что в MySQL информационная схема относится не к отдельной базе данных, а ко всему серверу. Поэтому на тот случай, если в разных базах сервера имеются таблицы с одинаковыми именами, в условия отбора нужно добавить предикат с указанием схемы (базы данных): TABLE_SCHEMA=’computers’.

Конкатенация в MySQL выполняется с помощью функции CONCAT. В итоге окончательное решение нашей задачи можно написать так:

 SELECT CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='computers' AND TABLE_NAME='Laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION ), ' FROM Laptop');

PostgreSQL

В PostgreSQL задачу представления значений столбца в виде текстового списка можно решить при помощи двух встроенных функций: ARRAY и ARRAY_TO_STRING. Первая из них преобразует выборку в массив значений, а вторая преобразует массив в список. При этом вторым параметром функции ARRAY_TO_STRING задается символ, который будет являться разделителем элементов списка. Теперь решение нашей задачи можно записать в виде:

 SELECT 'SELECT ' || ARRAY_TO_STRING(ARRAY(SELECT COLUMN_NAME::VARCHAR(50) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='laptop' AND COLUMN_NAME NOT IN ('code') ORDER BY ORDINAL_POSITION ), ', ') || ' FROM Laptop';

Заметим, что для конкатенации строк в PostgreSQL используется стандартный оператор «||«, и отметим необходимость явного приведения типа данных столбца COLUMN_NAME (information_schema.sql_identifier) к типу CHAR/VARCHAR.

Oracle

Oracle не поддерживает стандартный формат представления метаданных. Поэтому решение использует обращение к системной таблице user_tab_columns:

 SELECT 'SELECT ' || (SELECT LISTAGG(column_name,', ') WITHIN GROUP (ORDER BY column_id) FROM user_tab_columns WHERE table_name='LAPTOP' AND column_name NOT IN ('CODE') ) || ' FROM Laptop' FROM dual;

Как выбрать все поля из таблицы в базе данных кроме одного, не перечисляя их?

Как выбрать все поля из таблицы в базе данных кроме одного, не перечисляя их. Есть ли такая функция в MySQL?

Отслеживать
задан 24 июл 2015 в 12:04
61 1 1 золотой знак 1 1 серебряный знак 8 8 бронзовых знаков
Тут почитайте sql-ex.ru/help/select20.php?Lang=0
24 июл 2015 в 12:30

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

SELECT CONCAT( 'SELECT ', ( SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA. COLUMNS WHERE TABLE_SCHEMA = 'database' AND TABLE_NAME = 'table' AND COLUMN_NAME NOT IN ('field') ORDER BY ORDINAL_POSITION ), ' FROM table' ) INTO @asd; PREPARE newsql FROM @asd; EXECUTE newsql;

где database — база данных, table — таблица, field — поле (столбец), который исключаем. Решение так себе, но работает.

Отслеживать
ответ дан 24 июл 2015 в 13:35
user179410 user179410

Никак. Нет такого способа. Придется вытаскивать все, либо перечислять.

UPD: Хотя на английском SO был предложен способ =)

Отслеживать
ответ дан 24 июл 2015 в 12:09
2,166 3 3 золотых знака 14 14 серебряных знаков 29 29 бронзовых знаков

Важное на Мете

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

SQL — Урок 4. Выборка данных — оператор SELECT

Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT. Синтаксис его использования следующий:

SELECT что_выбрать FROM откуда_выбрать;

Вместо «что_выбрать» мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо «откуда_выбрать» следует указать имя таблицы.

Давайте сначала посмотрим все столбцы из таблицы users:

SELECT * FROM users;

Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:

SELECT id_user FROM users;

Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:

SELECT name, email FROM users;

Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:

SELECT * FROM topics;

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:

SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;

По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC

Теперь наши данные отсортированы в порядке по убыванию.

Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:

Сравните результат с результатом предыдущего запроса.

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE, синтаксис у такого запроса следующий:

SELECT имя_столбца FROM имя_таблицы WHERE условие;

Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):

SELECT * FROM topics WHERE id_author=4;

Или мы хотим узнать, кто создал тему «велосипеды»:

Конечно, было бы удобнее, чтобы вместо id автора, выводилось его имя, но имена хранятся в другой таблице. В последующих уроках мы узнаем, как выбирать данные из нескольких таблиц. А пока узнаем, какие условия можно задавать, используя ключевое слово WHERE.

Оператор	Описание
= (равно)	Отбираются значения равные указанному