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

Откуда:
Сообщений: 4
Здравствуйте, уважаемые знатоки.
У меня есть идея написать запрос, который будет выполнять сортировку результатов по параметру, переданному в запрос.
И все работало до тех пор, пока поля, по которым производилась сортировка были однотипные. Это поля name и code. Когда добавил еще одно поле id, типа int, стал получать ошибку: "Conversion failed when converting the nvarchar value 'ХХХХХХХ' to data type int".
Т.е. в операторе CASE происходит преобразование типов.
При этом, есть вероятность, что придется сортировать по полю с датой, сто также приводит к ошибке.
Как обойти эту ошибку?

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


Таблица:

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]
9 окт 19, 13:38    [21990371]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
msLex
Member

Откуда:
Сообщений: 6680
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).
9 окт 19, 13:41    [21990378]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36456
Конвертируйте поля в выражении case к одному типу. Или динамику городите.
9 окт 19, 13:42    [21990379]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1086
Sniffka
Здравствуйте, уважаемые знатоки.
У меня есть идея написать запрос, который будет выполнять сортировку результатов по параметру, переданному в запрос.
И все работало до тех пор, пока поля, по которым производилась сортировка были однотипные. Это поля name и code. Когда добавил еще одно поле id, типа int, стал получать ошибку: "Conversion failed when converting the nvarchar value 'ХХХХХХХ' to data type int".
Т.е. в операторе CASE происходит преобразование типов.
При этом, есть вероятность, что придется сортировать по полю с датой, сто также приводит к ошибке.
Как обойти эту ошибку?

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


Таблица:

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]


ORDER BY
        CASE WHEN @order = 'DESC' and @sort = 'id' THEN id ELSE null END DESC,


И так далее. Но изврат, конечно.
9 окт 19, 13:42    [21990381]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Владислав Колосов
Member

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

ничего толкового из этого не получится. Сортируйте в клиентском приложении.
9 окт 19, 13:56    [21990406]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6791
А еще лучше - используйте табличную функцию, а не процедуру.
9 окт 19, 13:58    [21990407]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
invm
Member

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

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;
9 окт 19, 14:19    [21990442]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Sniffka
Member

Откуда:
Сообщений: 4
invm, ваше решение мне понравилось больше.
Удалось отделаться только 1 преобразованием типа:
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
9 окт 19, 14:43    [21990479]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Sniffka
Member

Откуда:
Сообщений: 4
Владислав Колосов,

Сортировать на клиенте не вариант. Запрос -- представляет собой одчитывание данных. И выглядит полностью так:
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
9 окт 19, 14:46    [21990485]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Владислав Колосов
Member

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

в сложных случаях лучше использовать генератор запросов, написанный CLR (процедура, функция). При больших объемах данных variant тип даст ухудшение производительности.
9 окт 19, 14:51    [21990494]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Sniffka
Member

Откуда:
Сообщений: 4
Владислав Колосов
Sniffka,

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


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)
9 окт 19, 14:55    [21990505]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Sniffka
Удалось отделаться только 1 преобразованием типа
Не надо лениться. Добавите дату - придется писать преобразование везде.

Но можно упростить
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
9 окт 19, 14:55    [21990507]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
msLex
Member

Откуда:
Сообщений: 6680
invm
Не надо лениться. Добавите дату - придется писать преобразование везде.


почему?
sql_variant - имеет наибольший приоритет, среди всех типов
9 окт 19, 15:00    [21990520]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
invm
Member

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

Да, вы правы.
9 окт 19, 15:06    [21990533]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
msLex
Member

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

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

https://docs.microsoft.com/ru-ru/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
9 окт 19, 15:10    [21990540]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Sniffka
Владислав Колосов
Sniffka,

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


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)
Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.
9 окт 19, 15:11    [21990544]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
iap
Member

Откуда: Москва
Сообщений: 46858
invm
Sniffka
пропущено...


Эту проблему держу в голове. Если начнет проседать производительность, примем меры. :о)
Универсальная сортировка может быть либо по строке, либо по sql_variant
И тут sql_variant не обязательно проигрывает по производительности, - зависит от исходного типа данных.
Но для разных типов нужны разные типы сортировки. Например, по целому числу - одно правило, по строковому представлению числа - другое.
Так что не просто преобразовать в строку придётся, но и после этого её обработать (например, выравнять по правому краю для целого).
9 окт 19, 15:28    [21990577]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6791
Я пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.
9 окт 19, 15:36    [21990586]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
invm
Member

Откуда: Москва
Сообщений: 8797
Владислав Колосов
Я пробовал использовать SQLVariant для поиска в таблице ~200млн строк, оказалось хуже, чем строка. Речь идет о приведении к типу при сравнении.
Верю. Только зачем обобщать?
Тем более, что сортировка - не сравнение, и, в большинстве случаев, простого преобразования в строку недостаточно.
9 окт 19, 16:39    [21990664]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
uaggster
Member

Откуда:
Сообщений: 715
Не надо ничего преобразовывать.
Столбцов мало, вариантов - тоже.

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'
10 окт 19, 08:33    [21991010]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
msLex
Member

Откуда:
Сообщений: 6680
uaggster
Select * from
(Select top(Select 100) percent 
id, name, code
FROM #scale
Order by id ASC
) t


этот не гарантирует сортировку
10 окт 19, 12:15    [21991166]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
uaggster
Member

Откуда:
Сообщений: 715
msLex, да, вы правы.
Тогда так:

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
10 окт 19, 12:54    [21991199]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка ORDER BY по условию CASE  [new]
aleks222
Member

Откуда:
Сообщений: 747
invm
Sniffka
пропущено...


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


Эээ?

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, '')...
10 окт 19, 15:11    [21991371]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить