Борьба с SQL Где фильтр

У меня есть таблица, [Customer], которая имеет поля [postcode] и [totalValue].

Мне нужно спроектировать запрос, который возвращает первые два символа всего [почтового индекса], а затем SUM из [totalValue] для этой конкретной группы почтовых индексов (так что каждое значение для NW, L1 и т. Д.).

У меня есть этот код, который работает нормально:

SELECT LEFT(postcode, 2), SUM(totalValue) FROM Customer GROUP BY LEFT(postcode, 2) 

… однако у меня есть записи в [Customer], которые не содержат действительный [почтовый индекс].

Действующий британский почтовый индекс – это либо одна, либо две буквы, за которыми следует номер (например, LE1, L12 и т. Д.).

Я хотел бы отфильтровать все неправильные / пустые / нулевые записи [postcode] в отдельную запись записи, но это не соответствует моему набору навыков.

Вы можете использовать LIKE :

 where postcode like '[AZ][A-Z0-9][0-9]%' 

Вы также можете проверить длину и другие характеристики, но это отвечает на ваш конкретный вопрос.

РЕДАКТИРОВАТЬ:

Для отдельной записи используйте case :

 SELECT (CASE WHEN postcode like '[AZ][A-Z0-9][0-9]%' THEN LEFT(postcode, 2) ELSE 'Separate Entry' END) as PostCode2, SUM(totalValue) FROM Customer GROUP BY (CASE WHEN postcode like '[AZ][A-Z0-9][0-9]%' THEN LEFT(postcode, 2) ELSE 'Separate Entry' END); 

Ваши предположения о действительных почтовых индексах слегка выходят

Действующий британский почтовый индекс – это либо одна, либо две буквы, за которыми следует номер (например, LE1, L12 и т. Д.).

Проще говоря, допустимыми форматами для британского почтового индекса являются:

 +----------+---------------------------------------------+----------+ | Format | Coverage | Example | +----------+---------------------------------------------+----------+ | AA9A 9AA | WC postcode area; EC1–EC4, NW1W, SE1P, SW1 | EC1A 1BB | +----------+---------------------------------------------+----------+ | A9A 9AA | E1W, N1C, N1P | W1A 0AX | +----------+---------------------------------------------+----------+ | A9 9AA | B, E, G, L, M, N, S, W | M1 1AE | | A99 9AA | | B33 8TH | +----------+---------------------------------------------+----------+ | AA9 9AA | All other postcodes | CR2 6XH | | AA99 9AA | | DN55 1PT | +----------+---------------------------------------------+----------+ 

Каждый из них можно определить совпадение шаблонов для:

 AA9A 9AA - [AZ][AZ][0-9][AZ] [0-9][AZ][AZ] A9A 9AA - [AZ][0-9][AZ] [0-9][AZ][AZ] A9 9AA - [AZ][0-9] [0-9][AZ][AZ] A99 9AA - [AZ][0-9][0-9] [0-9][AZ][AZ] AA9 9AA - [AZ][AZ][0-9] [0-9][AZ][AZ] AA99 9AA - [AZ][AZ][0-9][0-9] [0-9][AZ][AZ] 

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

 CREATE TABLE dbo.SimplePostCodeValidation ( PostCode VARCHAR(8) NOT NULL, Pattern VARCHAR(50) NOT NULL ); INSERT dbo.SimplePostCodeValidation (PostCode, Pattern) VALUES ('AA9A 9AA', '[AZ][AZ][0-9][AZ] [0-9][AZ][AZ]'), ('A9A 9AA', '[AZ][0-9][AZ] [0-9][AZ][AZ]'), ('A9 9AA', '[AZ][0-9] [0-9][AZ][AZ]'), ('A99 9AA', '[AZ][0-9][0-9] [0-9][AZ][AZ]'), ('AA9 9AA', '[AZ][AZ][0-9] [0-9][AZ][AZ]'), ('AA99 9AA', '[AZ][AZ][0-9][0-9][0-9][AZ][AZ]'), -- REPEAT THE POSTCODES WITHOUT SPACES ('AA9A9AA', '[AZ][AZ][0-9][AZ][0-9][AZ][AZ]'), ('A9A9AA', '[AZ][0-9][AZ][0-9][AZ][AZ]'), ('A99AA', '[AZ][0-9][0-9][AZ][AZ]'), ('A999AA', '[AZ][0-9][0-9][0-9][AZ][AZ]'), ('AA99AA', '[AZ][AZ][0-9][0-9][AZ][AZ]'), ('AA999AA', '[AZ][AZ][0-9][0-9][0-9][AZ][AZ]'); 

Теперь вы можете легко проверить свои почтовые индексы:

 DECLARE @T TABLE (Postcode VARCHAR(8)); INSERT @T (PostCode) SELECT PostCode FROM dbo.SimplePostCodeValidation UNION ALL SELECT PostCode FROM (VALUES ('123456'), (''), ('TEST')) t (PostCode); SELECT t.PostCode, IsValid = CASE WHEN pc.PostCode IS NULL THEN 0 ELSE 1 END FROM @T AS t LEFT JOIN SimplePostCodeValidation AS pc ON t.PostCode LIKE pc.Pattern; 

Что возвращает:

 PostCode IsValid ---------------------- AA9A 9AA 1 A9A 9AA 1 A9 9AA 1 A99 9AA 1 AA9 9AA 1 AA99 9AA 1 123456 0 0 TEST 0 

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

 SELECT CASE WHEN pc.PostCode IS NULL THEN 'Invalid' ELSE LEFT(c.postcode, 2) END, TotalValue = SUM(totalValue) FROM Customer AS c LEFT JOIN SimplePostCodeValidation AS pc ON t.PostCode LIKE pc.Pattern; GROUP BY CASE WHEN pc.PostCode IS NULL THEN 'Invalid' ELSE LEFT(c.postcode, 2) END; 

Если вы хотите усложниться, на самом деле существуют дополнительные ограничения на то, что является допустимым почтовым индексом, например, если это шаблон A9 9AA тогда первая буква может быть только одной из (B, E, G, L, M, N, S, W). Руководства, изложенные в wikipedia state:

  • Области с только одноразрядными районами: BR, FY, HA, HD, HG, HR, HS, HX, JE, LD, SM, SR, WC, WN, ZE (хотя WC всегда подразделяется на другое письмо, например WC1A ).
  • Районы с только двузначными районами: AB, LL, SO.
  • Области с областью «0» (ноль): BL, BS, CM, CR, FY, HA, PR, SL, SS (BS – единственная область, в которой есть как район 0, так и район 10).
  • Следующие центральные Лондонские одноразрядные районы были дополнительно разделены путем добавления буквы после цифры и перед пространством: EC1-EC4 (но не EC50), SW1, W1, WC1, WC2 и часть E1 (E1W), N1 (N1C и N1P), NW1 (NW1W) и SE1 (SE1P).
  • Буквы QVX не используются в первой позиции.
  • Буквы IJZ не используются во второй позиции.
  • Единственными буквами, которые появляются на третьей позиции, являются ABCDEFGHJKPSTUW, когда структура начинается с A9A.
  • Единственными буквами, которые появляются в четвертой позиции, являются ABEHMNPRVWXY, когда структура начинается с AA9A.
  • Последние две буквы не используют буквы CIKMOV, чтобы не напоминать цифры или друг друга при написании рукописей.
  • Секторы почтового кода – одна из десяти цифр: от 0 до 9 с 0, используемая только один раз, когда 9 использовали в почтовом городке, за исключением Кройдона и Ньюпорта (см. Выше).

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

Попробуй это. Все неправильные (или NULL) почтовые индексы будут накапливаться в записи «-»:

 SELECT CASE WHEN postcode like '[AZ][A-Z0-9][0-9]%' THEN LEFT(postcode, 2) ELSE '--' END, SUM(totalValue) FROM Customer GROUP BY CASE WHEN postcode like '[AZ][A-Z0-9][0-9]%' THEN LEFT(postcode, 2) ELSE '--' END 

Запрос:

 WITH CTE AS (SELECT CASE WHEN postcode LIKE('[AZ][A-Z0-9][0-9]%') THEN LEFT(postcode,2) ELSE 'Invalid' END AS PostCode ,totalValue FROM Customer) SELECT PostCode, SUM(totalValue) as totalValue FROM CTE GROUP BY PostCode 

Использует CTE для сбора данных. Недействительные почтовые индексы будут сгруппированы в «Недействительный».

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

 SELECT LEFT(postcode, 2), SUM(totalValue) FROM Customer WHERE ISNUMERIC(LEFT(postcode, 1))<> 1 GROUP BY LEFT(postcode, 2) 

Надеюсь это поможет.

Это выбор:

 SELECT LEFT(postcode, 2), SUM(totalValue) FROM Customer WHERE postcode IS NOT NULL AND postcode != '' AND dbo.fn_isValid(postcode) = 1 GROUP BY LEFT(postcode, 2) UNION SELECT 'NULL', SUM(totalValue) FROM Customer WHERE postcode IS NULL GROUP BY LEFT(postcode, 2) UNION SELECT 'EMPTY', SUM(totalValue) FROM Customer WHERE postcode = '' GROUP BY LEFT(postcode, 2) UNION SELECT 'WRONG', SUM(totalValue) FROM Customer WHERE dbo.fn_isValid(postcode) = 0 GROUP BY LEFT(postcode, 2) 

Тогда вам нужна функция оценки, что-то вроде этого:

 CREATE FUNCTION [dbo].[fn_IsValid] ( @Code nchar(32) ) RETURNS bit AS BEGIN DECLARE @Status bit SET @Status = -- Your validation logic --be aware of empty and null values RETURN @Status END 
Interesting Posts

предоставлять разрешение на все операции с базой данных

Аудит каждой вставленной строки в триггере

Не удалось создать объект IDataAdapter. Этот провайдер может не полностью поддерживаться с помощью задачи Execute SQL

Создание специального инструмента запросов SQL, который не требует от пользователя знания SQL (ASP.NET)

Ошибка арифметического переполнения, преобразующая выражение в тип данных datetime (Int64 to datetime)

SQL-запрос для обновления столбцов таблицы, где входные параметры не являются нулевыми?

Выбрать по битовому / логическому значению

MERGE также вставляет NULL-записи

В T-SQL под MS SQL Server 2008, что означает «@» перед значением параметра *, это строковый литерал?

Могу ли я иметь более одного индекса в таблице базы данных?

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

Перенос базы данных SQLite на Microsoft SQL Server

Настройка состояния NOCOUNT с локальной переменной

SQL Server – КАК

Добавление записи в базу данных с помощью C #

Давайте будем гением компьютера.