Вычисляемый столбец sql: Указание вычисляемых столбцов в таблице — SQL Server

sql — Как сослаться на вычисляемый столбец?


Вопрос задан


Изменён
7 месяцев назад


Просмотрен
86 раз

Есть задача:

Какая разница в стоимости между самым дорогим и самым дешевым заказом из США.

решить нужно через онлайн тренажер вот здесь:
https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SELECT Customers.Country, SUM(Quantity*Price) as Sum, OrderDetails.OrderID
FROM Customers join Orders ON Customers.CustomerID = Orders.CustomerID
join OrderDetails ON Orders.OrderID = OrderDetails.OrderID
join Products ON OrderDetails.ProductID = Products.ProductID
where Customers.Country IN ('USA')
GROUP BY OrderDetails.OrderID

Написал код, который считает стоимость заказов из штатов. Есть ли способ обратиться к вычисляемому столбцу Sum? Потому, что если в SELECT прописывать

MaX(Sum)-Min(Sum) as diff

появляется ошибка no such column:






2

Получилось решить через подзапросы:

WITH Summa_z AS
    (SELECT  OrderDetails.OrderID, Quantity*Price as Summa FROM Products
        INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID GROUP BY OrderDetails.OrderID ORDER BY Quantity*Price DESC),
     USA AS
    (SELECT  Orders.CustomerID, Country, OrderID  FROM Customers
        INNER JOIN Orders ON Orders.CustomerID = Customers.CustomerID WHERE Country IN ('USA'))
SELECT Max(Summa)-Min(Summa) as Result FROM Summa_z join USA ON USA.OrderID = Summa_z.OrderID







Зарегистрируйтесь или войдите

Регистрация через Google

Регистрация через Facebook

Регистрация через почту

Отправить без регистрации

Почта

Необходима, но никому не показывается

Отправить без регистрации



Почта

Необходима, но никому не показывается




By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.


Online Documentation for SQL Manager for PostgreSQL


Выводимые столбцы


На этой вкладке Вы формируете выходную форму запроса.


Вы указываете, какие столбцы будут отображаться в результате запроса и как они будут отсортированы и сгруппированы. Можно перетащить нужный столбец из рабочей области или выбрать его из раскрывающегося списка Source column name. Также, Вы можете создавать вычисляемые столбцы.


С помощью кнопок Вы можете изменить порядок столбцов.


 


 


  • В столбце Name of output указан псевдоним столбца, под которым оно будет выведено в результате запроса.


  • Столбец Aggregate содержит аггрегирующую функцию.


  • Source column name содержит настоящие имена столбцов, выводимых в результат запроса.


  • Grouping указывает сгруппирован столбец или нет.


 


Если флажок Select only unique records установлен, то в результате запроса отображаются только неповторяющиеся записи.


 


Основные действия с записями выполняются при помощи контекстного меню, которое открывается по нажатию на записи правой кнопкой мыши.


 


 


С помощью этого меню выполняются следующие операции:


  • удаление выделенного столбца — Delete current row,


  • вставка вложенного запроса — Insert Query,


  • вставка вычисляемого столбца — Insert CASE.


 


 


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


Порядок столбцов в результате запроса будет соответствовать их последовательности в этой таблице.


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


 






Смотрите также:


Задание критериев


Критерии группировки


Параметры сортировки


 

mysql — оператор выбора SQL, который создает вычисляемый столбец, а затем использует вычисляемый столбец для другого вычисляемого столбца

Задавать вопрос

спросил

Изменено
1 год, 3 месяца назад

Просмотрено
1к раз

Мне нужно использовать вычисляемый столбец для другого столбца.

Вот что у меня есть:

 SELECT item_name,
       список цен,
       скидка_процент,
       цена_списка * процент_скидки КАК сумма_скидки,
       цена_списка * процент_скидки - сумма_скидки КАК цена_скидки,
ИЗ предметов
ЗАКАЗАТЬ ПО Discount_price DESC

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

Я хотел бы использовать для этого оператор выбора, поэтому я не исследовал никаких других способов. Кто-нибудь видит очевидные ошибки?

Вам потребуется табличное выражение, чтобы «назвать» создаваемое вами выражение. Затем вы можете использовать его во внешнем запросе, как показано ниже:

 Например:
выбирать
  *,
  цена_списка * процент_скидки - сумма_скидки КАК цена_скидки,
от (
  ВЫБЕРИТЕ имя_элемента,
       список цен,
       скидка_процент,
       цена_списка * процент_скидки КАК сумма_скидки
  ИЗ предметов
) Икс
ЗАКАЗАТЬ ПО Discount_price DESC

В MySQL для этого можно использовать встроенные переменные. Во-первых, вы можете объявить переменную с помощью @ и назначить ее на основе :=, и это столбец результатов, которому присвоено имя. ТОГДА эта @variable доступна для использования в следующем столбце выбора. Чтобы объявить переменную, просто установите в предложении FROM псевдоним, а затем продолжайте как обычно.

 ВЫБЕРИТЕ имя_элемента,
       список цен,
       скидка_процент,
       -- если предположить, что процент скидки в 10% будет равен 0,10, а не 10.
       -- если 10% = 10,0, то делаем ( Discount_percentage / 100,0 )
       @myDiscount := цена_списка * процент_скидки КАК сумма_скидки,
       list_price - @myDiscount КАК Discount_price
   ОТ
      предметы,
      ( выберите @myDiscount := 0.0 ) sqlvars
   СОРТИРОВАТЬ ПО
      Discount_price DESC

Поскольку псевдоним SQLVars всегда будет возвращать только 1 запись, мы можем просто иметь дополнительную таблицу, разделенную запятыми, предложение JOIN не требуется. Это объявляет переменную.

Затем в таблице COLUMNS вы можете увидеть, как она ПЕРВЫМ рассчитывается путем расчета суммы скидки и присвоения переменной @myDiscount VARIABLE, но сохраняется в столбце окончательного результата DISCOUNT_AMOUNT. Затем ПЕРЕМЕННАЯ может быть использована как прямое вычитание из прейскурантной цены цены со скидкой.

Нет необходимости выбирать из результатов выбора

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

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

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

Что такое вычисляемый столбец?

Если в таблицах базы данных sql есть столбцы, значения которых зависят от других столбцов, вы можете использовать вычисляемые столбцы.
Использование вычисляемых столбцов или вычисляемых столбцов позволит вам сохранить логику вычисления таких вычисляемых значений в базе данных.
И избавит вас от дополнительного кодирования каждый раз, когда вам потребуется вычисляемый элемент.

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

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

Вычисляемые столбцы на самом деле физически не создаются в связанной таблице базы данных, если только они не определены как «сохраняемые» вычисляемые столбцы.
Вы можете думать о вычисляемых столбцах как о виртуальных столбцах. Они физически не хранятся в соответствующей таблице базы данных sql.
Они вычисляются каждый раз, когда на них ссылаются в операторе sql.
Если вы хотите создать индекс для вычисляемого столбца, то
Если вам требуется, чтобы вычисляемый столбец сохранялся в базе данных, а не вычислялся каждый раз, вы должны определить вычисляемый столбец как сохраняемый, используя ключевое слово «PERSISTED» в операторах CREATE TABLE . .. ADD column или ALTER TABLE … ALTER COLUMN.
Их значения обновляются при изменении любых столбцов, которые являются частью их расчета.
Пометив вычисляемый столбец как PERSISTED, вы можете создать индекс для вычисляемого столбца, который будет детерминированным, но не точным.
Кроме того, если вычисляемый столбец ссылается на функцию CLR, Компонент Database Engine не может проверить, действительно ли эта функция является детерминированной.

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

Подзапросы в этом контексте запрещены. Допускаются только скалярные выражения.

Вычисляемый столбец вычисляется из выражения. Но выражение не может быть подзапросом.
Если вам нужно выполнить оператор SELECT для сбора необходимых данных для выражения значения вычисляемого столбца, можно использовать определяемую пользователем функцию.
Вы можете создать определяемую пользователем функцию, которая может выполнять операторы SELECT.
Но если вы решите создать или изменить вычисляемый столбец для сохранения, у вас могут возникнуть некоторые другие проблемы, связанные с детерминизмом, особенно для типов данных XML и XQuery.
Вы можете решить проблему создания сохраняемого вычисляемого столбца, определив определяемую пользователем функцию (udf), которая используется в выражении, используя «WITH SCHEMABINDING» с пользовательской функцией sql.

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

Вы не можете создавать вычисляемые столбцы/вычисляемые столбцы как сохраненные, если в выражении вычисляемого столбца есть определяемая пользователем функция, и эта функция выполняет запросы SELECT из таблиц базы данных.
Это связано с тем, что если вы используете определяемую пользователем функцию sql в выражении вычисляемого столбца, чтобы вычисляемый столбец сохранялся, udf должен быть детерминированным.
Для детерминизма такой функции sql необходимо определить определяемую пользователем функцию t-sql с помощью «WITH SCHEMABINDING» .
В этом случае для привязки схемы потребуются имена таблиц в формате, состоящем из двух частей.
И когда вы изменяете функцию sql с именами таблиц в двухкомпонентном формате, включая имя схемы и подсказку «WITH SCHEMABINDING», на этот раз функцию нельзя будет использовать в выражении сохраняемого столбца.
Поскольку в этом случае столбец будет определен с доступом к пользовательским данным, который не подходит для сохраняемого вычисляемого столбца.

