SQL - Not In Range - VirtualIreland.ru

Zindur · 09.10.2006, 15:23

Вопрос на засыпку

Есть Таблица с полем наприер Field1 данными: 1,2,3,5,9,10

Задача (тута самое интересное) :D :
получить результат: 4,6,7,8 - тоесть то что нету в базе

Извращения:
1. выборку зделать по определёным параметрам: тоесть чАво нету между 2 и 5
2. поле может быть разного типа

пока вижу ответ только как, создать временную таблицу(со всеми данными) и сравнить эти таблицы.
База Данных Пока : MS SQL, Interbase, Access,.....
Варианты

TYA · 09.10.2006, 16:35

Цитата:

Сообщение от Zindur

Вопрос на засыпку

Есть Таблица с полем наприер Field1 данными: 1,2,3,5,9,10

Задача (тута самое интересное) :D :
получить результат: 4,6,7,8 - тоесть то что нету в базе

Извращения:
1. выборку зделать по определёным параметрам: тоесть чАво нету между 2 и 5
2. поле может быть разного типа

пока не вижу ответа как создавать временную таблицу и сравнить эти таблицы.
База Данных Пока : MS SQL, Interbase, Access,.....
Варианты

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

Если тип данных неизвестен (число, строка...) и правило порядка/последовательности может быть описан разными способомами, то есть предложение хранить правильный порядок/последовательность определенного типа данных в отдельной таблице А, ну или написать фунцию/процедуру, которая знает как генерить этот порядок, пусть будет тоже A. С числами видимо все упрощается за счет встроенных средств.
И когда надо найти отсутсвующие данные, то сделать выборку из таблицы/функции А in range [a,b] во временную таблицу B (для простоты, тип поля B должен соответствовать типу поля Field1 "рабочей" таблицы) и затем исключить все записи из B, которым соответствуют записям в "рабочей" таблице по значению в поле Field1. Остаток в таблице B и есть ответ. Я думаю тип поля можно найти at runtime.
Если нет, то можно написать несколько функций/процедур для разных типов данных, и вызывать нужную при работе с определенной таблицой. Можно написать и функцию-конвертор, но по-идее работа с однотипными данными в базе будет быстрее, чем конвертить каждый раз.

KaraNagai · 09.10.2006, 17:06

афтар, ты должен получше определить что значит "чего нету в базе".

magician · 09.10.2006, 17:28

Цитата:

Сообщение от Zindur

Есть Таблица с полем наприер Field1 данными: 1,2,3,5,9,10

Задача (тута самое интересное) :D :
получить результат: 4,6,7,8 - тоесть то что нету в базе

Извращения:
1. выборку зделать по определёным параметрам: тоесть чАво нету между 2 и 5
2. поле может быть разного типа

Ответ на лету - в базе нет всего, кроме того что там есть ..

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

если серьезно - то какими средствами разработки нужно это определить ?

tadpole · 10.10.2006, 07:54

Zindur, SQL - это, по сути, язык для работы с множествами. В твоём примере, если я правильно понимаю, есть некое множество всех возможных чисел и некое подмножество оного, представленное таблицей. Тебе необходимо найти разность полного множества и подмножества, содержащегося в таблице. Если предположить, что всё множество состоит из целых чисел от 1 до 100 включительно, то следующий запрос выдаст искомое:

Код:

SELECT level AS field1
  FROM dual
CONNECT BY level <= 100
MINUS
SELECT field1
  FROM t

где t - твоя таблица-подмножество. Ключевое слово в данном запросе -- MINUS. Ключевой момент -- как определить полное множество. В данном примере я использовал иерархический запрос.

Теперь, если тебе надо найти "чАво нету между 2 и 5", достаточно применить WHERE-clause к вышеприведённой выборке:

Код:

SELECT *
  FROM (
          SELECT level AS field1
            FROM dual
          CONNECT BY level <= 100
          MINUS
          SELECT field1
            FROM t
       )
 WHERE field1 BETWEEN 2 AND 5

Очевидно, что в случае, когда множество является не просто набором целых чисел от X до X + n, тебе надо будет его как-то определить, о чём уже писали другие авторы выше.

P.S. Вышеприведённый примеры написаны на Oracle SQL. Я не знаю, есть ли в "MS SQL, Interbase, Access,....." иерархические запросы и inline views, но уверен, что то же самое можно выразить используя "родной" синтаксис.

YellowMan · 10.10.2006, 09:19

Мой вариант - работает везде на ANSI-SQL базах. Пример от 0 до 99 - но расширить дальше легко.Синтаксис - TSQL.

Код:

create table #tmp (int integer)

insert into #tmp
select 1 union select 2 union select 3 union select 5 union select 9 union select 10 

select f.seq from
(
Select [09].int  + [1090].int*10
           as seq
  from 
(select 1 as int union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) as [09] ,
(select 1 as int union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) as [1090]
) f left join #tmp t on t.int=f.seq
where 
t.int is null and f.seq <11

drop table #tmp

Но Zindur, наиболее правильный ответ ты уже выдал сам

Цитата:

пока вижу ответ только как, создать временную таблицу(со всеми данными) и сравнить эти таблицы.

Причем таблицу лучше создать не временную а постоянную - пригодиться во многих случаях. Заодно имеет смысл в базе всегда держать таблицу с датами на каждый день.
Только этот вариант даст 100% гарантию что код будет работать в любой ANSI-SQL совместимой базе.

Варианты с вычитанием и рекурсией тоже имеют место быть - но ИМХО это не правильно, не дело сервера баз данных заниматься вычитаниями.
Да и тормозить будет на больших объемах.

TYA · 10.10.2006, 09:42

Цитата:

Сообщение от TYA

...и затем исключить все записи из B, которым соответствуют записям в "рабочей" таблице по значению в поле Field1.

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

KaraNagai · 10.10.2006, 09:56

в порядке стеба:

Код:

select 
distinct (select count(1) from tblTable where field > s1.field) * (select count(1) from tblTable) + (select count(1) from tblTable where field > s2.field) 
from tblTable s1 inner join tblTable s2 on 1 = 1 where 
(select count(1) from tblTable where field > s1.field) * (select count(1) from tblTable) + (select count(1) from tblTable where field > s2.field) < 1000 and
not (select count(1) from tblTable where field > s1.field) * (select count(1) from tblTable) + (select count(1) from tblTable where field > s2.field) in
(select field from tblTable)

показывает все значения поля до 1000, которые не присутствуют в этом поле в данной таблице, при условии, что в таблице присутствует хотя бы 34 записи ;)

Zindur · 10.10.2006, 11:36

Кароче решился всётаки на так называемую временую тавлицу(ы), производительность всётаки важнее

да и лехче будет адаптировать под разные базы , хотя нечего тута адаптировать

Просто вчера меня глючило и всё не как не мог решится (искал чтото замудренное) покушал пивка, прочитал "Жёлтого" - отошло.
Да и ни всем клиентам нада ето возможность, (кому надо включитса генератор таблиц - один раз. потерпит один раз пару минут, + с возможностю увеличения - зато потом всё будет красиво и быстренько)
Спасиб всем

KaraNagai · 10.10.2006, 11:43

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

Zindur · 10.10.2006, 11:46

Цитата:

Сообщение от KaraNagai

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

так так оно и будет

просто не знал как правильнее её навАть, поентому и сказал "так называемую временную"

. извиняюсь если неправильно высказался. Удалить всегда успеется

DY · 10.10.2006, 12:56

Вот здесь еще посмотреть можно (если, конечно, это то, что нужно):

Detecting gaps and ranges of gaps in incremental number fields

http://www.sqlserverfaq.com/controls.../CodeLib/9.htm

KaraNagai · 10.10.2006, 13:00

DY, поиск начал и концов гапов - гораздо проще задача по той простой причине, что каждому началу и каждому концу гапа соответствует запись в таблице 1:1.

ерёма · 10.10.2006, 17:26

тоже так можно сбацать табличку с ID (1, 2, 3 и т.д.), и каким нибудь calculated field :

Код:

CREATE TABLE [t1] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[val] [varchar] (50)  NULL ,
	CONSTRAINT [PK_t1] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


WHILE 
	(SELECT COUNT(*)
	FROM t1)< 10
BEGIN
	INSERT INTO [dbo].[t1]([val])
	VALUES (NULL)
	CONTINUE
END

DY · 11.10.2006, 13:44

Дык я и не утверждаю, что это и есть решение. Ссылку привел, скорее, как отправную точку.

Цитата:

Сообщение от KaraNagai

DY, поиск начал и концов гапов - гораздо проще задача по той простой причине, что каждому началу и каждому концу гапа соответствует запись в таблице 1:1.