Блог

Главная - Вопросы по программированию - Создание вычисляемого столбца Int в SQL Server из текстового столбца

Создание вычисляемого столбца Int в SQL Server из текстового столбца

#sql #sql-server

Вопрос:

Я хочу создать новый столбец в существующей таблице SQL Server, в котором используются сокращения состояний (существующий столбец) для перекодирования состояний в число (1-50) для статистического анализа, который будет выполнен после экспорта выходных данных.

Предполагая, что Алабама равна AL = 1, а Вайоминг равен WY = 50, как бы я поступил так для каждого штата?

Комментарии:

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

2. Я также предлагаю вам сделать столбец в существующей таблице a FOREIGN KEY новой таблицы после того, как вы ее создали и заполнили, чтобы люди не могли добавлять несуществующие состояния. Кроме того, я надеюсь, что ваша колонка-это не text колонка; char(2) судя по всему, она должна быть «а».

Ответ №1:

Два способа:

  1. Создайте таблицу STATE_ID поиска (со значениями 1..50) и STATE_ABBREV (от «AL» до «WY»), затем присоединитесь к этой таблице.
  2. Создайте большое CASE заявление:
 CASE STATE_ABBR
  WHEN 'AL' THEN 1
  ...
  WHEN 'WY' THEN 50
  ELSE NULL
END AS STATE_ID

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

Комментарии:

1. DEFAULT NULL недопустимый синтаксис, он должен быть ELSE NULL или просто полностью опущен (поскольку NULL , по сути, используется по умолчанию, если сравнение не совпадает). Обратите внимание, что в T-SQL нет оператора case (в отличие от некоторых языков), это было бы выражение case. Помимо того, что вещи более удобны для обслуживания в отдельной таблице, подобное CASE выражение также имеет тенденцию работать плохо, так как оно расширяется до огромного OR внутреннего объема, оптимизатор не может собирать статистику по нему для целей объединения, и оно может быть пересчитано много раз.

2. Вы правы; я трачу слишком много времени на JavaScript 🙂 Спасибо

3. Сработало как надо, спасибо! Я только начинаю изучать SQL, так что я довольно неопытен-обычно я просто делаю статистику в SAS, так что все связанные таблицы для меня в новинку!

Ответ №2:

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

 create table #temp
( stateCode char(2))
INSERT INTO #temp values ('PA'),('AL'),('NJ'),('MA'),('DC')

select StateCode,charindex(Statecode '|','AL|PA|NJ|NY|MA|DC|')/3 1 as numb
from #temp
order by numb

Обратите внимание, что charindex работает медленно, но при желании этот подход можно адаптировать к вашему вычисляемому полю.

Опять же, я думаю, что таблица поиска-лучшее решение, но если это должно быть вычисляемое поле, это должно сработать для вас

Комментарии:

1. Если вы попробуете это сделать, вы увидите DC , что вам назначен неправильный номер (так как строка поиска не заканчивалась на | ). На самом деле, любой неверный код приведет к получению номера для AL , что, вероятно, нежелательно. Исправить обе проблемы легко, но делает выражение еще менее очевидным, чем оно есть ( nullif(charindex(Statecode '|','AL|PA|NJ|NY|MA|DC|'),0)/3 1 ). Я не поклонник CASE этого, но CHARINDEX кажется еще менее привлекательным с точки зрения ремонтопригодности.

2. Да, полностью согласен, я бы не хотел использовать CHARINDEX для чего-то важного. Вот почему я согласился с подходом, основанным на таблице поиска. Однако, если им действительно нужно вычисляемое поле, этот подход следует рассмотреть.

Метки: