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

Откуда: Лужки
Сообщений: 5308
vehfl
Roman S. Golubin
Аналогично для 5-й задачи:

/*Получение строк "мама мыла раму" и "Карл у Клары украл кораллы"*/
select S = (select S 'data()'
            from T
            where G = t.G
            order by ID
            for xml path (''))
from T t
group by G
order by G

а это только у меня этот запрос возвращает
автор

Карл мама мыла раму у Клары украл кораллы
Карл мама мыла раму у Клары украл кораллы

у вас сервер игнорит регистр. измените псевдоним таблицы Т
9 сен 09, 18:36    [7638889]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
borin
Member

Откуда: angarsk/msk
Сообщений: 677
блин, чувствую, что задачу 6 можно приспособить к подсчету суммы цифр,
и для отдельного случая типа '12345' сделал, но вот как к целой таблице чего-то типа '12345' применить сей волшебный with, ума не приложу.
похоже, только свою функцию sum_of_cifers создавать, а уж внутри нее with юзать
10 мар 10, 09:59    [8453377]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
borin
блин, чувствую, что задачу 6 можно приспособить к подсчету суммы цифр,
и для отдельного случая типа '12345' сделал, но вот как к целой таблице чего-то типа '12345' применить сей волшебный with, ума не приложу.
похоже, только свою функцию sum_of_cifers создавать, а уж внутри нее with юзать
Ну зачем же цикликами по буковкам бегать для каждой строки таблицы?

Лучьше уж так:

-- Таблица с номерами, к-во записей - до максимального размера строки.
declare @num table(i int not null primary key)
insert @num values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19)
-- Таблица с цифрами 0-9
declare @dig table(i int not null primary key)
insert @dig values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) 

declare @T table(id int not null primary key, val varchar(100) not null)
insert @T values (1, '123'),(2, '999'),(3, '123456'),(4, '111111')

select	t.id,
		(
			select SUM(d.i)
			from @num as n
				join @dig as d
					on SUBSTRING(t.val, n.i, 1) = convert(varchar, d.i)
		) as [Искомая сумма]
from @T as t
Если поиграть с типами данных, немножко модифицировать, то будет быстро.
10 мар 10, 12:21    [8454606]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
alexeyvg
Если поиграть с типами данных, немножко модифицировать, то будет быстро.

Модифицировал, тест на 10000 записей - 570 мс CPU:
declare @num table(i int not null primary key)
insert @num values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19),(20), (21), (22), (23), (24), (25), (26), (27), (28), (29),(30), (31), (32), (33), (34), (35), (36), (37), (38), (39),(40), (41), (42), (43), (44), (45), (46), (47), (48), (49)
declare @dig table(i int not null, s varchar(1) not null primary key)
insert @dig values (0, '0'), (1, '1'), (2, '2'), (3, '3'), (4, '4'), (5, '5'), (6, '6'), (7, '7'), (8, '8'), (9, '9') 

declare @T table(id int not null primary key, val varchar(100) not null)
insert @T
select	top 10000
		ROW_NUMBER() over (order by c1.object_id),
		replace(CONVERT(varchar(100), newid()), '-', '')
from sys.columns as c1
	cross join sys.columns as c2

declare @R table(id int not null primary key, s int)

insert @R
select	t.id,
		(
			select SUM(d.i)
			from @num as n
				join @dig as d
					on SUBSTRING(t.val, n.i, 1) = d.s
		) as [Искомая сумма]
from @T as t
10 мар 10, 12:38    [8454746]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
разъясните как пронумеровать выборку с помощью СТЕ ?


автор
--Нумерация строк
DECLARE @Cardinality INT, @Seed INT, @Increment INT;
SELECT @Cardinality=100, @Seed=5, @Increment=3;

CREATE TABLE T1 (A INT, B INT, C INT);

WITH T(ID,N, NN) AS
(
SELECT 1, @Seed, 0 WHERE @Cardinality>=1
UNION ALL
SELECT ID+1, N+@Increment, NN+@Increment-1 FROM T WHERE ID<@Cardinality
)
INSERT INTO T1 SELECT ID AS A, N AS B, NN AS C FROM T ORDER BY ID
OPTION (MAXRECURSION 0);

--1 Вариант
SELECT TOP(@Cardinality)
ROW_NUMBER() OVER (ORDER BY B DESC,C) AS num,
A, B, C FROM T1
WHERE B BETWEEN 20 AND 50;

--2 Вариант
DECLARE @From INT, @To INT, @Step INT;
SELECT @From = 1, @To = 10, @Step=1;

WITH CTE (N, A, B, C) AS (
SELECT @From, A, B, C
FROM T1
WHERE @From<=@To AND T1.B BETWEEN 20 AND 50
UNION ALL
SELECT N+@Step, CTE.A, CTE.B, CTE.C
FROM CTE JOIN T1 ON CTE.A=T1.A AND CTE.B=T1.B AND CTE.C=T1.C AND T1.B BETWEEN 20 AND 50
WHERE N<=@To
)
SELECT N, A, B, C
FROM CTE
where N<=@To AND B BETWEEN 20 AND 50
OPTION (MAXRECURSION 0);

--
DROP TABLE T1;


Без CTE все работает, а с CTE почему-то получаю декартово произведение.
Объясните где ошибка и как ее исправить?
5 дек 10, 14:50    [9888605]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
разъясните как пронумеровать выборку с помощью СТЕ ?

--Нумерация строк
DECLARE @Cardinality INT, @Seed INT, @Increment INT;
SELECT @Cardinality=100, @Seed=5, @Increment=3;

CREATE TABLE T1 (A INT, B INT, C INT);

WITH T(ID,N, NN) AS
(
 SELECT 1, @Seed, 0 WHERE @Cardinality>=1
 UNION ALL
 SELECT ID+1, N+@Increment, NN+@Increment-1 FROM T WHERE ID<@Cardinality
)
INSERT INTO T1 SELECT ID AS A, N AS B, NN AS C FROM T ORDER BY ID
OPTION (MAXRECURSION 0);

--1 Вариант
SELECT TOP(@Cardinality)
ROW_NUMBER() OVER (ORDER BY B DESC,C) AS num, 
A, B, C FROM T1
WHERE B BETWEEN 20 AND 50;

--2 Вариант
DECLARE @From INT, @To INT, @Step INT;
SELECT @From = 1, @To = 10, @Step=1;

WITH CTE (N, A, B, C) AS (
    SELECT @From, A, B, C
    FROM T1 
	WHERE @From<=@To AND T1.B BETWEEN 20 AND 50 
	UNION ALL
    SELECT N+@Step, CTE.A, CTE.B, CTE.C
    FROM CTE JOIN T1 ON CTE.A=T1.A AND CTE.B=T1.B AND CTE.C=T1.C AND T1.B BETWEEN 20 AND 50 
	WHERE N<=@To
)
SELECT N, A, B, C 
FROM CTE 
where N<=@To AND B BETWEEN 20 AND 50 
OPTION (MAXRECURSION 0);

--
DROP TABLE T1;

Без CTE все работает (вариант 1), а с CTE (вариант 2) почему-то получаю декартово произведение.
Объясните где ошибка и как ее исправить?
5 дек 10, 14:53    [9888608]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31435
test7
Без CTE все работает (вариант 1), а с CTE (вариант 2) почему-то получаю декартово произведение.
Объясните где ошибка и как ее исправить?
Вы в первом-же шаге
SELECT @From, A, B, C
    FROM T1 
	WHERE @From<=@To AND T1.B BETWEEN 20 AND 50 
выбираете все записи из таблицы T1

Вообще непонятно, зачем нумерацию заменять на CTE
5 дек 10, 15:02    [9888617]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
alexeyvg
test7
Без CTE все работает (вариант 1), а с CTE (вариант 2) почему-то получаю декартово произведение.
Объясните где ошибка и как ее исправить?
Вы в первом-же шаге
SELECT @From, A, B, C
    FROM T1 
	WHERE @From<=@To AND T1.B BETWEEN 20 AND 50 
выбираете все записи из таблицы T1

Вообще непонятно, зачем нумерацию заменять на CTE


Я это заметил, но никак не получается получить в запросе до UNION ALL одну строку,
Не могу понять как правильно прописать условие.

PS: Эту задачу решаю, чтобы лучше понять как работает CTE.
5 дек 10, 15:31    [9888641]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
мне нужно получить с помощью CTE такой же результат как и для запроса (вариант 1),
num	A	B	C
1	16	50	30
2	15	47	28
3	14	44	26
4	13	41	24
5	12	38	22
6	11	35	20
7	10	32	18
8	9	29	16
9	8	26	14
10	7	23	12
11	6	20	10

но никак не могу разобраться как его получить.
Получаю только декартово произведение:
1	6	20	10
1	7	23	12
1	8	26	14
1	9	29	16
1	10	32	18
1	11	35	20
1	12	38	22
1	13	41	24
1	14	44	26
1	15	47	28
1	16	50	30
2	16	50	30
3	16	50	30
4	16	50	30
5	16	50	30
6	16	50	30
7	16	50	30
8	16	50	30
9	16	50	30
10	16	50	30

....

2	6	20	10
3	6	20	10
4	6	20	10
5	6	20	10
6	6	20	10
7	6	20	10
8	6	20	10
9	6	20	10
10	6	20	10

или если указываю
WITH CTE (N, A, B, C) AS (
    SELECT @From, A, B, C
    FROM T1 
	--WHERE @From<=@To AND B BETWEEN 20 AND 50 
        WHERE B=20
	UNION ALL
    SELECT N+@Step, CTE.A, CTE.B, CTE.C
    FROM CTE JOIN T1 ON CTE.A=T1.A AND CTE.B=T1.B AND CTE.C=T1.C AND T1.B BETWEEN 20 AND 50 
	WHERE N<=@To
)
SELECT N, A, B, C 
FROM CTE 
where N<=@To AND B BETWEEN 20 AND 50 
получаю такой результат
N	A	B	C
1	6	20	10
2	6	20	10
3	6	20	10
4	6	20	10
5	6	20	10
6	6	20	10
7	6	20	10
8	6	20	10
9	6	20	10
10	6	20	10
и это опять же не то что нужно.

помогите, господа знатоки, как пронумеровать строки с помощью CTE для данной выборки из таблицы T1?
5 дек 10, 15:56    [9888686]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
иными словами как написать с помощью CTE такую же конструкцию?
--1 Вариант
SELECT TOP(@Cardinality)
ROW_NUMBER() OVER (ORDER BY B DESC,C) AS num, 
A, B, C FROM T1
WHERE B BETWEEN 20 AND 50;
5 дек 10, 16:00    [9888692]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
_djХомяГ
Guest
Так что ли
with cte 
as
(
select 1 as num,A, B, C FROM T1
where b=50
union all
select num+1,t1.A,t1.B,t1.C FROM T1 join cte
on  t1.A=cte.A-1
where t1.b>=20
)
select * from cte
5 дек 10, 16:23    [9888744]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
kinglion
Member

Откуда:
Сообщений: 16
Подскажите плиз. что будет работать быстрее на больших объемах данных (более 300млн. записей)

select top(1000)
row_number() over (ORDER BY id) as n, 
field1, field2, field3, field2 from table1

или аналогичная конструкция с использованием CTE ?

with cte(n) as (select 1 union all select n+1 from cte where n<300000000)
select * from cte where n<=1000 order by n
option (maxrecursion 0)
5 дек 10, 16:34    [9888773]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
test7
Member

Откуда:
Сообщений: 6
_djХомяГ,

спасибо, похоже, что оно
5 дек 10, 16:37    [9888777]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
kinglion
Member

Откуда:
Сообщений: 16
сравнивая планы запросов пришел к выводу, что при больших объемах данных (несколько миллионов строк) использование CTE для Задачи №1. Генерирование последовательности чисел, дат и т.п. - неэффективно.

Эффективно для этих целей использовать row_number() over (ORDER BY id).

Если я не прав, поправьте меня.
5 дек 10, 17:21    [9888929]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
kinglion
сравнивая планы запросов пришел к выводу, что при больших объемах данных (несколько миллионов строк) использование CTE для Задачи №1. Генерирование последовательности чисел, дат и т.п. - неэффективно.

Эффективно для этих целей использовать row_number() over (ORDER BY id).

Если я не прав, поправьте меня.
Прав. Но не совсем.
Ещё лучше создать постоянную таблицу с последовательными целыми числами
и один раз заполнить её любым способом. В дальнейшем джойниться с ней по необходимости.
5 дек 10, 18:00    [9889021]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Gator
Member

Откуда: Москва
Сообщений: 14981
iap> В дальнейшем джойниться с ней по необходимости.
Не понял, каким образом джойниться?

Допустим, есть SeqentialNumberTable и таблица Адресов.
Надо пронумеровать выборку адресов .
С row_number() over (ORDER BY id) все понятно,
а как (чем) джойниться к SeqentialNumberTable?
6 дек 10, 03:34    [9890327]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Gator
iap> В дальнейшем джойниться с ней по необходимости.
Не понял, каким образом джойниться?

Допустим, есть SeqentialNumberTable и таблица Адресов.
Надо пронумеровать выборку адресов .
С row_number() over (ORDER BY id) все понятно,
а как (чем) джойниться к SeqentialNumberTable?
Вы спрашивали про "генерирование последовательности чисел".
Я написал именно про это.
Что касается нумерации строк в запросе, то CTE тут как корове седло.
Конечно же, используете ранжирующие функции.
6 дек 10, 09:35    [9890694]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Вопрос_по_цту
Guest
А Как такое на cte сделать?

declare @a table  (ID int primary key, sm int not null)
declare @a2 as table(ID int identity primary key, IDs varchar(100), sm int not null default 0 )

    

insert into @a(ID, sm)
values (1,5),(2,10),(3,5)




declare @ID int, @IDs varchar(200), @sm int
select @ID = ID, @sm = sm from @a where ID = (select min(ID) from @a) 
while @@rowcount = 1
begin

   insert into @a2(IDs, sm) 
   --output inserted.*
        select IDs, sm
        from (       
            select 0 as ID, cast(@ID as varchar(10)) as IDs, @sm as sm
            union            
            select ID, IDs + ',' + cast(@ID as varchar(10)), sm + @sm
            from @a2 ) t
        
    select @ID = ID, @sm = sm from @a where ID = (select min(ID) from @a where ID > @ID) 
end

select * from @a2 order by ID desc

29 апр 11, 18:54    [10589091]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
iljy
Member

Откуда:
Сообщений: 8711
Вопрос_по_цту,

;with cte as
(
	select id maxid, cast(id as varchar(1000)) IDs, sm from @a
		union all
	select t.ID, cast(IDs + ',' + CAST(t.ID as varchar) as varchar(1000)), c.sm+t.sm
	from cte c join @a t on t.ID > c.maxid
)
select IDs, sm from cte
29 апр 11, 21:06    [10589455]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
Я, конечно, извиняюсь за гробокопательство, но не получилось промолчать. :)
iap
Рассмотрим некоторые другие популярные задачи.
При наличии натурального ряда достаточной длины все эти задачи значительно более эффективно решаются на SQL без использования CTE. Ну разве что для конкатенации строк - еще более-менее адекватный инструмент.
Сама же суть рекурсии говорит о том, что необходимо иметь доступ к значению полученному на предыдущем шаге. Вот классическим примером применения CTE считаю генерацию ряда фибоначчи.
with x (val1, val2, depth) as
(select 1, 1, 1
 union all
 select val1 + val2, val1, depth + 1 from x where depth < 10)
select depth level, val2 fib
from x;
level       fib
----------- -----------
1           1
2           1
3           2
4           3
5           5
6           8
7           13
8           21
9           34
10          55

(10 row(s) affected)
Тут, действительно, без CTE никак, хоть есть уже сгенерированный натуральный ряд, хоть нет.
17 авг 11, 02:56    [11130143]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
dbms_photoshop, есть целый ряд вещей, в которых трудно обойтись без рекурсии (при условии, конечно, что стоит задача решить все одним запросом), как пример: агрегирующее перемножение, либо число в разных степенях. Традиционные способы через функции работают с float и страдают малой точностью.

Но таки да, согласен. Рекурсивного CTE стоит избегать всеми способами, если есть возможность. ИМХО даже циклы и то лучше. Но опять же - цикл во вьюху не запхать. Для конкатенации, как на мой взгляд - лучше всего использовать FOR XML.
17 авг 11, 03:10    [11130165]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
kDnZP
dbms_photoshop, есть целый ряд вещей, в которых трудно обойтись без рекурсии (при условии, конечно, что стоит задача решить все одним запросом), как пример: агрегирующее перемножение, либо число в разных степенях. Традиционные способы через функции работают с float и страдают малой точностью.
Нарастающее перемножение:
WITH t
     AS (SELECT 1 id UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5)
SELECT id,
       Exp(Sum(Log(id)) OVER (/* здесь же вроде с Denali уже можно указывать сортировку? */)) summ
FROM   t
Число в разных степенях:
SELECT NUMBER + 1           rn,
       Power(2, NUMBER + 1) pow
FROM   MASTER..spt_values
WHERE  TYPE = 'P'
       AND NUMBER < 10
Не оно?
kDnZP
Но таки да, согласен. Рекурсивного CTE стоит избегать всеми способами, если есть возможность. ИМХО даже циклы и то лучше. Но опять же - цикл во вьюху не запхать. Для конкатенации, как на мой взгляд - лучше всего использовать FOR XML.
Ну с FOR XML, к примеру, весьма компактно решается и задача перевода в другую систему счисления:
WITH t
     AS (SELECT 1876557579 n,
                16         radix
         FROM   dual),
     tmp
     AS (SELECT Floor(n / Power(radix, rn - 1)) % radix d,
                radix,
                rn
         FROM   t
                CROSS APPLY (SELECT NUMBER + 1 rn
                             FROM   MASTER..spt_values
                             WHERE  TYPE = 'P'
                                    AND Power(16, NUMBER) < n
                                    AND NUMBER <= 7) p)
SELECT (SELECT CASE
                 WHEN d > 9 THEN Char(Ascii('A') + d%radix - 10)
                 ELSE Cast(d AS VARCHAR(1))
               END
        FROM   tmp
        ORDER  BY rn DESC
        FOR XML PATH('')) result
Но неужели для таких целей как конкатенация или нарастающее умножение не выгоднее написать свой агрегат используя CREATE AGGREGATE? Кто-нибудь производительность сравнивал?
17 авг 11, 04:11    [11130189]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
dbms_photoshop, невнимательно читаете.
kDnZP
Традиционные способы через функции работают с float и страдают малой точностью.

Это касается Exp,Log,Power.

Что же касается CLR, то в принципе я точно не могу сказать, что будет медленнее CLR или рекурсия. Но предполагаю что будут примерно наравне. С другой стороны я избегаю использования как рекурсий, так и CLR, если такая возможность есть))).

В то же время - работа с FOR XML будет значительно быстрее рекурсии, такие тесты проводились.
17 авг 11, 08:59    [11130462]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
tt1
Guest
dbms_photoshop
Я, конечно, извиняюсь за гробокопательство, но не получилось промолчать. :)
iap
Рассмотрим некоторые другие популярные задачи.
При наличии натурального ряда достаточной длины все эти задачи значительно более эффективно решаются на SQL без использования CTE. Ну разве что для конкатенации строк - еще более-менее адекватный инструмент.
Сама же суть рекурсии говорит о том, что необходимо иметь доступ к значению полученному на предыдущем шаге. Вот классическим примером применения CTE считаю генерацию ряда фибоначчи.
with x (val1, val2, depth) as
(select 1, 1, 1
 union all
 select val1 + val2, val1, depth + 1 from x where depth < 10)
select depth level, val2 fib
from x;
level       fib
----------- -----------
1           1
2           1
3           2
4           3
5           5
6           8
7           13
8           21
9           34
10          55

(10 row(s) affected)
Тут, действительно, без CTE никак, хоть есть уже сгенерированный натуральный ряд, хоть нет.




--генерация последовательности 1..i
with x (i) as
(select 1
 union all
 select i + 1 from x where i <= 54)
select i
into #t1
from x;

--расчет фибонначи
exec sp_drop_table '#t2' 
select i, cast (round((power((1 + SQRT(5))/2, i) - power((1 - SQRT(5))/2, i))/SQRT(5),0) as bigint) vl
  into #t2
  from #t1 
  
--проверка результата  
  select *
    from #t2 a
    join #t2 b
      on a.i = b.i + 1
    join #t2 c
      on a.i = c.i + 2  
 where b.vl + c.vl <> a.vl
7 фев 12, 15:43    [12047780]     Ответить | Цитировать Сообщить модератору
 Re: SQL2005: популярные задачи форума и CTE  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Тут попросили меня решить одну достаточно тривиальную задачку, в ходе решения которой я наткнулся на один затык, связанный с использованием параметризованных рекурсивных CTE внутри cross apply. Задачка такая: перевернуть строки внутри выборки. Процедурные конструкции использовать, естественно, запрещено, REVERSE - тоже. Оговорюсь сразу: вопрос представляет для меня чисто теоретический характер, на практике требуемое уже есть в готовом виде, да и реализовать его вполне можно самому, выйдя за рамки постановки задачи.
Итак, всё вроде бы просто:
select v.n, f.s from (
  values (1, 'ABC'),(2, 'DEF'),(3, 'FGH')
) v(n,s)
cross apply (
  select (
    select substring(v.s, len(v.s) - m.number, 1) from master..spt_values m
    where m.type='P' and m.number between 0 and len(v.s)-1
    for xml path(''), type
  ).value('.','varchar(max)')
) f(s)

Усложняем задачу: отказываемся от master..spt_values, и последовательность для substring генерим с помощью CTE:
;with seq as (
  select 0 n
  union all
  select 1+n from seq
)
select s.n,f.s from (
  values (1, 'ABC'),(2, 'DEF'),(3, 'GHI')
) s(n, s)
cross apply (
  select (
    select SUBSTRING(s.s, len(s.s)-q.n,1) from seq q where q.n<LEN(s.s) for xml path(''), type
  ).value('.','varchar(max)')
) f(s)
option (maxrecursion 3)

при попытке выполнения выбрасывает
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.
- сервер пытается сначала сгенерить весь набор, формируемый CTE, и при отсутствии ограничений в объявлении вылетает по дефолтному значению MAXRECURSION.
Если попробовать сделать так:
;with src as (
  select * from (
    values (1, 'ABC'),(2, 'DEF'),(3, 'GHI')
  ) s(n, s)
),
seq as (
  select 0 n
  union all
  select 1+n from seq where n<(select max(len(s)) from src)
)
select s.n,f.s from src s
cross apply (
  select (
    select substring(s.s, len(s.s)-q.n,1) from seq q where q.n<len(s.s) for xml path(''), type
  ).value('.','varchar(max)')
) f(s)
option (maxrecursion 3)

то получаем вот такое:
Msg 467, Level 16, State 1, Line 6
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'seq'.
Можно, в конце концов, сделать вот так:
;with src as (
  select * from (
    values (1, 'ABC'),(2, 'DEF'),(3, 'GHI')
  ) s(n, s)
),
maxlen as (
  select max(len(s)) mx from src
),
seq as (
  select 0 n
  union all
  select 1+n from seq where n<(select mx from maxlen)
)
select s.n,f.s from src s
cross apply (
  select (
    select substring(s.s, len(s.s)-q.n,1) from seq q where q.n<len(s.s) for xml path(''), type
  ).value('.','varchar(max)')
) f(s)
option (maxrecursion 3)

и всё будет работать - но не нравится подгонка рекурсивного CTE под один параметр, который при длине 99% строк в выборке в 1 символ, и 1% строк в 4000 символов будет давать большой оверхед по размеру последовательности.
Разумеется, можно вынести CTE, формирующее последовательность, и то, что внутри cross apply, в отдельную функцию - но, как было написано выше, интересно выяснить, возможно ли реализовать именно вышеописанное - т.е. параметризовывать CTE внутри cross apply для каждой записи выборки.
26 фев 12, 12:14    [12152558]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить