Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сортировка ORDER BY по условию CASE / 23 сообщений из 23, страница 1 из 1
09.10.2019, 13:38
    #39873976
Sniffka
Sniffka
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Здравствуйте, уважаемые знатоки.
У меня есть идея написать запрос, который будет выполнять сортировку результатов по параметру, переданному в запрос.
И все работало до тех пор, пока поля, по которым производилась сортировка были однотипные. Это поля 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:41
    #39873981
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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:42
    #39873982
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Конвертируйте поля в выражении case к одному типу. Или динамику городите.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2019, 13:42
    #39873984
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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:56
    #39873999
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Sniffka,

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

Сортировать на клиенте не вариант. Запрос -- представляет собой одчитывание данных. И выглядит полностью так:
Код: 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:51
    #39874067
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Sniffka,

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

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

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

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

Да, вы правы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2019, 15:10
    #39874102
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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:11
    #39874105
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
SniffkaВладислав КолосовSniffka,

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

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


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.Но для разных типов нужны разные типы сортировки. Например, по целому числу - одно правило, по строковому представлению числа - другое.
Так что не просто преобразовать в строку придётся, но и после этого её обработать (например, выравнять по правому краю для целого).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2019, 15:36
    #39874141
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Я пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.10.2019, 16:39
    #39874201
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
Владислав КолосовЯ пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.Верю. Только зачем обобщать?
Тем более, что сортировка - не сравнение, и, в большинстве случаев, простого преобразования в строку недостаточно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.10.2019, 08:33
    #39874396
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию 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.
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, 12:15
    #39874502
msLex
msLex
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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:54
    #39874527
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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, 15:11
    #39874656
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сортировка ORDER BY по условию CASE
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
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Сортировка ORDER BY по условию CASE / 23 сообщений из 23, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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