Примеры кодов вычисляемых столбцов T-SQL для таблиц SQL Server

Здесь вы можете найти примеры, в которых свойства вычисляемого столбца иллюстрируются примером кода t-sql.


CREATE TABLE Authors
(
 AuthorId int IDENTITY(1,1) NOT NULL,
 FirstName nvarchar(100),
 LastName nvarchar(100),
  FullName AS (FirstName + SPACE(1) + LastName) -- вычислить г столбец
)

Код

В приведенном выше примере таблицы вычисляемый столбец FullName определен как объединение значений двух других столбцов в той же таблице.
Это пример простого выражения. Мы определяем вычисляемый столбец с помощью предложения » AS » и видим, что мы не определяем тип столбца, поскольку тип определяется неявно выражением вычисления.


INSERT INTO Authors (FirstName, LastName) VALUES (N'Roger', N'Wolter')
INSERT INTO Authors (FirstName, LastName) VALUES (N'Dejan', N'Sarka')
SELECT * FROM Authors

Код

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


ALTER TABLE Authors ADD FullName2 AS (СЛУЧАЙ, КОГДА AuthorId < 3 THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)

Код

Теперь вставьте некоторые данные в таблицу sql и посмотрите результаты.


ВСТАВИТЬ В Авторы (Имя, Фамилия) ЗНАЧЕНИЯ (Н'Ицик', Н'Бен-Ган')
ВЫБРАТЬ * ОТ Авторов

Код

Теперь давайте создадим вспомогательную таблицу базы данных SQL Server, которую мы будем использовать в наших следующих примерах.


Создать таблицу DBParameters (
ParameterId int Identity(1,1) NOT NULL,
ParameterDescription nvarchar(1000),
ParameterValue nvarchar(1000)
)
GO
вставить в DBParameters select N'Имя перед фамилией', N' 1'

Код

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


ALTER TABLE Authors ADD FullName3 AS (СЛУЧАЙ, КОГДА СУЩЕСТВУЕТ (SELECT * FROM DBParameters), THEN LastName + SPACE(1) + FirstName ELSE FirstName + SPACE(1) + LastName END)

Код

И ошибка, которую выдаст SQL Engine:


Сообщение 1046, уровень 15, состояние 1, строка 1
В этом контексте подзапросы не допускаются. Допускаются только скалярные выражения.

Код

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


CREATE FUNCTION GetBooleanParameterValue(
 @ParameterId as int
) ВОЗВРАЩАЕТ бит
AS
BEGIN
 DECLARE @result бит
 SELECT @result = CAST(ParameterValue AS bit) FROM DBParameters WHERE ParameterId = @ParameterId
 RETURN @result
END
GO
SELECT dbo. GetBooleanParameterValue(1)

Код

Теперь я попробую использовать определяемую пользователем функцию вместо запроса подвыборки.


ALTER TABLE Authors ADD FullName3 AS (СЛУЧАЙ, КОГДА dbo.GetBooleanParameterValue(1) = 1 THEN (FirstName + SPACE(1) + LastName) ELSE (LastName + SPACE(1) + FirstName) END)

Код
UPDATE DBParameters SET ParameterValue = 1 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 FROM Authors

UPDATE DBParameters SET ParameterValue = 0 WHERE ParameterId = 1
SELECT FirstName, LastName, FullName3 from Authors

Код

При первом выборе отображается имя перед фамилией в полном имени.
И после обновления таблицы параметров второй выбор отображает фамилии перед последним.

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


CREATE TABLE [Author Expertise]
(
 Id int Identity(1,1) NOT NULL,
 AuthorId int,
 Expertise nvarchar(1000) NOT NULL
)
GO

INSERT INTO [Author Expertise] SELECT 1, Н'Т- SQL'
ВСТАВИТЬ В [Экспертиза автора] SELECT 1, N'CLR'
ВСТАВИТЬ В [Экспертиза автора] SELECT 2, N'CLR'
ВСТАВИТЬ В [Экспертиза автора] SELECT 2, N'XML'
ВСТАВИТЬ В [Экспертиза автора] ] ВЫБЕРИТЕ 3, N'T-SQL'
INSERT INTO [Author Expertise] SELECT 3, N'CLR'
INSERT INTO [Author Expertise] SELECT 3, N'Service Broker'
GO

