ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сортировка ORDER BY по условию CASE
23 сообщений из 23, страница 1 из 1
Сортировка ORDER BY по условию CASE
    #39873976
Sniffka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sniffka
Гость
Здравствуйте, уважаемые знатоки.
У меня есть идея написать запрос, который будет выполнять сортировку результатов по параметру, переданному в запрос.
И все работало до тех пор, пока поля, по которым производилась сортировка были однотипные. Это поля name и code. Когда добавил еще одно поле id, типа int, стал получать ошибку: "Conversion failed when converting the nvarchar value 'ХХХХХХХ' to data type int".
Т.е. в операторе CASE происходит преобразование типов.
При этом, есть вероятность, что придется сортировать по полю с датой, сто также приводит к ошибке.
Как обойти эту ошибку?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
DECLARE @sort nvarchar(max) = 'name';
DECLARE @order nvarchar(max) = 'desc';

SELECT id, name, code
FROM [scale]

ORDER BY
        CASE WHEN @order = 'DESC' THEN
                CASE @sort
                        WHEN 'id' THEN id
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END DESC,
        CASE WHEN @order = 'ASC' THEN
                CASE @sort
                        WHEN 'id' THEN id
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END



Таблица:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE [dbo].[scale](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[code] [nvarchar](100) NULL,
	[date_create] [datetime] NULL,
	[date_modify] [datetime] NULL,
 CONSTRAINT [PK_scale] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
...
Рейтинг: 0 / 0
09.10.2019, 13:38
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39873981
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
https://docs.microsoft.com/ru-ru/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017


Типы возвращаемых данных
Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression.. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
...
Рейтинг: 0 / 0
09.10.2019, 13:41
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39873982
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
Конвертируйте поля в выражении case к одному типу. Или динамику городите.
...
Рейтинг: 0 / 0
09.10.2019, 13:42
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39873984
Minamoto
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Minamoto
Участник
SniffkaЗдравствуйте, уважаемые знатоки.
У меня есть идея написать запрос, который будет выполнять сортировку результатов по параметру, переданному в запрос.
И все работало до тех пор, пока поля, по которым производилась сортировка были однотипные. Это поля name и code. Когда добавил еще одно поле id, типа int, стал получать ошибку: "Conversion failed when converting the nvarchar value 'ХХХХХХХ' to data type int".
Т.е. в операторе CASE происходит преобразование типов.
При этом, есть вероятность, что придется сортировать по полю с датой, сто также приводит к ошибке.
Как обойти эту ошибку?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
DECLARE @sort nvarchar(max) = 'name';
DECLARE @order nvarchar(max) = 'desc';

SELECT id, name, code
FROM [scale]

ORDER BY
        CASE WHEN @order = 'DESC' THEN
                CASE @sort
                        WHEN 'id' THEN id
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END DESC,
        CASE WHEN @order = 'ASC' THEN
                CASE @sort
                        WHEN 'id' THEN id
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END



Таблица:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE [dbo].[scale](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](100) NOT NULL,
	[code] [nvarchar](100) NULL,
	[date_create] [datetime] NULL,
	[date_modify] [datetime] NULL,
 CONSTRAINT [PK_scale] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



Код: sql
1.
2.
ORDER BY
        CASE WHEN @order = 'DESC' and @sort = 'id' THEN id ELSE null END DESC,



И так далее. Но изврат, конечно.
...
Рейтинг: 0 / 0
09.10.2019, 13:42
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39873999
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Sniffka,

ничего толкового из этого не получится. Сортируйте в клиентском приложении.
...
Рейтинг: 0 / 0
09.10.2019, 13:56
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874000
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
А еще лучше - используйте табличную функцию, а не процедуру.
...
Рейтинг: 0 / 0
09.10.2019, 13:58
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874025
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Sniffka,

Код: 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.
declare @t table (a int, b varchar(30));
insert into @t
values
 (1, 'b'), (2, 'a');

declare @order int = 1;

select
 *
from
 @t
order by
 case
  when @order = 1 then cast(a as sql_variant)
  when @order = 2 then cast(b as sql_variant)
 end;

set @order = 2;

select
 *
from
 @t
order by
 case
  when @order = 1 then cast(a as sql_variant)
  when @order = 2 then cast(b as sql_variant)
 end;
...
Рейтинг: 0 / 0
09.10.2019, 14:19
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874056
Sniffka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sniffka
Гость
invm, ваше решение мне понравилось больше.
Удалось отделаться только 1 преобразованием типа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
ORDER BY
        CASE WHEN @order = 'DESC' THEN
                CASE @sort
                        WHEN 'id' THEN CAST(id AS sql_variant)
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END DESC,
        CASE WHEN @order = 'ASC' THEN
                CASE @sort
                        WHEN 'id' THEN CAST(id AS sql_variant)
                        WHEN 'name' THEN name
                        WHEN 'code' THEN code
                END
        END
...
Рейтинг: 0 / 0
09.10.2019, 14:43
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874059
Sniffka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sniffka
Гость
Владислав Колосов,

Сортировать на клиенте не вариант. Запрос -- представляет собой одчитывание данных. И выглядит полностью так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT id, name, code
FROM [scale]
ORDER BY 
    CASE WHEN @order = 'DESC' THEN 
        CASE @sort
            WHEN 'id' THEN CAST(id AS sql_variant)
            WHEN 'name' THEN name
            WHEN 'code' THEN code
        END
    END DESC,
    CASE WHEN @order = 'ASC' THEN 
        CASE @sort
        WHEN 'id' THEN CAST(id AS sql_variant)
        WHEN 'name' THEN name
            WHEN 'code' THEN code
        END
    END
OFFSET @start ROWS
FETCH NEXT @limit ROWS ONLY		
FOR JSON AUTO
...
Рейтинг: 0 / 0
09.10.2019, 14:46
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874067
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Sniffka,

в сложных случаях лучше использовать генератор запросов, написанный CLR (процедура, функция). При больших объемах данных variant тип даст ухудшение производительности.
...
Рейтинг: 0 / 0
09.10.2019, 14:51
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874076
Sniffka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sniffka
Гость
Владислав КолосовSniffka,

в сложных случаях лучше использовать генератор запросов, написанный CLR (процедура, функция). При больших объемах данных variant тип даст ухудшение производительности.

Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)
...
Рейтинг: 0 / 0
09.10.2019, 14:55
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874078
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
SniffkaУдалось отделаться только 1 преобразованием типаНе надо лениться. Добавите дату - придется писать преобразование везде.

Но можно упростить
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT id, name, code
FROM [scale] cross apply
(select cast(id as sql_variant), cast(name as sql_variant), cast(code as sql_variant)) t(id_v, name_v, code_v)

ORDER BY
        CASE WHEN @order = 'DESC' THEN
                CASE @sort
                        WHEN 'id' THEN id_v
                        WHEN 'name' THEN name_v
                        WHEN 'code' THEN code_v
                END
        END DESC,
        CASE WHEN @order = 'ASC' THEN
                CASE @sort
                        WHEN 'id' THEN id_v
                        WHEN 'name' THEN name_v
                        WHEN 'code' THEN code_v
                END
        END
...
Рейтинг: 0 / 0
09.10.2019, 14:55
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874089
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
invmНе надо лениться. Добавите дату - придется писать преобразование везде.

почему?
sql_variant - имеет наибольший приоритет, среди всех типов
...
Рейтинг: 0 / 0
09.10.2019, 15:00
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874097
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
msLex,

Да, вы правы.
...
Рейтинг: 0 / 0
09.10.2019, 15:06
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874102
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
invm,

Более того, нет ни одного типа, который требовал бы явного приведение к sql_variant. Либо поддерживается неявное, либо приведение вообще не поддерживается.

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
...
Рейтинг: 0 / 0
09.10.2019, 15:10
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874105
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
SniffkaВладислав КолосовSniffka,

в сложных случаях лучше использовать генератор запросов, написанный CLR (процедура, функция). При больших объемах данных variant тип даст ухудшение производительности.

Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.
...
Рейтинг: 0 / 0
09.10.2019, 15:11
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874133
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
invmSniffkaпропущено...


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.Но для разных типов нужны разные типы сортировки. Например, по целому числу - одно правило, по строковому представлению числа - другое.
Так что не просто преобразовать в строку придётся, но и после этого её обработать (например, выравнять по правому краю для целого).
...
Рейтинг: 0 / 0
09.10.2019, 15:28
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874141
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Я пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.
...
Рейтинг: 0 / 0
09.10.2019, 15:36
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874201
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Владислав КолосовЯ пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.Верю. Только зачем обобщать?
Тем более, что сортировка - не сравнение, и, в большинстве случаев, простого преобразования в строку недостаточно.
...
Рейтинг: 0 / 0
09.10.2019, 16:39
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874396
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Не надо ничего преобразовывать.
Столбцов мало, вариантов - тоже.

Код: 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.
DECLARE @sort nvarchar(max) = 'name';
DECLARE @order nvarchar(max) = 'desc';

Create table #scale (id int, name varchar(10), code numeric(30,0))

insert into #scale
Values (1, 'test1', 1000000000000000000),
 (2, 'test1', 1000000000000000001),
 (3, 'test4', 2000000000000000001),
 (5, 'test6', 2000000001000000001)


Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by id ASC
) t
Where @sort = 'id' and @order = 'asc'
Union all
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by id DESC
) t
Where @sort = 'id' and @order = 'DESC'
Union all
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by name ASC
) t
Where @sort = 'name' and @order = 'ASC'
Union all
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by name DESC
) t
Where @sort = 'name' and @order = 'DESC'
Union all
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by code ASC
) t
Where @sort = 'code' and @order = 'ASC'
Union all
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by code DESC
) t
Where @sort = 'code' and @order = 'DESC'
...
Рейтинг: 0 / 0
10.10.2019, 08:33
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874502
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
uaggster
Код: sql
1.
2.
3.
4.
5.
6.
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by id ASC
) t



этот не гарантирует сортировку
...
Рейтинг: 0 / 0
10.10.2019, 12:15
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874527
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
msLex, да, вы правы.
Тогда так:

Код: 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.
DECLARE @sort nvarchar(max) = 'name';
DECLARE @order nvarchar(max) = 'desc';

Create table #scale (id int, name varchar(10), code numeric(30,0))

insert into #scale
Values (1, 'test1', 1000000000000000000),
 (2, 'test1', 1000000000000000001),
 (3, 'test4', 2000000000000000001),
 (5, 'test6', 2000000001000000001)

Select id, name, code
from (
Select 
Row_Number() over (order by id ASC) N
, id, name, code
FROM #scale
Where @sort = 'id' and @order = 'asc'
Union all
Select 
Row_Number() over (order by id DESC) N
,id, name, code
FROM #scale
Where @sort = 'id' and @order = 'DESC'
Union all
Select 
Row_Number() over (order by name ASC) N
,id, name, code
FROM #scale
Where @sort = 'name' and @order = 'ASC'
Union all
Select
Row_Number() over (order by name desc) N
,id, name, code
FROM #scale
Where @sort = 'name' and @order = 'DESC'
Union all
Select 
Row_Number() over (order by code ASC) N
,id, name, code
FROM #scale
Where @sort = 'code' and @order = 'ASC'
Union all
Select Row_Number() over (order by code desc) N
,id, name, code
FROM #scale
Where @sort = 'code' and @order = 'DESC'
) t
Order by N
...
Рейтинг: 0 / 0
10.10.2019, 12:54
| Ответить | Цитировать | Написать  
Сортировка ORDER BY по условию CASE
    #39874656
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
invmSniffkaпропущено...


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.

Эээ?

Код: sql
1.
2.
3.
4.
5.
SELECT id, name, code
FROM [scale] cross apply
(select cast(id as sql_variant), cast(name as sql_variant), cast(code as sql_variant)) t(id_v, name_v, code_v)
ORDER BY
  iif( @sort = 'id', id, 0), iif( @sort = 'name', name_v, '')...
...
Рейтинг: 0 / 0
10.10.2019, 15:11
| Ответить | Цитировать | Написать  
23 сообщений из 23, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сортировка ORDER BY по условию CASE
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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