Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / case запрос / 25 сообщений из 25, страница 1 из 1
15.08.2018, 11:31
    #39687947
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Есть запрос с несколько сотней условий, условия постоянно добавляются, появилась необходимость вынести условия в отдельную таблицу, с вызовом вложенного запроса или хранимой процедуры. Подскажите как грамотно написать подобный запрос. БД MSSQL

select
case
when a = 1 then b + 10
when a = 2 then b + a
when a = 3 then b - a
when a = 4 then b + 5
end
from table1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 11:43
    #39687958
nullin
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion, посмотрите в сторону CHOOSE , SQL Server 2012 и выше
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 11:52
    #39687971
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Если я правильно понял вашу задачу, вам нужно вынести вычисления в отдельный модуль.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create function dbo.ifnGetCalculatedValue (
    @a int
  , @b int
)
returns table
as 
return select case 
                when @a = 1 then @b + 10
                when @a = 2 then @b + @a
                when @a = 3 then @b - @a
                when @a = 4 then @b + 5
              end Val
go

select t.*, calc.Val
from dbo.Table1 t
	cross apply dbo.ifnGetCalculatedValue(t.a, t.b) calc
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 12:35
    #39688023
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Владимир Затуливетер,

вы верно поняли вычисления нужно вынести в отдельный модуль
но данные нужно вынести в отдельную таблицу

т.е. в новой таблице, назовем table2 (a, b), должны быть занесены условия и результат
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 12:37
    #39688024
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
nullin,

на сколько я понимаю данный оператор не получится обработать
если будет сложное условие?

when a > 1 and a < 10 and a < b then b + 10
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 12:43
    #39688029
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Владимир Затуливетер,

вспомогательная таблица должна хранить условия и результат

insert into table2 (where,result) values ("a>1 and a<10", "b")
insert into table2 (where,result) values ("a = 1", "b+10")
insert into table2 (where,result) values ("a = 2", "b+a")
insert into table2 (where,result) values ("a=3", "b-5")
insert into table2 (where,result) values ("a=4", "b+5")
insert into table2 (where,result) values ("a>1 and a<10", "b=5")
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 12:54
    #39688040
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
SionВладимир Затуливетер,

вспомогательная таблица должна хранить условия и результат

insert into table2 (where,result) values ("a>1 and a<10", "b")
insert into table2 (where,result) values ("a = 1", "b+10")
insert into table2 (where,result) values ("a = 2", "b+a")
insert into table2 (where,result) values ("a=3", "b-5")
insert into table2 (where,result) values ("a=4", "b+5")
insert into table2 (where,result) values ("a>1 and a<10", "b=5")

1. такую ересь разрешит только динамика
2. нужен приоритет для условий
3. на больших объектах , та и на других это будет ой как не весело
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 13:10
    #39688053
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

автордолжны быть занесены условия и результат
это задача прикладного уровня, а не базы данных.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 13:15
    #39688062
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

для решения задачи на уровне СУБД необходимо создать таблицу с перечислением всех известных значений А (ключ) и списком смещений Б для каждого значения ключа.
Затем использовать объединение в запросе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 14:42
    #39688156
L_argo
L_argo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Напишите SQL-функцию(и) с вычислениями.
Ф-ция пусть ч-л зачитывает из настроечных таблиц, которых может быть много.
Если алгоритм изменится, то ее(их) можно будет на лету изменить не трогая прочих программ.
Это и будет той самой динамикой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 17:44
    #39688311
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Вот можете попробовать. Но это извращение в SQL Server такое обычно не делают.
Как уже выше говорили нужно чтобы условия которые мы пихаем в таблицу были закодированы, например были в функции которую я выше скинул. Функция будет большой, но если правильно отформатировать то думаю не должно быть большой проблемой.

Код: sql
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.
50.
51.
52.
53.
54.
use tempdb
go

create table dbo.Cases
(
    Id smallint identity primary key
  , Сondition varchar(1000) not null
  , Expression varchar(1000) not null
);

insert into dbo.Cases ( Сondition, Expression )
values ( 'a>1 and a<10', 'b' )
     , ( 'a = 1', 'b+10' )
     , ( 'a = 2', 'b+a' )
     , ( 'a=3', 'b-5' )
     , ( 'a=4', 'b+5' )
     , ( 'a>1 and a<10', '5' );
go

create table dbo.Table1 ( a int, b int )
go

insert into dbo.Table1 ( a, b )
values ( 1, 10 )
	 , ( 10, 20 )
	 , ( 5, 30 )
go	

create or alter proc dbo.GetData
as 
	set nocount on;

	declare @CasesStr nvarchar(max) = (
			select concat(' when ', Сondition, ' then ', Expression) 
			from dbo.Cases
			for xml path('')
	)
	set @CasesStr = replace(replace(@CasesStr, '&gt;', '>'), '&lt;', '<')	

	declare @Sql nvarchar(max) = N'
	
	select *,
		case ' + @CasesStr + ' end as Val
	from dbo.Table1
	'

	print @Sql
	exec (@Sql)

go


exec dbo.GetData
go
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 18:10
    #39688330
Remind
Remind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
А чем простейший вариант не устраивает?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
DECLARE @data TABLE (a INT, b INT)

INSERT INTO @data
VALUES
  (1, 7), (2, 12), (3, 4), (4, 9)

DECLARE @trn TABLE (a INT, b INT)

INSERT INTO @trn
VALUES 
  (1, 10), (2, 2), (3, -3), (4, 5)

SELECT d.*, d.b + t.b
FROM @data d
  JOIN @trn t ON t.a = d.a
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 18:47
    #39688342
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
SionВладимир Затуливетер,

вспомогательная таблица должна хранить условия и результат

insert into table2 (where,result) values ("a>1 and a<10", "b")
insert into table2 (where,result) values ("a = 1", "b+10")
insert into table2 (where,result) values ("a = 2", "b+a")
insert into table2 (where,result) values ("a=3", "b-5")
insert into table2 (where,result) values ("a=4", "b+5")
insert into table2 (where,result) values ("a>1 and a<10", "b=5")Варианта два:
1. Использовать динамический SQL, как предложил Владимир Затуливетер
2. Сделать триггер на таблицу, который будет создавать функцию (процедуру, вьюху) для вычислений на основе данных таблицы (и соответственно пересоздавать их при изменении условий).

Что лучше - зависит от задачи.
Если речь о каком то универсальном репорте, и фактически вычисления будут в одном месте, то можно использовать вариант 1
Если эти вычисления будут разбросаны по коду, по куче запросов/процедур, то лучше 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 20:22
    #39688369
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Remind,

в вашем вариант идет денормализация данных.
В случае с case мы получим один единственный результат на выходе
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 20:48
    #39688375
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Владимир Затуливетер, спасибо за помощь.

вы уж простите никогда не писал хранимок по T-SQL в основом на PL-SQL ваял код в ранние годы, конструкции конструктивно разные.

возникла мысль завернуть case условие в процедуру (закодировать)
select
case
when a>1 then b
.....
when a=1 then b+5
end,
table1.id
frоm table1

на выходе получить запрос

select getResultFromTable2(a,b), table1.id frоm table1

при добавлении в таблицу нового условия повесить триггер на изменение для перезаписи хранимой процедуры.

поможете с реализацией триггера и процедуры
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 21:40
    #39688397
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sionвозникла мысль завернуть case условие в процедуруВ функцию.
 
Код: sql
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
use tempdb;
go

create table dbo.Conditions (id int identity primary key, condition nvarchar(100) not null, formula nvarchar(100) not null, priority int not null);
go

create trigger trConditions_GenerateFunction
on dbo.Conditions
after insert, update, delete
as
begin
 set nocount on;

 if not exists(select 1 from inserted) and not exists(select 1 from deleted)
  return;

 declare @case_clause nvarchar(max), @create_statement nvarchar(max);

 select
  @case_clause = N'case ' + nullif(t.x.value('.', 'nvarchar(max)'), '') + N'end'
 from
  (
   select
    N'when (' + condition + N') then (' + formula + N') '
   from
    dbo.Conditions
   order by
    priority
   for xml path(''), type
  ) t(x);

 if object_id('dbo.fnGetResultByCondition', 'IF') is not null
  drop function dbo.fnGetResultByCondition;
 
 select
  @create_statement = N'create function dbo.fnGetResultByCondition (@a int, @b int) returns table as return (select ' +
   isnull(@case_clause, N'cast(null as int)') +
   N' as Result from (values (@a, @b)) t(a, b));';
  
 exec sp_executesql @create_statement;
end;
go

insert into dbo.Conditions
values
 ('a > 3', 'b + 10', 1),
 ('a < b', 'b * 10', 2);
go

select
 *
from
 (values (-1, -2), (1, 2), (4, 0)) t(a, b) cross apply
 dbo.fnGetResultByCondition(t.a, t.b) r;
go

if object_id('dbo.fnGetResultByCondition', 'IF') is not null
 drop function dbo.fnGetResultByCondition;

drop table dbo.Conditions;
go

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2018, 21:56
    #39688403
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
invm,
то что нужно, спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 10:37
    #39688594
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
invm,

для удобства понимания по какому условию происходит расчет, надо еще вывести номер приоритета.
как в вашу функцию добавить вывод приоритета не прибегаю к созданию отдельного запроса?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 11:25
    #39688630
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

 
Код: sql
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
use tempdb;
go

create table dbo.Conditions (id int identity primary key, condition nvarchar(100) not null, formula nvarchar(100) not null, priority int not null);
go

create trigger trConditions_GenerateFunction
on dbo.Conditions
after insert, update, delete
as
begin
 set nocount on;

 if not exists(select 1 from inserted) and not exists(select 1 from deleted)
  return;

 declare @case_clause nvarchar(max), @create_statement nvarchar(max);

 select
  @case_clause = N'case ' + nullif(t.x.value('.', 'nvarchar(max)'), '') + N'end'
 from
  (
   select
    N'when (' + condition +
	N') then (select (' + formula +
	N') as [@Result], ''' + condition + 
	N''' as [@Condition], ''' + formula +
	N''' as [@Formula], ' + cast(priority as nvarchar(10)) +
	N' as [@Priority] for xml path(''ResultData''), type) '
   from
    dbo.Conditions
   order by
    priority
   for xml path(''), type
  ) t(x);

 if object_id('dbo.fnGetResultByCondition', 'IF') is not null
  drop function dbo.fnGetResultByCondition;
 
 select
  @create_statement = N'create function dbo.fnGetResultByCondition (@a int, @b int) returns table as return ' +
   N'(select b.n.value(''@Result'', ''int'') as Result, b.n.value(''@Condition'', ''nvarchar(100)'') as Condition, b.n.value(''@Formula'', ''nvarchar(100)'') as Formula, b.n.value(''@Priority'', ''int'') as Priority ' +
   N'from (select ' +
   isnull(@case_clause, N'cast(null as xml)') +
   N' as ResultData from (values (@a, @b)) t(a, b)) a outer apply a.ResultData.nodes(''/ResultData'') b(n));';
  
 exec sp_executesql @create_statement;
end;
go

insert into dbo.Conditions
values
 ('a > 3', 'b + 10', 1),
 ('a < b', 'b * 10', 2);
go

select
 *
from
 (values (-1, -2), (1, 2), (4, 0)) t(a, b) cross apply
 dbo.fnGetResultByCondition(t.a, t.b) r;
go

if object_id('dbo.fnGetResultByCondition', 'IF') is not null
 drop function dbo.fnGetResultByCondition;

drop table dbo.Conditions;
go

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 21:33
    #39689135
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
invm,

добрый день, к сожалению не работает в случае со строками
Код: sql
1.
2.
3.
4.
insert into dbo.Conditions
values
 ('a = ''TEST''', 'b + 10', 1);
go
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 22:17
    #39689144
Владимир Затуливетер
Владимир Затуливетер
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

Ну вы уж сами давайте поднапрягитесь и исправьте ошибку.
Или хотите чтобы мы все за вас написали?
"не работает" - там же наверное ошибка какая-то появляется? ее и исправляйте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 22:28
    #39689146
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Владимир Затуливетер,
в самой функции это сделать не сложно

Код: sql
1.
when (a = 2) then (select (b * 10) as [@Result], 'a = 2'


заменить часть кода на
Код: sql
1.
when (a = 'A') then (select (b * 10) as [@Result], 'a = ''A'''



но когда я смотрю обработку триггера не понимаю где нужно поправить.
типы [@Result] выносят мозг

пару часов сидел, не выдержал, прошу помощи
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 22:41
    #39689149
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

Поправьте в триггере формирование case'а
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
 select
  @case_clause = N'case ' + nullif(t.x.value('.', 'nvarchar(max)'), '') + N'end'
 from
  (
   select
    N'when (' + condition +
	N') then (select (' + formula +
	N') as [@Result], ''' + replace(condition, '''', '''''') + 
	N''' as [@Condition], ''' + replace(formula, '''', '''''') +
	N''' as [@Formula], ' + cast(priority as nvarchar(10)) +
	N' as [@Priority] for xml path(''ResultData''), type) '
   from
    dbo.Conditions
   order by
    priority
   for xml path(''), type
  ) t(x);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2018, 22:52
    #39689154
Sion
Sion
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
invm,
спасибо я как раз хотел задать вопрос почему на условие then необходимо 4 кавычки
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.08.2018, 11:55
    #39689388
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
case запрос
Sion,

кавычка экранируется кавычкой, читайте документацию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / case запрос / 25 сообщений из 25, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]