CREATE FUNCTION GetAuthorExpertiseCount(
 @AuthorId as int
) ВОЗВРАЩАЕТСЯ в t
КАК
НАЧАЛО
 DECLARE @result int
 SELECT @result = COUNT(*) FROM [Author Expertise] WHERE AuthorId = @AuthorId
 RETURN @result
END
GO

SELECT dbo. GetAuthorExpertiseCount(3) 9006 2

Код

ALTER TABLE Authors ADD Expertise_Topics_Count AS ( dbo.GetAuthorExpertiseCount(AuthorId))
SELECT * FROM Authors

Код


ALTER TABLE Authors ADD BooksInfo XML
GO
UPDATE Authors
SET BooksInfo = N'
<книги>
 
  <технология>Microsoft SQL Server 2005< /technology>

 
  <технология>Microsoft SQL Server 2008
 
'
WHERE AuthorId = 3
GO
CREATE FUNCTION GetLastBookTitle (
 @BooksInfo XML
) ВОЗВРАЩАЕТ nvarchar( 1000)
КАК
НАЧАЛО
 DECLARE @Title nvarchar(1000)

 выберите
  @Title = i.value('@title', 'nvarchar(1000)')
 из @BooksInfo.nodes('/books/book') n(i)
 заказ by CONVERT(datetime, i.value('./publishdate[1]/. ', 'nvarchar(1000)'), 103) desc

 RETURN @Title
END
GO
ALTER TABLE Authors ADD LastBookTitle AS dbo.GetLastBookTitle(BooksInfo)
GO
SELECT AuthorId, Fullname3, BooksInfo, LastBookTitle FROM Authors
90 003

Код


ALTER TABLE Authors ADD BirthDate datetime
GO
UPDATE Authors SET BirthDate = '19700101' WHERE AuthorId = 1
GO
ALTER TABLE Authors ADD Age AS DATEDIFF(yy, BirthDate, GETDATE())
9 0003
Код

Давайте добавим другую таблицу возраста, которая зависит от другого вычисляемого столбца.


ИЗМЕНИТЬ ТАБЛИЦУ Авторы ДОБАВИТЬ Age2 AS DATEADD(yy, Age, BirthDate)

Код

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

.
Сообщение 1759, уровень 16, состояние 0, строка 1
Вычисляемый столбец «Возраст» в таблице «Авторы» не может использоваться в другом определении вычисляемого столбца.

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


CREATE FUNCTION TestUDF(
 @Age int,
 @BirthDate datetime
) RETURN datetime
AS
BEGIN
 RETURN DATEADD(yy, @Age, @BirthDate)
END
GO 9006 2 ALTER TABLE Authors ДОБАВИТЬ Age2 AS dbo.TestUDF(Age, Дата рождения)

Код

SQL Engine выдаст следующее сообщение об ошибке в этой команде ALTER TABLE.

Сообщение 1759, уровень 16, состояние 0, строка 1
Вычисляемый столбец «Возраст» в таблице «Авторы» не может использоваться в другом определении вычисляемого столбца.

Что если мы хотим определить вычисляемый столбец как столбец PERSISTED, чтобы создать индексы для этих столбцов.


ALTER TABLE Authors ADD LastBookTitlePersisted AS dbo.GetLastBookTitle(BooksInfo) Persisted

Код

Сообщение 4936, уровень 16, состояние 1, строка 1
Вычисляемый столбец «LastBookTitlePersisted» в таблице «Authors» не может быть сохранен, так как столбец не является детерминированным.

Причина, по которой мы не можем определить столбец LastBookTitlePersisted как PERSISTED, заключается в том, что вычисляемый столбец является недетерминированным из-за определяемой пользователем функции dbo.GetLastBookTitle() — это функция на основе XML.
Но решение этой проблемы для этого udf простое.
Если мы изменим процедуру, добавив подсказку "WITH SCHEMABINDING", мы сможем сохранить связанный столбец.


CREATE FUNCTION GetLastBookTitle2 (
 @BooksInfo XML
) ВОЗВРАЩАЕТ nvarchar(1000)
WITH SCHEMABINDING -- "WITH SCHEMABINDING" необходим, если я хочу, чтобы столбец был постоянным вычисляемым столбцом
AS
BEGIN

 DECLARE @Title nvarchar(1000)

 выбрать
  @Title = i.value('@title', 'nvarchar(1000)')
 из @BooksInfo.nodes('/books/book') n(i)
 упорядочить по CONVERT(datetime, i.value(' ./publishdate[1]/.', 'nvarchar(1000)'), 103) desc

 RETURN @Title
END

Код

Теперь мы можем успешно создать столбец как сохраненный.