Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
есть таблица с префиксами:
prefix
0091
00910
00911
00912
009120
009121
009122
00913
00914
00915
00916
00917
00918
00919


надо для префикса 0091 определить является ли диапазон 0091N полным. В данном случае это не так, поскольку подгруппа 00912N не полная

тест:
declare @t table (
	prefix varchar (20) primary key
)


insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('00910')
insert into @t (prefix) values ('00911')
insert into @t (prefix) values ('00912')
insert into @t (prefix) values ('009120')
insert into @t (prefix) values ('009121')
insert into @t (prefix) values ('0091210')
insert into @t (prefix) values ('0091211')
insert into @t (prefix) values ('009122')
insert into @t (prefix) values ('00913')
insert into @t (prefix) values ('00914')
insert into @t (prefix) values ('00915')
insert into @t (prefix) values ('00916')
insert into @t (prefix) values ('00917')
insert into @t (prefix) values ('00918')
insert into @t (prefix) values ('00919')
insert into @t (prefix) values ('009190')
insert into @t (prefix) values ('009191')
insert into @t (prefix) values ('009192')
insert into @t (prefix) values ('009193')
insert into @t (prefix) values ('009194')
insert into @t (prefix) values ('009195')
insert into @t (prefix) values ('009196')
insert into @t (prefix) values ('009197')
insert into @t (prefix) values ('009198')
insert into @t (prefix) values ('009199')

вот так:
declare @prefix varchar (20)
set @prefix = '0091'
select
	count(*) as counter
, left (prefix, len(prefix) - 1 ) 
, len(prefix)
from @t --#preise
where prefix like  @prefix + '_%'
group by len(prefix)
, left (prefix, len(prefix) - 1 )
having count (*) < 10

работает, но если в таблице нет 00919, то тогда и этот блок считается неполным, хотя существуют 009190 - 009199
и ещё, если есть только 0091 и 009190 - 009199, то блок тоже не полный, но я не смог этот случай правильно обработать.
Помогите пожалуйста.

Сервер: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
в режиме совместимости 80
1 фев 12, 18:56    [12014766]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
edyaN
в режиме совместимости 80

Ну раз рекурсия здесь недоступна, то видимо надобно закладываться на некое кол-во подгрупп и селфджойнить запросы с дочерними подгруппами, с подсчетом количеств...
1 фев 12, 19:41    [12014986]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
kDnZP,

ну уровень совместимости можно и поднять. просто пока не было надобности, поэтому оставил как было. типа работает - не трожь
и как бы это выглядело с рекурсиями?
1 фев 12, 19:45    [12015012]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
edyaN, че-то типа такого:

declare @prefix varchar (20)
set @prefix = '0091'

;WITH rcte AS (
SELECT prefix, CAST(NULL AS varchar (20)) parent, 1 lv FROM @t
UNION ALL
SELECT t1.prefix, t2.prefix, t2.lv+1 FROM @t t1
JOIN rcte t2 ON t1.prefix LIKE t2.prefix+'_' AND LEN(t1.prefix)=LEN(t2.prefix)+t2.lv
)
SELECT  DISTINCT @prefix prefix, CASE WHEN MIN(COUNT(*)) OVER ()<10 THEN 'No' ELSE 'Yes' END res
--       ,parent
FROM    rcte
WHERE parent LIKE @prefix+'%'
GROUP BY parent
--ORDER BY parent
1 фев 12, 20:19    [12015138]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
kDnZP
edyaN, че-то типа такого:


Вы меня огорчаете. Нафега тут группировки?

declare @t table (
	prefix varchar (20) primary key
)


insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('00910')
insert into @t (prefix) values ('00911')
insert into @t (prefix) values ('00912')
insert into @t (prefix) values ('009120')
insert into @t (prefix) values ('009121')
insert into @t (prefix) values ('0091210')
insert into @t (prefix) values ('0091211')
insert into @t (prefix) values ('009122')
insert into @t (prefix) values ('00913')
insert into @t (prefix) values ('00914')
insert into @t (prefix) values ('00915')
insert into @t (prefix) values ('00916')
insert into @t (prefix) values ('00917')
insert into @t (prefix) values ('00918')
insert into @t (prefix) values ('00919')
insert into @t (prefix) values ('009190')
insert into @t (prefix) values ('009191')
insert into @t (prefix) values ('009192')
insert into @t (prefix) values ('009193')
insert into @t (prefix) values ('009194')
insert into @t (prefix) values ('009195')
insert into @t (prefix) values ('009196')
insert into @t (prefix) values ('009197')
insert into @t (prefix) values ('009198')
insert into @t (prefix) values ('009199')

declare @n table(n char(1))
insert into @n values ('0')
insert into @n values ('1')
insert into @n values ('2')
insert into @n values ('3')
insert into @n values ('4')
insert into @n values ('5')
insert into @n values ('6')
insert into @n values ('7')
insert into @n values ('8')
insert into @n values ('9')

declare @prefix varchar (20)
set @prefix = '0091'

-- ну я могу написать with, тока вот рекурсии тут не нада
select * from (select * from @t WHERE prefix like  @prefix + '%') T 
WHERE exists(select * from @t X inner join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix)
		and
	  exists(select * from @t X right outer join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix WHERE X.prefix is null)
1 фев 12, 20:27    [12015169]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
aleks2, да я канешна не против, но ваш запрос нерабочий, если я правильно понимаю задачу, т.к. тут полюбому нужно глядеть скокаугодно уровней (ну если ессно не обращать внимание на естественное ограничение в виде varchar(20)). Т.е. нерекурсивное решение только с кучей селфджойнов и никак иначе.
1 фев 12, 20:43    [12015221]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
aleks2,

kDnZP прав.
могут быть и такие случаи, что префикс закрыт блоком, как в примере для 00919 есть полный блок 009190 - 9, при этом сам 00919 может и отсутствовать (но не обязательно). Точно также 009190 может быть блоком от 0091900 до 0091909 и так далее.
Как это сделать без рекурсии я пока не догадался.

kDnZP, твое решение работает и на 80 уровне совместимости, но на некоторых случаях не правильно. Например когда 00919 отсутствует, но есть блок 009190 - 9. Но это уже лучше, чем у меня, буду дальше думать.

Новые варианты всегда велкам :)
1 фев 12, 20:59    [12015270]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
edyaN, честно говоря слегка непонял что не работает...
Попробую уточнить:
1. В таблице префиксов есть только 0091 и больше ничего другого - является ли диапазон полным?
2. В таблице префиксов две записи 0091 и 0091000001 - диапазон для 0091 не полный, правильно?
3. В таблице префиксов есть записи 0091, 0091000000...0091000009 - диапазон для 0091 все равно не полный, правильно?
1 фев 12, 21:08    [12015316]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
На всяк случай еще вариация на тему:
DECLARE @prefix VARCHAR(20)
SET @prefix='0091' ;
WITH    rcte
          AS (
              SELECT    prefix
                       ,CAST(NULL AS VARCHAR(20)) parent
                       ,1 lv
              FROM      @t
              UNION ALL
              SELECT    t1.prefix
                       ,t2.prefix
                       ,t2.lv+1
              FROM      @t t1
              JOIN      rcte t2 ON t1.prefix LIKE t2.prefix+'_'
                                   AND LEN(t1.prefix)=LEN(t2.prefix)+t2.lv
             )
    SELECT TOP 1
            t.prefix
           ,t.res
    FROM    (
             SELECT  DISTINCT
                    @prefix prefix
                   ,CASE WHEN MIN(COUNT(*)) OVER ()<10 THEN 'No'
                         ELSE 'Yes'
                    END res
                   ,0 f
             FROM   rcte
             WHERE  parent LIKE @prefix+'%'
             GROUP BY parent
             UNION ALL
             SELECT @prefix
                   ,'No'
                   ,1
            ) t
    ORDER BY t.f
1 фев 12, 21:23    [12015379]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
edyaN
Member

Откуда: Berlin
Сообщений: 185
kDnZP
edyaN, честно говоря слегка непонял что не работает...
Попробую уточнить:
1. В таблице префиксов есть только 0091 и больше ничего другого - является ли диапазон полным?

да
kDnZP
2. В таблице префиксов две записи 0091 и 0091000001 - диапазон для 0091 не полный, правильно?

да, неполный
kDnZP
3. В таблице префиксов есть записи 0091, 0091000000...0091000009 - диапазон для 0091 все равно не полный, правильно?

да, неполный

вот для этого случая не работает:
insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('00910')
insert into @t (prefix) values ('00911')
insert into @t (prefix) values ('00912')
insert into @t (prefix) values ('00913')
insert into @t (prefix) values ('00914')
insert into @t (prefix) values ('00915')
insert into @t (prefix) values ('00916')
insert into @t (prefix) values ('00917')
insert into @t (prefix) values ('00918')
--insert into @t (prefix) values ('00919') --- этого префикса нет, но есть блок
insert into @t (prefix) values ('009190')
insert into @t (prefix) values ('009191')
insert into @t (prefix) values ('009192')
insert into @t (prefix) values ('009193')
insert into @t (prefix) values ('009194')
insert into @t (prefix) values ('009195')
insert into @t (prefix) values ('009196')
insert into @t (prefix) values ('009197')
insert into @t (prefix) values ('009198')
insert into @t (prefix) values ('009199')
1 фев 12, 21:34    [12015448]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
edyaN
вот для этого случая не работает:
...

Вот еще одна вариация на тему, с учетом
edyaN
kDnZP
edyaN, честно говоря слегка непонял что не работает...
Попробую уточнить:
1. В таблице префиксов есть только 0091 и больше ничего другого - является ли диапазон полным?

да

DECLARE @prefix VARCHAR(20)
SET @prefix='0091'
;WITH    rcte
          AS (
              SELECT    prefix
                       ,CAST(NULL AS VARCHAR(20)) parent
                       ,1 lv
              FROM      @t
              UNION ALL
              SELECT    t1.prefix
                       ,t2.prefix
                       ,t2.lv+1
              FROM      @t t1
              JOIN      rcte t2 ON t1.prefix LIKE t2.prefix+'_'
                                   AND LEN(t1.prefix)=LEN(t2.prefix)+t2.lv
             )
    SELECT TOP 1
            t.prefix
           ,t.res
    FROM    (
             SELECT  DISTINCT
                    @prefix prefix
                   ,CASE WHEN MIN(COUNT(*)) OVER ()<10 THEN 'No'
                         ELSE 'Yes'
                    END res
                   ,0 f
             FROM   rcte
             WHERE  parent LIKE @prefix+'%'
             GROUP BY parent
             UNION ALL
             SELECT @prefix
                   ,'Yes'
                   ,1
             FROM   rcte
             WHERE  prefix LIKE @prefix+'%' AND lv=1
             HAVING COUNT(*)=1
             UNION ALL
             SELECT @prefix
                   ,'No'
                   ,2
            ) t
    ORDER BY t.f
1 фев 12, 21:58    [12015552]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
kDnZP
aleks2, да я канешна не против, но ваш запрос нерабочий, если я правильно понимаю задачу, т.к. тут полюбому нужно глядеть скокаугодно уровней (ну если ессно не обращать внимание на естественное ограничение в виде varchar(20)). Т.е. нерекурсивное решение только с кучей селфджойнов и никак иначе.


КОНКРЕТНЫЙ примерчик нерабочести бы? Хе-хе...
Теоретеги.
2 фев 12, 07:34    [12016557]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
declare @t table (
	prefix varchar (20) primary key
)


insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('00910')
insert into @t (prefix) values ('00911')
insert into @t (prefix) values ('00912')
insert into @t (prefix) values ('009120')
insert into @t (prefix) values ('009121')
insert into @t (prefix) values ('0091210')
insert into @t (prefix) values ('0091211')
insert into @t (prefix) values ('009122')
insert into @t (prefix) values ('00913')
insert into @t (prefix) values ('00914')
insert into @t (prefix) values ('00915')
insert into @t (prefix) values ('00916')
insert into @t (prefix) values ('00917')
insert into @t (prefix) values ('00918')
--insert into @t (prefix) values ('00919')
insert into @t (prefix) values ('009190')
insert into @t (prefix) values ('009191')
insert into @t (prefix) values ('009192')
insert into @t (prefix) values ('009193')
insert into @t (prefix) values ('009194')
insert into @t (prefix) values ('009195')
insert into @t (prefix) values ('009196')
insert into @t (prefix) values ('009197')
insert into @t (prefix) values ('009198')
insert into @t (prefix) values ('009199')

declare @n table(n char(1))
insert into @n values ('0')
insert into @n values ('1')
insert into @n values ('2')
insert into @n values ('3')
insert into @n values ('4')
insert into @n values ('5')
insert into @n values ('6')
insert into @n values ('7')
insert into @n values ('8')
insert into @n values ('9')

declare @prefix varchar (20)
set @prefix = '0091'

-- ну я могу написать with, тока вот рекурсии тут не нада
select * from (select * from @t WHERE prefix like  @prefix + '%') T 
WHERE exists(select * from @t X inner join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix)
		and
	  exists(select * from @t X right outer join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix WHERE X.prefix is null)

select
	count(*) as counter
, left (prefix, len(prefix) - 1 ) 
, len(prefix)
from @t --#preise
where prefix like  @prefix + '_%'
group by len(prefix)
, left (prefix, len(prefix) - 1 )
having count (*) < 10


Хде разница, Зин?!
2 фев 12, 07:39    [12016562]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
aleks2, давайте начнем с того - че оно у вас ваще выводит?

Для всех проверок:
declare @prefix varchar (20)
set @prefix = '0091'


+ 1.
insert into @t (prefix) values ('0091')

Результат:
prefix
--------------------

(строк обработано: 0)

counter
----------- -------------------- -----------

(строк обработано: 0)
Это типа диапазон полный? Ну ладно, допустим.

+ 2.
insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('00910')

Результат:
prefix
--------------------
0091

(строк обработано: 1)

counter
----------- -------------------- -----------
1 0091 5
Так, это предположительно - диапазон не полный. Хорошо.

+ 3.
insert into @t (prefix) values ('0091')
insert into @t (prefix) values ('009190')
insert into @t (prefix) values ('009191')
insert into @t (prefix) values ('009192')
insert into @t (prefix) values ('009193')
insert into @t (prefix) values ('009194')
insert into @t (prefix) values ('009195')
insert into @t (prefix) values ('009196')
insert into @t (prefix) values ('009197')
insert into @t (prefix) values ('009198')
insert into @t (prefix) values ('009199')

Результат:
prefix
--------------------

(строк обработано: 0)

counter
----------- -------------------- -----------

(строк обработано: 0)
Опять полный? А вот и фигушки.
2 фев 12, 10:27    [12017086]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
kDnZP
aleks2, давайте начнем с того - че оно у вас ваще выводит?


Чо выводит?
Мое
0091
00912
009121
Тредстартера
9	0091	5
3 00912 6
2 009121 7

Но поскоку заказа на доп. столбцы не поступало - непонятно в чем претензии.

kDnZP
- 3.

Что-то я не понял? Тредстартеру и разрывы надо учесть неполным?
select * from (select * from @t WHERE prefix like  @prefix + '%') T 
WHERE exists(select * from @t X inner join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix)
		and
	  exists(select * from @t X right outer join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix WHERE X.prefix is null)
UNION ALL
-- пжалуйста, разрывы
select * from (select * from @t WHERE prefix like  @prefix + '%') T 
WHERE exists(select * from @t X inner join (select T.prefix+n prefix FROM (select A.n+B.n n FROM @n A CROSS JOIN @n B) C) Y ON X.prefix=Y.prefix)
		and 
	  not exists(select * from @t X inner join (select T.prefix+n prefix FROM @n) Y ON X.prefix=Y.prefix)


Рекурсия - это абсолютное зло.
2 фев 12, 12:06    [12017957]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
aleks2
Рекурсия - это абсолютное зло.

Серьезно? А наскока быстрее ваш вариант? Просто интересно.

+ ?
INSERT INTO @t(prefix)
SELECT '0091'+LTRIM(STR(v.number)) FROM master..spt_values v WHERE v.type='p' --AND v.number<300

* Вообще-то спор ниочем, я сам не любитель рекурсии и прочих процедурных подходов, но ведь это не повод отказаться от рекурсии вообще.
2 фев 12, 12:53    [12018318]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
aleks2, но если ниче не напутал, то вариант без рекурсии я тоже могу родить :)

--SET STATISTICS TIME ON
SELECT t.prefix FROM (
SELECT *, SUM(t2.c1) OVER () c3
FROM @t t
CROSS APPLY
(SELECT COUNT(*) c1 FROM @t t2 WHERE t.prefix=LEFT(t2.prefix,LEN(t.prefix)) AND LEN(t.prefix)=LEN(t2.prefix)-1) t2
CROSS APPLY
(SELECT COUNT(*) c2 FROM @t t3 WHERE t.prefix=LEFT(t3.prefix,LEN(t.prefix)) AND t.prefix<>t3.prefix) t3
) t
WHERE t.prefix LIKE @prefix+'%' AND NOT c1 IN (0,10) OR (t.prefix = @prefix AND t.c2<>t.c3)
--SET STATISTICS TIME OFF
2 фев 12, 13:50    [12018841]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
А еще лучше так:
--SET STATISTICS TIME ON
SELECT t.prefix FROM (
SELECT *, SUM(t2.c1) OVER () c3
FROM @t t
CROSS APPLY
(
SELECT ISNULL(SUM(t.c1),0) c1, ISNULL(SUM(t.c2),0) c2 FROM (
SELECT CASE WHEN LEN(t.prefix)=LEN(t2.prefix)-1 THEN 1 ELSE 0 END c1,
		CASE WHEN t.prefix<>t2.prefix THEN 1 ELSE 0 END c2
FROM @t t2 WHERE t.prefix=LEFT(t2.prefix,LEN(t.prefix))
) t
) t2
) t
WHERE t.prefix LIKE @prefix+'%' AND NOT c1 IN (0,10) OR (t.prefix = @prefix AND t.c2<>t.c3)
--SET STATISTICS TIME OFF

А вообще, конечно aleks2 молодец, развел меня на переписывания запроса слету))), стоило тока рекурсией пристыдить.
2 фев 12, 14:34    [12019358]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить