存储过程中,为达到根据参数@SortColumn,@SortDirection来动态排序但又不使用动态SQL的效果,在网上找到如下代码可以达到效果,请兄弟们帮我解释一下如下代码段,类似:“假如@SortColumn='A',@SortDirection=0,那么A.DESC后面是什么东西?”并从效率上分析一下如下代码段是否合适SELECT *
FROM MyTable
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
ASC
FROM MyTable
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
ASC
/*
姓名 部门 学历 出生年月
---- ---- ---- ------------------------------------------------------
A 后勤 高中 1986-01-01 00:00:00.000
B 后勤 初中 1984-03-07 00:00:00.000
C 管理 本科 1987-02-01 00:00:00.000
D 操作 专科 1976-02-01 00:00:00.000
E 操作 专科 1943-02-01 00:00:00.000(所影响的行数为 5 行)
*/
declare @SortDirection varchar(1),@SortColumn varchar(10)set @SortDirection=0
set @SortColumn='A'SELECT *
FROM tb
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
-- WHEN 'D' THEN 出生年月
END
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
-- WHEN 'D' THEN 出生年月
END
END
ASC
/*
姓名 部门 学历 出生年月
---- ---- ---- ------------------------------------------------------
E 操作 专科 1943-02-01 00:00:00.000
D 操作 专科 1976-02-01 00:00:00.000
C 管理 本科 1987-02-01 00:00:00.000
B 后勤 初中 1984-03-07 00:00:00.000
A 后勤 高中 1986-01-01 00:00:00.000(所影响的行数为 5 行)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(1),[部门] varchar(4),[学历] varchar(4),[出生年月] datetime)
insert [tb]
select 'A','后勤','高中','1986-1-1' union all
select 'B','后勤','初中','1984-3-7' union all
select 'C','管理','本科','1987-2-1' union all
select 'D','操作','专科','1976-2-1' union all
select 'E','操作','专科','1943-2-1'
declare @SortDirection varchar(1),@SortColumn varchar(10)set @SortDirection=1
set @SortColumn='d'SELECT *
FROM tb
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
WHEN 'D' THEN convert(varchar(23),出生年月,120)
END
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
WHEN 'D' THEN convert(varchar(23),出生年月,120)
END
END
ASC
/*姓名 部门 学历 出生年月
---- ---- ---- ------------------------------------------------------
E 操作 专科 1943-02-01 00:00:00.000
D 操作 专科 1976-02-01 00:00:00.000
B 后勤 初中 1984-03-07 00:00:00.000
A 后勤 高中 1986-01-01 00:00:00.000
C 管理 本科 1987-02-01 00:00:00.000(所影响的行数为 5 行)
*/
----0)解释相关--1)@SortDirection字段的作用是主导是逆序还是升序,0 为DESC,1 为ASC.
--2)@SortColumn为排序字段
--3)如下写法可助于理解,仅供参考--4)脚本SELECT *
FROM foryou
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'id' THEN id
WHEN 'name' THEN name
WHEN 'cou' THEN cou
END
ELSE 1
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'id' THEN id
WHEN 'name' THEN name
WHEN 'cou' THEN cou
END
ELSE 1
END
ASC
而实际上,上面的语句只能有一种排序 要么DESC 要么ASC.
SELECT *
FROM MyTable
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
DESC, --@SortDirection = '1' 时,是:null desc
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
ASC --@SortDirection = '0' 时是:null asc
FROM MyTable
ORDER BY
CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
DESC,
CASE @SortDirection
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
ASC分析一下上面这段,不知是否完全正确。在order by里 ,不指定列名时,写了两个case。当遇到这类情况时,sqlserver应该是对每一个case形成一个新的列(通过执行计划中可以判断),如同select 新的列=CASE @SortDirection
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN A
WHEN 'B' THEN B
WHEN 'C' THEN C
WHEN 'D' THEN D
END
END
所以如果case的结果是null,那么依然不影响他的排序
即使把程序进行如下修改,也照样执行正常declare
@SortDirection1 varchar(1),
@SortDirection2 varchar(1),
@SortColumn varchar(10)set @SortDirection1=0
set @SortDirection2=1
set @SortColumn='A'SELECT *
FROM tb
ORDER BY
CASE @SortDirection1
WHEN '0'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
WHEN 'D' THEN convert(varchar(23),出生年月,120)
END
END
DESC,
CASE @SortDirection2
WHEN '1'
THEN
CASE @SortColumn
WHEN 'A' THEN 姓名
WHEN 'B' THEN 部门
WHEN 'C' THEN 学历
WHEN 'D' THEN convert(varchar(23),出生年月,120)
END
END
ASC