ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / case запрос
25 сообщений из 25, страница 1 из 1
case запрос
    #39687947
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
Есть запрос с несколько сотней условий, условия постоянно добавляются, появилась необходимость вынести условия в отдельную таблицу, с вызовом вложенного запроса или хранимой процедуры. Подскажите как грамотно написать подобный запрос. БД 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:31
| Ответить | Цитировать | Написать  
case запрос
    #39687958
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
Sion, посмотрите в сторону CHOOSE , SQL Server 2012 и выше
...
Рейтинг: 0 / 0
15.08.2018, 11:43
| Ответить | Цитировать | Написать  
case запрос
    #39687971
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
Если я правильно понял вашу задачу, вам нужно вынести вычисления в отдельный модуль.

Код: 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, 11:52
| Ответить | Цитировать | Написать  
case запрос
    #39688023
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
Владимир Затуливетер,

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

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

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

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

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

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

Код: 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:10
| Ответить | Цитировать | Написать  
case запрос
    #39688342
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
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, 18:47
| Ответить | Цитировать | Написать  
case запрос
    #39688369
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
Remind,

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

вы уж простите никогда не писал хранимок по 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, 20:48
| Ответить | Цитировать | Написать  
case запрос
    #39688397
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
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:40
| Ответить | Цитировать | Написать  
case запрос
    #39688403
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
invm,
то что нужно, спасибо!
...
Рейтинг: 0 / 0
15.08.2018, 21:56
| Ответить | Цитировать | Написать  
case запрос
    #39688594
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
invm,

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

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

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

Код: 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:28
| Ответить | Цитировать | Написать  
case запрос
    #39689149
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
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:41
| Ответить | Цитировать | Написать  
case запрос
    #39689154
Sion
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sion
Участник
invm,
спасибо я как раз хотел задать вопрос почему на условие then необходимо 4 кавычки
...
Рейтинг: 0 / 0
16.08.2018, 22:52
| Ответить | Цитировать | Написать  
case запрос
    #39689388
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Sion,

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


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