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

Откуда:
Сообщений: 66
 		Есть запрос с несколько сотней условий, условия постоянно добавляются, появилась необходимость вынести условия в отдельную таблицу, с вызовом вложенного запроса или хранимой процедуры. Подскажите как грамотно написать подобный запрос. БД 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
15 авг 18, 11:31    [21642731]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
nullin
Member

Откуда: pullin
Сообщений: 131
 		Sion, посмотрите в сторону CHOOSE, SQL Server 2012 и выше
15 авг 18, 11:43    [21642757]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
 		Если я правильно понял вашу задачу, вам нужно вынести вычисления в отдельный модуль.

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
15 авг 18, 11:52    [21642775]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		Владимир Затуливетер,

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

т.е. в новой таблице, назовем table2 (a, b), должны быть занесены условия и результат
15 авг 18, 12:35    [21642865]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

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

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

when a > 1 and a < 10 and a < b then b + 10
15 авг 18, 12:37    [21642868]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		Владимир Затуливетер,

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

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")
15 авг 18, 12:43    [21642882]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
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. на больших объектах , та и на других это будет ой как не весело
15 авг 18, 12:54    [21642901]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владислав Колосов
Member

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

автор
должны быть занесены условия и результат

это задача прикладного уровня, а не базы данных.
15 авг 18, 13:10    [21642933]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владислав Колосов
Member

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

для решения задачи на уровне СУБД необходимо создать таблицу с перечислением всех известных значений А (ключ) и списком смещений Б для каждого значения ключа.
Затем использовать объединение в запросе.
15 авг 18, 13:15    [21642944]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
L_argo
Member

Откуда:
Сообщений: 1132
 		Напишите SQL-функцию(и) с вычислениями.
Ф-ция пусть ч-л зачитывает из настроечных таблиц, которых может быть много.
Если алгоритм изменится, то ее(их) можно будет на лету изменить не трогая прочих программ.
Это и будет той самой динамикой.
15 авг 18, 14:42    [21643177]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
 		Вот можете попробовать. Но это извращение в SQL Server такое обычно не делают.
Как уже выше говорили нужно чтобы условия которые мы пихаем в таблицу были закодированы, например были в функции которую я выше скинул. Функция будет большой, но если правильно отформатировать то думаю не должно быть большой проблемой.

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
15 авг 18, 17:44    [21643470]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 		А чем простейший вариант не устраивает?

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
15 авг 18, 18:10    [21643510]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30702
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
15 авг 18, 18:47    [21643544]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

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

в вашем вариант идет денормализация данных.
В случае с case мы получим один единственный результат на выходе
15 авг 18, 20:22    [21643637]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		Владимир Затуливетер, спасибо за помощь.

вы уж простите никогда не писал хранимок по 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

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

поможете с реализацией триггера и процедуры
15 авг 18, 20:48    [21643655]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9114
Sion
возникла мысль завернуть case условие в процедуру
В функцию.
+
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
15 авг 18, 21:40    [21643702]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		invm,
то что нужно, спасибо!
15 авг 18, 21:56    [21643728]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

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

для удобства понимания по какому условию происходит расчет, надо еще вывести номер приоритета.
как в вашу функцию добавить вывод приоритета не прибегаю к созданию отдельного запроса?
16 авг 18, 10:37    [21644107]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
invm
Member

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

+
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
16 авг 18, 11:25    [21644156]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

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

добрый день, к сожалению не работает в случае со строками
insert into dbo.Conditions
values
 ('a = ''TEST''', 'b + 10', 1);
go
16 авг 18, 21:33    [21645051]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владимир Затуливетер
Member

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

Ну вы уж сами давайте поднапрягитесь и исправьте ошибку.
Или хотите чтобы мы все за вас написали?
"не работает" - там же наверное ошибка какая-то появляется? ее и исправляйте.
16 авг 18, 22:17    [21645098]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		Владимир Затуливетер,
в самой функции это сделать не сложно

when (a = 2) then (select (b * 10) as [@Result], 'a = 2'

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


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

пару часов сидел, не выдержал, прошу помощи
16 авг 18, 22:28    [21645107]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
invm
Member

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

Поправьте в триггере формирование case'а
 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);
16 авг 18, 22:41    [21645119]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Sion
Member

Откуда:
Сообщений: 66
 		invm,
спасибо я как раз хотел задать вопрос почему на условие then необходимо 4 кавычки
16 авг 18, 22:52    [21645133]     Ответить | Цитировать Сообщить модератору
 Re: case запрос  [new]
Владислав Колосов
Member

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

кавычка экранируется кавычкой, читайте документацию.
17 авг 18, 11:55    [21645756]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить