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

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
Есть две таблицы: письма и тесты заголовков писем.
Если в таблице с тестами, скажем, 3 строки, то при добавлении 9 строк в таблицу писем необходимо, чтобы результат был таким:
Заголовок письма Текст письма
Тест заголовка 1 Текст письма
Тест заголовка 1 Текст письма
Тест заголовка 1 Текст письма
Тест заголовка 2 Текст письма
Тест заголовка 2 Текст письма
Тест заголовка 2 Текст письма
Тест заголовка 3 Текст письма
Тест заголовка 3 Текст письма
Тест заголовка 3 Текст письма

Подскажите, пожалуйста, как эту задачу можно решить с помощью select или update, если это возможно. Я пока кроме курсора выходов не вижу.
3 янв 18, 16:40    [21080272]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
xenix
Guest
Vsevolod V,
автор
SELECT L.*, H.*
FROM LETTERS L
CROSS JOIN HEADERS H

так?
3 янв 18, 16:44    [21080278]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
xenix,

Спасибо. Нет, не так. В вашем варианте будет 1 получателю отправлено 3 письма с разными заголовками. И в общей сложности будет 27 писем, а не 9

Заголовок письмаТекст письмаEmail
Тест заголовка 1Текст письмаburkov@mail.ru
Тест заголовка 1Текст письмаfoma@yandex.ru
Тест заголовка 1Текст письмаgurch@mail.ru
Тест заголовка 2Текст письмаborya@gmail.com
Тест заголовка 2Текст письмаs.krupko@mail.ru
Тест заголовка 2Текст письмаmashhod@yandex.ru
Тест заголовка 3Текст письмаvorobeva.2017@gmail.com
Тест заголовка 3Текст письмаvershinin@uralsibstal.com
Тест заголовка 3Текст письмаganediy@yandex.ru
3 янв 18, 17:00    [21080309]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
ntile,
Guest
declare @z table (id int identity, txt varchar(100))
declare @t table (id int identity, txt varchar(100))

insert into @z (txt)
values ('Тест заголовка 1'), ('Тест заголовка 2'), ('Тест заголовка 3')

insert into @t (txt)
values ('Текст письма 1'), ('Текст письма 2'), ('Текст письма 3'),('Текст письма 4'), ('Текст письма 5'), ('Текст письма 6'),('Текст письма 7') --, ('Текст письма 8'), ('Текст письма 9')

declare @cnt int = (select count(*) from @z)

--	
;with b as
	(select *, row_number()over(order by id) as n from @z),
c as
	(select *, ntile(@cnt)over(order by id) as n from @t)		 

select
	b.n
	,b.txt 
	,c.txt
from b inner join c on b.n=c.n


ntxttxt
1Тест заголовка 1Текст письма 1
1Тест заголовка 1Текст письма 2
1Тест заголовка 1Текст письма 3
2Тест заголовка 2Текст письма 4
2Тест заголовка 2Текст письма 5
3Тест заголовка 3Текст письма 6
3Тест заголовка 3Текст письма 7
3 янв 18, 17:03    [21080313]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
ntile,

Спасибо! Похоже на то что надо. Буду проверять
3 янв 18, 17:09    [21080322]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
ntile,

В предложенном вами решении используется переменная @cnt, которая вроде как становится прецедентом для появления курсора. Дело в том, что задача усложнена тем, что в таблице писем разные рассылки с разными наборами тестируемых значений. То есть количество тестируемых значений необходимо рассчитывать в запросе, а не выводить в переменную.

Я попробовал это сделать, но столкнулся с ошибкой: "Ссылка на столбец "mt_id" недопустима в аргументе для функции NTILE. Допускаются только ссылки на столбцы внешней области или изолированные выражения и вложенные запросы."


;with 
b as (select *, row_number()over(order by mtv_id, mt_id) as n from tMailTestValues), 
mails as (select *, ntile((select count(*) from tMailTestValues where mt_id = mt.mt_id))over(order by mt_id) as n from v_A_TasksMail tm join 
tMailTest mt on tm.m_id_template = mt.m_id)		 

select
	b.n,
	mails.n
from b inner join mails on b.n = mails.n
3 янв 18, 18:05    [21080375]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
ntile,
Guest
Vsevolod V
ntile,

В предложенном вами решении используется переменная @cnt, которая вроде как становится прецедентом для появления курсора. Дело в том, что задача усложнена тем, что в таблице писем разные рассылки с разными наборами тестируемых значений. То есть количество тестируемых значений необходимо рассчитывать в запросе, а не выводить в переменную.

Я попробовал это сделать, но столкнулся с ошибкой: "Ссылка на столбец "mt_id" недопустима в аргументе для функции NTILE. Допускаются только ссылки на столбцы внешней области или изолированные выражения и вложенные запросы."


;with 
b as (select *, row_number()over(order by mtv_id, mt_id) as n from tMailTestValues), 
mails as (select *, ntile((select count(*) from tMailTestValues where mt_id = mt.mt_id))over(order by mt_id) as n from v_A_TasksMail tm join 
tMailTest mt on tm.m_id_template = mt.m_id)		 

select
	b.n,
	mails.n
from b inner join mails on b.n = mails.n
Зачем это where в подзапросе ?
Вот так "работает"

;with b as
	(select *, row_number()over(order by id) as n from @z),
c as
	(select *, ntile((select count(*) from @z))over(order by id) as n from @t)		 

select
	b.n
	,b.txt 
	,c.txt
from b inner join c on b.n=c.n
3 янв 18, 18:20    [21080397]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
Давайте лучше заново более полно сформулирую задачу со своей структурой данных.

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

m_id_parent - идентификатор рассылки

m_id m_addressFrom m_addressTo m_subject m_text m_nameFrom m_id_parent
1 'vova@yandex.ru''vasya@mail.ru''Поздравляем Вас с Новым Годом!''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
2 'vova@yandex.ru''ivan@mail.ru''Поздравляем Вас с Новым Годом!''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
3 'vova@yandex.ru''petr@mail.ru''Поздравляем Вас с Новым Годом!''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
4 'gena@yandex.ru''masha@mail.ru''Услуги клининга в Санкт-Петербурге''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
5 'gena@yandex.ru''dasha@mail.ru''Услуги клининга в Санкт-Петербурге''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
6 'gena@yandex.ru''zinaida@mail.ru''Услуги клининга в Санкт-Петербурге''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
7 'olga@yandex.ru''stepan@mail.ru''Фильтры воды для дома и дачи''Добрый день! Заказывайте фильтры воды только у нас!''Всеволод Гаврилов'24


2. tMailTest - Таблица тестов для рассылки, например, тест темы письма, тест имени отправителя и т.п. Одна рассылка может содержать несколько тестов

mt_id mt_name m_id_parent
1 'Тест заголовка письма' 22
2 'Тест заголовка письма' 23



3. tMailTestValues
mtv_id mt_id mtv_value
11'С Новым 2018 Годом!'
21'Поздравляем от всей души с Новым Годом'
31'От всей души поздравляем с Новым Годом'
42'Профессиональные услуги клининга!'
52'Позаботьтесь о чистоте в вашем доме'


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

m_id m_addressFrom m_addressTo m_subject m_text m_nameFrom m_id_parent
1 'vova@yandex.ru''vasya@mail.ru''С Новым 2018 Годом!''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
2 'vova@yandex.ru''ivan@mail.ru''Поздравляем от всей души с Новым Годом''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
3 'vova@yandex.ru''petr@mail.ru''От всей души поздравляем с Новым Годом''Добрый день! Примите наши теплый поздравления''Всеволод Гаврилов'22
4 'gena@yandex.ru''masha@mail.ru''Профессиональные услуги клининга!''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
5 'gena@yandex.ru''dasha@mail.ru''Позаботьтесь о чистоте в вашем доме''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
6 'gena@yandex.ru''zinaida@mail.ru''Профессиональные услуги клининга!''Добрый день! Заказывайте клининг только у нас!''Всеволод Гаврилов'23
7 'olga@yandex.ru''stepan@mail.ru''Фильтры воды для дома и дачи''Добрый день! Заказывайте фильтры воды только у нас!''Всеволод Гаврилов'24
3 янв 18, 19:08    [21080480]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
aleks222
Guest
Осподи, что за фуфло?!!

select new_m_subject = isnull( x.mtv_value, t.m_subject)
       , t.* 
  from  v_A_TasksMail as t 
          outer apply ( select top(1) * from tMailTestValues where mtv_id = t.m_id ) as x


ЗЫ. Я догадываюсь, что страдалец хочет "иного"... но суть та же.
4 янв 18, 07:13    [21081173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
ntile,
Guest
Vsevolod V,

что б ты ещё скриптами всю эту "красоту" выкладывал, цены бы тебе не было :)

+
declare @v_A_TasksMail table (m_id int, m_addressFrom varchar(100), m_addressTo varchar(100), m_subject varchar(100), m_text varchar(100), m_nameFrom varchar(100), m_id_parent int) 
insert into @v_A_TasksMail values
 (1, 'vova@yandex.ru', 'vasya@mail.ru', 'Поздравляем Вас с Новым Годом!', 'Добрый день! Примите наши теплый поздравления', 'Всеволод Гаврилов', 22)
,(2, 'vova@yandex.ru', 'ivan@mail.ru', 'Поздравляем Вас с Новым Годом!', 'Добрый день! Примите наши теплый поздравления', 'Всеволод Гаврилов', 22) 
,(3, 'vova@yandex.ru', 'petr@mail.ru', 'Поздравляем Вас с Новым Годом!', 'Добрый день! Примите наши теплый поздравления', 'Всеволод Гаврилов', 22) 
,(4, 'gena@yandex.ru', 'masha@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! Заказывайте клининг только у нас!', 'Всеволод Гаврилов', 23) 
,(5, 'gena@yandex.ru', 'dasha@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! Заказывайте клининг только у нас!', 'Всеволод Гаврилов', 23) 
,(6, 'gena@yandex.ru', 'zinaida@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! Заказывайте клининг только у нас!', 'Всеволод Гаврилов', 23) 
,(7, 'olga@yandex.ru', 'stepan@mail.ru', 'Фильтры воды для дома и дачи', 'Добрый день! Заказывайте фильтры воды только у нас!', 'Всеволод Гаврилов', 24) 

declare @tMailTest table (mt_id int, mt_name varchar(100), m_id_parent int) 
insert into @tMailTest values
 (1, 'Тест заголовка письма', 22) 
,(2, 'Тест заголовка письма', 23) 

declare @tMailTestValues table (mtv_id int, mt_id int, mtv_value varchar(100))
insert into @tMailTestValues values
 (1, 1, 'С Новым 2018 Годом!')
,(2, 1, 'Поздравляем от всей души с Новым Годом') 
,(3, 1, 'От всей души поздравляем с Новым Годом')
,(4, 2, 'Профессиональные услуги клининга!') 
,(5, 2, 'Позаботьтесь о чистоте в вашем доме')


;with a1 as 
	(select *, n=row_number()over(partition by m_id_parent order by m_id) from @v_A_TasksMail)
,b1 as
	(select 
			a.*
			,b.m_id_parent
			,n=row_number()over(partition by b.m_id_parent order by a.mtv_id) 
			,m=count(a.mtv_id)over(partition by b.m_id_parent)
	from @tMailTestValues a inner join @tMailTest b on a.mt_id=b.mt_id) 

select 
		new_m_subject	=isnull(b1.mtv_value, a1.m_subject)	
		,a1.* 

from a1 left join b1 on a1.m_id_parent=b1.m_id_parent and (a1.n-1)%b1.m+1=b1.n  


new_m_subjectm_idm_addressFromm_addressTom_subjectm_textm_nameFromm_id_parentn
С Новым 2018 Годом!1vova@yandex.ruvasya@mail.ruПоздравляем Вас с Новым Годом!Добрый день! Примите наши теплый поздравленияВсеволод Гаврилов221
Поздравляем от всей души с Новым Годом2vova@yandex.ruivan@mail.ruПоздравляем Вас с Новым Годом!Добрый день! Примите наши теплый поздравленияВсеволод Гаврилов222
От всей души поздравляем с Новым Годом3vova@yandex.rupetr@mail.ruПоздравляем Вас с Новым Годом!Добрый день! Примите наши теплый поздравленияВсеволод Гаврилов223
Профессиональные услуги клининга!4gena@yandex.rumasha@mail.ruУслуги клининга в Санкт-ПетербургеДобрый день! Заказывайте клининг только у нас!Всеволод Гаврилов231
Позаботьтесь о чистоте в вашем доме5gena@yandex.rudasha@mail.ruУслуги клининга в Санкт-ПетербургеДобрый день! Заказывайте клининг только у нас!Всеволод Гаврилов232
Профессиональные услуги клининга!6gena@yandex.ruzinaida@mail.ruУслуги клининга в Санкт-ПетербургеДобрый день! Заказывайте клининг только у нас!Всеволод Гаврилов233
Фильтры воды для дома и дачи7olga@yandex.rustepan@mail.ruФильтры воды для дома и дачиДобрый день! Заказывайте фильтры воды только у нас!Всеволод Гаврилов241
4 янв 18, 13:33    [21081549]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
Задача сильно изменилась. Помогите, пожалуйста. Я вижу решение через использование inline фукнции, в которой будут рандомно выбираться и подменяться параметры в тексте. Но производительность, боюсь, будет низкой. Как можно решить это с помощью запрос?

Мне необходимо сделать рандомизацию текстов писем в рассылках для тестирования их эфективности. Под рандомизацией текста письма подразумевается то, что в тексте используются параметры, например [param1], которые подменяются при отправке письма рандомно.
Помогите, пожалуйста, как это можно решить наиболее оптимально с точки зрения производитеьности?

Исходные данные:
declare @v_A_TasksMail table (m_id int, m_addressFrom varchar(100), m_addressTo varchar(100), m_subject varchar(100), m_text varchar(100), m_nameFrom varchar(100), m_id_parent int) 
insert into @v_A_TasksMail values
 (1, 'vova@yandex.ru', 'vasya@mail.ru', 'Поздравляем Вас с Новым Годом!', '[param1] [param2]. Желаем Вам здоровья и счастья!', 'Всеволод Гаврилов', 22)
,(2, 'vova@yandex.ru', 'ivan@mail.ru', 'Поздравляем Вас с Новым Годом!', '[param1] [param2]. Желаем Вам здоровья и счастья!', 'Всеволод Гаврилов', 22) 
,(3, 'vova@yandex.ru', 'petr@mail.ru', 'Поздравляем Вас с Новым Годом!', '[param1] [param2]. Желаем Вам здоровья и счастья!', 'Всеволод Гаврилов', 22) 
,(4, 'gena@yandex.ru', 'masha@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! [param25] только у нас!', 'Всеволод Гаврилов', 23) 
,(5, 'gena@yandex.ru', 'dasha@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! [param25] только у нас!', 'Всеволод Гаврилов', 23) 
,(6, 'gena@yandex.ru', 'zinaida@mail.ru', 'Услуги клининга в Санкт-Петербурге', 'Добрый день! [param25] только у нас!', 'Всеволод Гаврилов', 23) 
,(7, 'olga@yandex.ru', 'stepan@mail.ru', 'Фильтры воды для дома и дачи', 'Добрый день! Заказывайте фильтры воды только у нас!', 'Всеволод Гаврилов', 24) 

declare @tMailTest table (mt_id int, mt_name varchar(100), m_id_parent int, mt_tag varchar(100)) 
insert into @tMailTest values
 (1, 'Тест текста письма', 22, '[param1]') 
,(2, 'Тест текста письма', 22, '[param2]') 
,(2, 'Тест текста письма', 23, '[param25]') 

declare @tMailTestValues table (mtv_id int, mt_id int, mtv_value varchar(100))
insert into @tMailTestValues values
 (1, 1, 'Добрый день!')
,(2, 1, 'Здравствуйте!') 
,(3, 1, 'Доброго времени суток!')
,(4, 2, 'Примите наши теплые поздравления!') 
,(5, 2, 'От весго нашего коллектива поздравляю Вас с Новым Годом!')
,(6, 3, 'Заказывайте услуги клининга')
,(7, 3, 'Профессиональные услуги клининга')


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

m_id m_addressFrom m_addressTo m_subject m_text m_nameFrom m_id_parent
1 'vova@yandex.ru' 'vasya@mail.ru' 'С Новым 2018 Годом!' 'Здравствуйте! Примите наши теплые поздравления' 'Всеволод Гаврилов' 22
2 'vova@yandex.ru' 'ivan@mail.ru' 'Поздравляем от всей души с Новым Годом' 'Здравствуйте! От весго нашего коллектива поздравляю Вас с Новым Годом!' 'Всеволод Гаврилов' 22
3 'vova@yandex.ru' 'petr@mail.ru' 'От всей души поздравляем с Новым Годом' 'Доброго времени суток! От весго нашего коллектива поздравляю Вас с Новым Годом!' 'Всеволод Гаврилов' 22
4 'gena@yandex.ru' 'masha@mail.ru' 'Профессиональные услуги клининга!' 'Добрый день! Профессиональные услуги клининга только у нас!' 'Всеволод Гаврилов' 23
5 'gena@yandex.ru' 'dasha@mail.ru' 'Профессиональные услуги клининга!' 'Добрый день! Профессиональные услуги клининга только у нас!' 'Всеволод Гаврилов' 23
6 'gena@yandex.ru' 'zinaida@mail.ru' 'Профессиональные услуги клининга!' 'Добрый день! Заказывайте клининг только у нас!' 'Всеволод Гаврилов' 23
7 'olga@yandex.ru' 'stepan@mail.ru' 'Фильтры воды для дома и дачи' 'Добрый день! Заказывайте фильтры воды только у нас!' 'Всеволод Гаврилов' 24
23 авг 18, 15:33    [21652174]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5699
Vsevolod V,

на SQL2016+ можно использовать FORMATMESSAGE().
23 авг 18, 18:30    [21652327]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 8006
Vsevolod V,

select
 vtm.*, f.x.value('.', 'varchar(max)')
from
 @v_A_TasksMail vtm cross apply
 (select replace(cast(cast('' as xml).query('sql:column("vtm.m_text")') as varchar(max)), '"', '"')) a(m_text) cross apply
 (select cast('<text value = "' + replace(replace(a.m_text, '[', '"/><param value = "['), ']', ']"/><text value = "') + '"/>' as xml)) b(x) cross apply
 (
  select
   isnull(e.mtv_value, d.node_value)
  from
   (
    select
     c.n.value('local-name(.)', 'varchar(1000)'),
     c.n.value('@value', 'varchar(1000)'),
     row_number() over (order by (select 1))
	from
     b.x.nodes('*') c(n)
   ) d(node_name, node_value, rn) outer apply
   (
    select top (1)
	 mtv.mtv_value
	from
	 @tMailTest mt join
	 @tMailTestValues mtv on mtv.mt_id = mt.mt_id
	where
	 d.node_name = 'param' and
	 mt.mt_tag = d.node_value
	order by
	 newid()
   ) e
  for xml path(''), type
 ) f(x);
23 авг 18, 18:38    [21652332]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 8006
Исправленный вариант
select
 vtm.*, f.x.value('.', 'varchar(max)')
from
 @v_A_TasksMail vtm cross apply
 (select replace(cast(cast('' as xml).query('sql:column("vtm.m_text")') as varchar(max)), '"', '&quot;')) a(m_text) cross apply
 (select cast('<text value = "' + replace(replace(a.m_text, '[', '"/><param value = "['), ']', ']"/><text value = "') + '"/>' as xml)) b(x) cross apply
 (
  select
   isnull(e.mtv_value, d.node_value)
  from
   (
    select
     c.n.value('local-name(.)', 'varchar(1000)'),
     c.n.value('@value', 'varchar(1000)'),
     row_number() over (order by (select 1))
    from
     b.x.nodes('*') c(n)
   ) d(node_name, node_value, rn) outer apply
   (
    select top (1)
     mtv.mtv_value
    from
     @tMailTest mt join
     @tMailTestValues mtv on mtv.mt_id = mt.mt_id
    where
     d.node_name = 'param' and
     mt.mt_tag = d.node_value
    order by
     newid()
   ) e
  order by
   d.rn
  for xml path(''), type
 ) f(x);
23 авг 18, 18:50    [21652341]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 384
invm,

ну вы как зафигачите так зафигачите...
кто это кроме вас сопровождать сможет?
23 авг 18, 19:03    [21652350]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
invm,

Нетривиальное интересное решение. Спасибо большое.
Чтобы сопровождать, конечно, придется поломать голову)
23 авг 18, 19:32    [21652383]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
Владислав Колосов
Vsevolod V,

на SQL2016+ можно использовать FORMATMESSAGE().


Можете, пожалуйста, поподробнее, почему это решение подойдет? Не ясно следующее. Фнкция прнимает аргументом param_value, который:
1. имеет отношение 1 к 1 с заполнителем значения, а надо 1 ко многим
2. Имеет ограничение в 20 значений
23 авг 18, 19:48    [21652402]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 8006
Владимир Затуливетер
кто это кроме вас сопровождать сможет?
Vsevolod V
Чтобы сопровождать, конечно, придется поломать голову)
Тут нет ничего экстраординарного.
Достаточно вывести промежуточные результаты, чтобы понять как работает запрос.

Vsevolod V
Можете, пожалуйста, поподробнее, почему это решение подойдет? Не ясно следующее. Фнкция прнимает аргументом param_value, который:
1. имеет отношение 1 к 1 с заполнителем значения, а надо 1 ко многим
2. Имеет ограничение в 20 значений
FORMATMESSAGE для вашей задачи не подходит. Вы не сможете вызвать функцию в одном запроск с разным количеством аргументов.
23 авг 18, 20:33    [21652442]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Vsevolod V
Member

Откуда: Россия. Санкт-Петербург
Сообщений: 3188
invm,

Все понял. Еще раз спасибо!
23 авг 18, 21:46    [21652483]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5699
Напрягите фантазийный орган, компаньерос.
Шаблон ведь ничего не стоит поправить.

select FORMATMESSAGE(N'надо %s %s %s', N'123 р.', '', '')
union
select FORMATMESSAGE(N'приходите в %s-%s %s', '18', '30', '')
union
select FORMATMESSAGE(N'дорогой %s %s %s!', N'Сидоров', N'Петр', N'Фёдорович')
24 авг 18, 11:52    [21652906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 8006
Владислав Колосов
Шаблон ведь ничего не стоит поправить.
Может уже перейдете от теории к практике?
И покажете нам как на исходных данных из 21652174 с помощью FORMATMESSAGE получить результат, показанный там же?
24 авг 18, 12:20    [21652946]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 5699
invm,

для той структуры справочников не подойдёт, конечно. Но справочники же можно изменить?
24 авг 18, 16:51    [21653363]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
invm
Member

Откуда: Москва
Сообщений: 8006
Владислав Колосов
Но справочники же можно изменить?
А так же зафиксировать количество, порядок и смысл параметров в шаблоне?
24 авг 18, 17:10    [21653374]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом, пожалуйста  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 384
Пятничный quiz!
Как вам такой вариант? Разбито на модули (функции), сопровождать проще намного будет думаю.

use tempdb
go

drop table if exists dbo.v_A_TasksMail
drop table if exists dbo.tMailTest
drop table if exists dbo.tMailTestValues
go

create table dbo.v_A_TasksMail (m_id int, m_addressFrom nvarchar(100), m_addressTo nvarchar(100), m_subject nvarchar(100), m_text nvarchar(100), m_nameFrom nvarchar(100), m_id_parent int) 
insert into dbo.v_A_TasksMail values
 (1, 'vova@yandex.ru', 'vasya@mail.ru', N'Поздравляем Вас с Новым Годом!', N'[param1] [param2]. Желаем Вам здоровья и счастья!', N'Всеволод Гаврилов', 22)
,(2, 'vova@yandex.ru', 'ivan@mail.ru', N'Поздравляем Вас с Новым Годом!', N'[param1] [param2]. Желаем Вам здоровья и счастья!', N'Всеволод Гаврилов', 22) 
,(3, 'vova@yandex.ru', 'petr@mail.ru', N'Поздравляем Вас с Новым Годом!', N'[param1] [param2]. Желаем Вам здоровья и счастья!', N'Всеволод Гаврилов', 22) 
,(4, 'gena@yandex.ru', 'masha@mail.ru', N'Услуги клининга в Санкт-Петербурге', N'Добрый день! [param25] только у нас!', N'Всеволод Гаврилов', 23) 
,(5, 'gena@yandex.ru', 'dasha@mail.ru', N'Услуги клининга в Санкт-Петербурге', N'Добрый день! [param25] только у нас!', N'Всеволод Гаврилов', 23) 
,(6, 'gena@yandex.ru', 'zinaida@mail.ru', N'Услуги клининга в Санкт-Петербурге', N'Добрый день! [param25] только у нас!', N'Всеволод Гаврилов', 23) 
,(7, 'olga@yandex.ru', 'stepan@mail.ru', N'Фильтры воды для дома и дачи', N'Добрый день! Заказывайте фильтры воды только у нас!', N'Всеволод Гаврилов', 24) 

create table dbo.tMailTest (mt_id int, mt_name nvarchar(100), m_id_parent int, mt_tag nvarchar(100)) 
insert into dbo.tMailTest values
 (1, N'Тест текста письма', 22, '[param1]') 
,(2, N'Тест текста письма', 22, '[param2]') 
,(2, N'Тест текста письма', 23, '[param25]') 

create table dbo.tMailTestValues (mtv_id int, mt_id int, mtv_value nvarchar(100))
insert into dbo.tMailTestValues values
 (1, 1, N'Добрый день!')
,(2, 1, N'Здравствуйте!') 
,(3, 1, N'Доброго времени суток!')
,(4, 2, N'Примите наши теплые поздравления!') 
,(5, 2, N'От весго нашего коллектива поздравляю Вас с Новым Годом!')
,(6, 3, N'Заказывайте услуги клининга')
,(7, 3, N'Профессиональные услуги клининга')

go

-- эта вьюха будет нужна чтобы получить случайное число в функции
create or alter view dbo.GetNewId as select newid() as new_id
go

-- получает список параметров из строки
create or alter function dbo.GetStringParams (
	@String nvarchar(max)
)
returns table
as 
/* test
select * from dbo.GetStringParams (N'asdf [param1] [param2]. Желаем Вам здоровья и счастья!')
*/
return
	select concat('[', t.ParamName, ']') as ParamName
	from string_split(@String, ']') 
		cross apply ( select charindex('[', value) as StartIndex ) c
		cross apply ( select stuff(value, 1, c.StartIndex, '') as ParamName ) t
	where c.StartIndex != 0
go

-- получает рандомное значение для переданного параметра
create or alter function dbo.GetRandomParamValue (
	@ParamName nvarchar(100)
)
returns table
as
/* test
select * from dbo.GetRandomParamValue ('[param1]')
*/
return
	select vv.mtv_value as RandomValue
	from dbo.tMailTest t
		cross apply (
			select top 1 *
			from dbo.tMailTestValues v cross join dbo.GetNewId
			where v.mt_id = t.mt_id
			order by new_id
		) vv
	where mt_tag = @ParamName
go

-- меняет параметры в строке на рандомные значения
create or alter function dbo.ReplaceParamsRandom (
	@String nvarchar(max)
)
returns nvarchar(max)
as
begin
/* test
select dbo.ReplaceParamsRandom ('P1=[param1] P2=[param2], P25=[param25]')
*/	
	-- инлайн не осилил...
	select @String = replace(@String, p.ParamName, rv.RandomValue)
	from dbo.GetStringParams(@String) p
		cross apply dbo.GetRandomParamValue(p.ParamName) rv

	return @String;

end
go

select m_id
     , m_addressFrom
     , m_addressTo
     , m_subject
     , dbo.ReplaceParamsRandom(m_text)
     , m_nameFrom
     , m_id_parent
from dbo.v_A_TasksMail
24 авг 18, 21:48    [21653527]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить