Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Bestuzhev
Member

Откуда:
Сообщений: 648
Пример:

есть таблица
id name sum
1 john 100
2 tom 50
3 jack 300
4 tom 450
5 john 150
6 tom 50
7 jack 250

нужно организовать поиск по "блоку", а именно хочу найти id с которого начинается следующая последовательность

jack 300
tom 450
john 150

результатом должно быть например значение id = 3.

Можно ли такое провернуть легко и непринужденно? спасибо.
16 июл 09, 20:41    [7426377]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Элементарно, Ватсон!
SQL2005/2008
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
SELECT 1[id], 'john'[name], 100[sum]
INTO T
UNION ALL SELECT 2, 'tom', 50
UNION ALL SELECT 3, 'jack', 300
UNION ALL SELECT 4, 'tom', 450
UNION ALL SELECT 5, 'john', 150
UNION ALL SELECT 6, 'tom', 50
UNION ALL SELECT 7, 'jack', 250;

DECLARE @P TABLE(id INT, [name] VARCHAR(50), [sum] INT);
INSERT @P(id, [name], [sum])
SELECT 1, 'jack', 300
UNION ALL SELECT 2, 'tom', 450
UNION ALL SELECT 3, 'john', 150;

SELECT T.id
FROM T
WHERE NOT EXISTS(SELECT T.id, T.[name], T.[sum] EXCEPT SELECT T.id-P.id+1, P.[name], P.[sum] FROM @P P);
16 июл 09, 21:31    [7426453]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Прошу прощения. Кажется я был неправ.
Вот так, кажется, правильно:
SQL2005/2008
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
SELECT 1[id], 'john'[name], 100[sum]
INTO T
UNION ALL SELECT 2, 'tom', 50
UNION ALL SELECT 3, 'jack', 300
UNION ALL SELECT 4, 'tom', 450
UNION ALL SELECT 5, 'john', 150
UNION ALL SELECT 6, 'tom', 50
UNION ALL SELECT 7, 'jack', 250;

DECLARE @P TABLE(id INT, [name] VARCHAR(50), [sum] INT);
INSERT @P(id, [name], [sum])
SELECT 1, 'jack', 300
UNION ALL SELECT 2, 'tom', 450
UNION ALL SELECT 3, 'john', 150;

SELECT T.id
FROM T
WHERE NOT EXISTS
(
 SELECT TT.id-T.id+1, TT.[name], TT.[sum]
 FROM T TT
 WHERE TT.id BETWEEN T.id AND T.id+(SELECT COUNT(*) FROM @P)-1
 EXCEPT
 SELECT P.id, P.[name], P.[sum]
 FROM @P P
);
16 июл 09, 21:43    [7426481]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Ещё вариант (в нём id может идти не подряд, оно только задаёт порядок):
SQL2005/2008
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
SELECT 1[id], 'john'[name], 100[sum]
INTO T
UNION ALL SELECT 2, 'tom', 50
UNION ALL SELECT 3, 'jack', 300
UNION ALL SELECT 4, 'tom', 450
UNION ALL SELECT 5, 'john', 150
UNION ALL SELECT 6, 'tom', 50
UNION ALL SELECT 7, 'jack', 250;

DECLARE @P TABLE(id INT, [name] VARCHAR(50), [sum] INT);
INSERT @P(id, [name], [sum])
SELECT 1, 'jack', 300
UNION ALL SELECT 2, 'tom', 450
UNION ALL SELECT 3, 'john', 150;

SELECT T.id
FROM T
WHERE NOT EXISTS
(
 SELECT ROW_NUMBER()OVER(ORDER BY id), [name], [sum] FROM
 (
  SELECT TOP(SELECT COUNT(*) FROM @P) TT.id, TT.[name], TT.[sum]
  FROM T TT
  WHERE TT.id>=T.id
  ORDER BY TT.id
 ) T
 EXCEPT
 SELECT ROW_NUMBER()OVER(ORDER BY P.id), P.[name], P.[sum]
 FROM @P P
);
16 июл 09, 21:59    [7426501]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Вот и я думаю, что надеяться на непрерывность значений id как-то опасно.
USE [master]
GO

DECLARE @Tbl TABLE ([id] INT, [name] VARCHAR(20), [sum] INT)

INSERT INTO @Tbl ([id],[name],[sum])
SELECT 1, 'john', 100 UNION ALL
SELECT 2, 'tom', 50 UNION ALL
SELECT 3, 'jack', 300 UNION ALL
SELECT 4, 'tom', 450 UNION ALL
SELECT 5, 'john', 150 UNION ALL
SELECT 6, 'tom', 50 UNION ALL
SELECT 7, 'jack', 250

SELECT TOP 1
	(SELECT TOP 1 id FROM @Tbl t3 WHERE t3.[id] < t1.id ORDER BY t3.[id] DESC)
FROM @Tbl t1
WHERE  
	NOT EXISTS (
		SELECT DISTINCT
			[name]
		FROM @Tbl
		EXCEPT
		SELECT
			[name]
		FROM @Tbl t2
		WHERE t2.id < t1.id
	)
Вот мои пять копеек. :)
Какой способ оптимальнее предоставим сравнить автору.
16 июл 09, 22:12    [7426525]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Больше не буду перед решением задачи смотреть на чужое решение...
Слишком вышло мудрено. Вот так лучше будет, пожалуй
SELECT TOP 1
	(SELECT TOP 1 id FROM @Tbl t3 WHERE t3.[id] < t1.id ORDER BY t3.[id] DESC)
FROM @Tbl t1
WHERE  
	EXISTS (
		SELECT TOP 1
			*
		FROM @Tbl t2
		WHERE t2.id < t1.id AND t2.[name] = t1.[name]
	)
16 июл 09, 22:17    [7426532]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Ай! Такую опасную неточность допустил. TOP есть, а ORDER bY нету...
SELECT TOP 1
	(SELECT TOP 1 id FROM @Tbl t3 WHERE t3.[id] < t1.id ORDER BY t3.[id] DESC)
FROM @Tbl t1
WHERE  
	EXISTS (
		SELECT TOP 1
			*
		FROM @Tbl t2
		WHERE t2.id < t1.id AND t2.[name] = t1.[name]
	)
ORDER BY t1.id
16 июл 09, 22:20    [7426537]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Недоработку у себя обнаружил:
iap
Ещё вариант (в нём id может идти не подряд, оно только задаёт порядок):
SQL2005/2008
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID(N'T','U') IS NOT NULL DROP TABLE T;
SELECT 1[id], 'john'[name], 100[sum]
INTO T
UNION ALL SELECT 2, 'tom', 50
UNION ALL SELECT 3, 'jack', 300
UNION ALL SELECT 4, 'tom', 450
UNION ALL SELECT 5, 'john', 150
UNION ALL SELECT 6, 'tom', 50
UNION ALL SELECT 7, 'jack', 250;

/*Искомая последовательность записей в порядке возрастания id*/
DECLARE @P TABLE(id INT, [name] VARCHAR(50), [sum] INT);
INSERT @P(id, [name], [sum])
SELECT 1, 'jack', 300
UNION ALL SELECT 2, 'tom', 450
UNION ALL SELECT 3, 'john', 150;

SELECT T.id
FROM T
WHERE NOT EXISTS
(
 SELECT ROW_NUMBER()OVER(ORDER BY id), COUNT(*)OVER(), [name], [sum] FROM
 (
  SELECT TOP(SELECT COUNT(*) FROM @P) TT.id, TT.[name], TT.[sum]
  FROM T TT
  WHERE TT.id>=T.id
  ORDER BY TT.id
 ) T
 EXCEPT
 SELECT ROW_NUMBER()OVER(ORDER BY P.id), COUNT(*)OVER(), P.[name], P.[sum]
 FROM @P P
);
17 июл 09, 08:50    [7427203]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2549
iap, Senay_L - мега варианты!

Но ведь так тоже можно?
IF object_id('tempdb..#t1') IS NOT NULL
  DROP TABLE #t1

IF object_id('tempdb..#t2') IS NOT NULL
  DROP TABLE #t2

CREATE TABLE #t1 (id int, [name] varchar(128), [sum] numeric(19, 4))
CREATE TABLE #t2 (id int, [name] varchar(128), [sum] numeric(19, 4))

INSERT #t1 (id, [name], [sum])
SELECT 11, 'john', 100 UNION ALL
SELECT 2, 'tom', 50 UNION ALL
SELECT 35, 'jack', 300 UNION ALL
SELECT 14, 'tom', 450 UNION ALL
SELECT 15, 'john', 150 UNION ALL
SELECT 26, 'tom', 50 UNION ALL
SELECT 37, 'jack', 250 UNION ALL
SELECT 78, 'jack', 300 UNION ALL
SELECT 99, 'tom', 450 UNION ALL
SELECT 10, 'john', 150 UNION ALL
SELECT 4, 'tom', 400

INSERT #t2 (id, [name], [sum])
SELECT 112, 'jack', 300 UNION ALL
SELECT 22, 'tom', 450 UNION ALL
SELECT 354, 'john', 150

SELECT * FROM #t1
SELECT * FROM #t2

SELECT t1.id FROM #t1 t1
FULL JOIN #t2 t2 ON t2.[name] = t1.[name] AND t2.[sum] = t1.[sum]
WHERE t1.id IS NOT NULL AND t2.id IS NOT NULL
AND t1.[name] = 'jack'

DROP TABLE #t1
DROP TABLE #t2
17 июл 09, 10:42    [7427717]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
PaulYoung
iap, Senay_L - мега варианты!

Но ведь так тоже можно?
IF object_id('tempdb..#t1') IS NOT NULL
  DROP TABLE #t1

IF object_id('tempdb..#t2') IS NOT NULL
  DROP TABLE #t2

CREATE TABLE #t1 (id int, [name] varchar(128), [sum] numeric(19, 4))
CREATE TABLE #t2 (id int, [name] varchar(128), [sum] numeric(19, 4))

INSERT #t1 (id, [name], [sum])
SELECT 11, 'john', 100 UNION ALL
SELECT 2, 'tom', 50 UNION ALL
SELECT 35, 'jack', 300 UNION ALL
SELECT 14, 'tom', 450 UNION ALL
SELECT 15, 'john', 150 UNION ALL
SELECT 26, 'tom', 50 UNION ALL
SELECT 37, 'jack', 250 UNION ALL
SELECT 78, 'jack', 300 UNION ALL
SELECT 99, 'tom', 450 UNION ALL
SELECT 10, 'john', 150 UNION ALL
SELECT 4, 'tom', 400

INSERT #t2 (id, [name], [sum])
SELECT 112, 'jack', 300 UNION ALL
SELECT 22, 'tom', 450 UNION ALL
SELECT 354, 'john', 150

SELECT * FROM #t1
SELECT * FROM #t2

SELECT t1.id FROM #t1 t1
FULL JOIN #t2 t2 ON t2.[name] = t1.[name] AND t2.[sum] = t1.[sum]
WHERE t1.id IS NOT NULL AND t2.id IS NOT NULL
AND t1.[name] = 'jack'

DROP TABLE #t1
DROP TABLE #t2
Нет. Хотя бы потому что не отслеживает порядок записей в таблице и в искомом наборе.
Или вот, например, количество записей в найденной последовательности записей должно совпадать с количесвом искомых?
Что-то не вижу, как Ваш запрос это проверяет.
Может, я, конечно, ошибаюсь...
17 июл 09, 10:56    [7427824]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
PaulYoung
Member

Откуда: Москва
Сообщений: 2549
iap,

Ваша правда. Такой вариант будет работать только при "непрерывных" искомых блоках, что не есть правильно
17 июл 09, 11:06    [7427884]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
PaulYoung
iap, Senay_L - мега варианты!
Ну разве это - мегавариант? Очень по-простецки сделано, можно сказать, топорно. Без всяких модных оконных функций. А ведь работать должно пошустрее на больших таблицах, имхо.
17 июл 09, 11:09    [7427908]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2Senya_L
опс, да?
id name sum
1 john 100
2 jack 300
3 tom 50

4 tom 450
5 john 150
6 tom 50
7 jack 250

для спящего время бодрствования равносильно сну
17 июл 09, 11:12    [7427938]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Алексей2003
2Senya_L
опс, да?
id name sum
1 john 100
2 jack 300
3 tom 50

4 tom 450
5 john 150
6 tom 50
7 jack 250

для спящего время бодрствования равносильно сну
В смысле?
17 июл 09, 11:35    [7428138]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
что значит в смысле?
с теми данными, что я привел, результат есть или нет?

для спящего время бодрствования равносильно сну
17 июл 09, 11:57    [7428301]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Алексей2003
что значит в смысле?
с теми данными, что я привел, результат есть или нет?

для спящего время бодрствования равносильно сну
Есть, = 3.

ЗЫ. Разве так трудно самому проверить? Если есть ошибка - говорите прямо, без обиняков. Я только учусь. :)
17 июл 09, 12:01    [7428326]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Hamlet
Member

Откуда: Armenia
Сообщений: 573
Senya_L
Вот и я думаю, что надеяться на непрерывность значений id как-то опасно.
USE [master]
GO

DECLARE @Tbl TABLE ([id] INT, [name] VARCHAR(20), [sum] INT)

INSERT INTO @Tbl ([id],[name],[sum])
SELECT 1, 'john', 100 UNION ALL
SELECT 2, 'tom', 50 UNION ALL
SELECT 3, 'jack', 300 UNION ALL
SELECT 4, 'tom', 450 UNION ALL
SELECT 5, 'john', 150 UNION ALL
SELECT 6, 'tom', 50 UNION ALL
SELECT 7, 'jack', 250

SELECT TOP 1
	(SELECT TOP 1 id FROM @Tbl t3 WHERE t3.[id] < t1.id ORDER BY t3.[id] DESC)
FROM @Tbl t1
WHERE  
	NOT EXISTS (
		SELECT DISTINCT
			[name]
		FROM @Tbl
		EXCEPT
		SELECT
			[name]
		FROM @Tbl t2
		WHERE t2.id < t1.id
	)
Вот мои пять копеек. :)
Какой способ оптимальнее предоставим сравнить автору.



Сорри......, агде тут определен искомый набор?
17 июл 09, 12:12    [7428414]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2Senya_L

последовательность надо найти вот такую (строгую):
jack 300
tom 450
john 150

при таких данных, ничего не должно выводиться, так?
id name sum
1 john 100
2 jack 300
3 tom 50
4 tom 450
5 john 150
6 tom 50
7 jack 250

но выводится 3. печально..

для спящего время бодрствования равносильно сну
17 июл 09, 12:17    [7428443]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Алексей2003
но выводится 3. печально..
Кто-то из нас неправильно понял задачу. Не исключено, что мы оба. :) Как я понял автору требуется найти не последовательность, а последний ID (если упорядочить), где заканчивается уникальность имен [name]. Хотя... вообще-то вчитался в старттопик. Пожалуй, я неправильно понял задачу. Буду думать.
17 июл 09, 12:42    [7428614]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Bestuzhev
Member

Откуда:
Сообщений: 648
если кто знаком с функцией pos в дельфях то как раз здесь мне нужна аналогия для записей. т.е.
функция pos('ads','wtwttasdgghjgh') возвращает номер символа с которого начинается вхождение подстроки 'ads' в строке поиска 'wtwttasdgghjgh' т.е. результат будет 6.
так и здесь, мне надо список всех (или хотя бы одного) id с которых начинается искомая последовательность записей.
Надеюсь понятно объяснил.

з.ы. пока пытаюсь сделать через опу
17 июл 09, 16:38    [7430309]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2Bestuzhev
вроде уже предлагали несколько решений тут. или вам нужно сочувствие? могу платочек дать.

для спящего время бодрствования равносильно сну
17 июл 09, 16:42    [7430336]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Konst_One
Member

Откуда:
Сообщений: 11514
что значит:
автор
id с которых начинается искомая последовательность записей


отсортировать просто по id и указать фильтр по name?

select top 1 id from table1 where [name] in ('jack','tom','john') order by id
17 июл 09, 16:43    [7430341]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
мде, для 2000 вот
declare @t table (id int, name varchar(10), sum int)
insert into @t
 select 1, 'john', 100 union all
 select 2, 'tom', 50 union all
 select 3, 'jack', 300 union all
 select 4, 'tom', 450 union all
 select 5, 'john', 150 union all
 select 6, 'tom', 50 union all
 select 7, 'jack', 300

declare @p table (id int, name varchar(10), sum int)
insert into @p
 select 1, 'jack', 300 union all
 select 2, 'tom', 450 union all
 select 3, 'john', 150 union all
 select 4, 'tom', 50
select min(t.id)
 from
  (select t.id, t.name, t.sum, tt.id ttid, tt.name ttname, tt.sum ttsum
    from @t t
     join @t tt on (select top 1 id from @t ttt where ttt.id > t.id order by id) = tt.id
  ) t
   join 
  (select p.id, p.name, p.sum, pp.id ppid, pp.name ppname, pp.sum ppsum
    from @p p
     join @p pp on (select top 1 id from @p ppp where ppp.id > p.id order by id) = pp.id
  ) p
   on t.name = p.name and t.sum = p.sum and t.ttname = p.ppname and t.ttsum = p.ppsum
 having min(p.id) = (select min(id) from @p) and max(ppid) = (select max(id) from @p)

для спящего время бодрствования равносильно сну
17 июл 09, 17:31    [7430722]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Bestuzhev
Member

Откуда:
Сообщений: 648
Алексей2003
(сразу видно москвичей, хе-хе давно ли?)
ладно, шучу.
читайте не только свои посты, уважаемый.
люди спросили - я ответил.
уже решил и гораздо проще. без юнионов и прочих утяжелителей.
всем спасибо.
17 июл 09, 17:56    [7430889]     Ответить | Цитировать Сообщить модератору
 Re: Как найти в таблице не просто несколько записей а определенную последовательность записей?  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
2Bestuzhev
без row_number?

для спящего время бодрствования равносильно сну
17 июл 09, 18:01    [7430922]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить