반응형
/*******************************************************************************************************************
-- Title : [SQL2016] 다국어 정렬에서 ASC/DESC에 따라 NULL 출력 순서 변경
-- Key word : order by case when then null 정렬 다국어 정렬 alignment
*******************************************************************************************************************/
■ 기본 정렬 상태(ASC vs. DESC)
- ASC일 때 NULL이 우선 출력됨
- DESC일 때 NULL이 나중에 출력됨
■ 정렬 조정(ASC vs. DESC)
- ASC일 때 NULL을 맨 뒤로
- DESC일 때 NULL을 맨 앞으로
■ Script
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
|
-- Create table and data
drop table #ttt;
create table #ttt( id int not null identity, col_a int, col_b nvarchar(100));
insert into #ttt values (56, N'hi, hello' );
insert into #ttt values (47, null );
insert into #ttt values (78, N'baby, baby ye~' );
insert into #ttt values (47, N'하나하면 할머니가' );
insert into #ttt values (47, N'두울하면 두부장수' );
insert into #ttt values (null, N' ' );
insert into #ttt values (34, N'.' );
insert into #ttt values (34, N',' );
insert into #ttt values (null, N'?' );
insert into #ttt values (0, N'19740530' );
insert into #ttt values (45, N'19851111' );
insert into #ttt values (56, N'TAPE FEEDER' );
insert into #ttt values (74, N'キャップ' );
insert into #ttt values (74, N'パネルベンダー' );
insert into #ttt values (48, N'稻麦脱粒机' );
insert into #ttt values (88, N'泡罩机' );
insert into #ttt values (84, N'Kopfstütze für Fahrzeugsitze' );
insert into #ttt values (55, N'ür Fahrzeugsitze' );
insert into #ttt values (77, N'ПРИЖИМНОЕ УСТРОЙСТВО' );
insert into #ttt values (48, null );
insert into #ttt values (48, N'[' );
-- Check the default alignment
select * from #ttt order by col_a asc;
select * from #ttt order by col_a desc;
select * from #ttt order by col_b asc;
select * from #ttt order by col_b desc;
-- ASC일 때는 NULL을 맨 뒤로
declare @ord_sect nvarchar(10) = 'asc';
select * from #ttt
order by (case when @ord_sect = 'asc' then (case when col_b is not null then 1 else 9 end) end) asc
, (case when @ord_sect = 'asc' then col_b end) asc
, (case when @ord_sect = 'desc' then (case when col_b is not null then 1 else 9 end) end) desc
, (case when @ord_sect = 'desc' then col_b end) desc;
-- DESC일 때는 NULL을 맨 앞으로 처리
declare @ord_sect nvarchar(10) = 'desc';
select * from #ttt
order by (case when @ord_sect = 'asc' then (case when col_b is not null then 1 else 9 end) end) asc
, (case when @ord_sect = 'asc' then col_b end) asc
, (case when @ord_sect = 'desc' then (case when col_b is not null then 1 else 9 end) end) desc
, (case when @ord_sect = 'desc' then col_b end) desc;
|
반응형