代码之家 › 专栏 › 技术社区 › ScottE

混合数据类型的SQLServer动态排序方式:这是个好主意吗?

tsql sql-server-2005

ScottE · 技术社区 · 14 年前

我有一个存储过程,它返回动态排序的结果。父文件夹(用于内容管理)有一个RankTypeID字段,允许按排名(0)、开始日期升序(1)、开始日期降序(2)和文档标题(3)排序

ORDER BY
    Case Parent.RankTypeID
      When 0 Then dbo.Folders.Rank
      When 1 Then Cast(dbo.Documents.SortableDateStart As bigint)
      When 2 Then (1 - Cast(dbo.Documents.SortableDateStart As bigint))
      When 3 Then Cast(dbo.Documents.Title as sql_variant)
    End

我将SortableDateStart设置为计算列,以获取DateStart smalldatetime列并将其转换为bigit进行排序。它采用ISO8601日期(专为xml使用而设计,也便于排序),并替换T、:、和-

(replace(replace(replace(CONVERT([varchar](16),[DateStart],(126)),'T',''),'-',''),':',''))

这有点难看。有没有更好的办法?我也对处理这种动态排序的更好方法持开放态度。

DECLARE @Temp TABLE
(
[Rank] int,
[Title] nvarchar(100),
[DateStart] datetime
)

INSERT into @Temp
SELECT 1, 'title1', '1/1/2010 10:01:00AM'
UNION
SELECT 2, 'atitle1', '1/1/2010 10:03:00AM'
UNION
SELECT 3, 'title1', '1/1/2010 10:10:00AM'
UNION
SELECT 4, 'btitle1', '1/1/2010 10:04:00AM'
UNION
SELECT 10, 'title1', '1/1/2010 10:07:00AM'
UNION
SELECT 11, 'dtitle1', '1/1/2010 10:09:00AM'
UNION
SELECT 12, 'ctitle1', '1/1/2010 10:00:01AM'
UNION
SELECT 13, 'title1', '1/1/2010 10:10:00AM'

DECLARE @RankTypeID tinyint
--SET @RankTypeID = 0 -- rank
--SET @RankTypeID = 1 -- date start asc
SET @RankTypeID = 2 -- date start desc
--SET @RankTypeID = 3 -- title

SELECT 
    [Rank],
    [DateStart],
    [Title]
FROM
    @Temp
ORDER BY
    Case @RankTypeID
      When 0 Then [Rank]
      When 1 Then Cast([DateStart] As sql_variant)
      When 3 Then [Title]
      else null
    End,      
    Case @RankTypeID
      When 2 Then Cast([DateStart] As sql_variant)
    End DESC

3 回复 | 直到 14 年前

Denis Valeev 14 年前

试试这个

ORDER BY
    Case Parent.RankTypeID
      When 0 Then dbo.Folders.Rank
      When 1 Then dbo.Documents.DateStart
      When 3 Then Cast(dbo.Documents.Title as sql_variant)
      else null
    End,      
    case Parent.RankTypeID
      when 2 Then dbo.Documents.DateStart
    end desc

不,你不需要投任何东西。下面是测试数据的完整解决方案。

order by 
case @RankTypeID when 0 then [Rank] else null end,      
case @RankTypeID when 1 then [DateStart] else null end,
case @RankTypeID when 2 then [DateStart] else null end desc,
case @RankTypeID when 3 then [Title] else null end

gbn 14 年前

一种方法是将排序值和实际顺序按

SELECT
   col1, col2, ...
FROM
   (
   SELECT
      col1, col2, ...,
      ROW_NUMBER() OVER (ORDER BY Rank) AS RankASC,
      ROW_NUMBER() OVER (ORDER BY DateStart) AS DateStartASC,
      ROW_NUMBER() OVER (ORDER BY Title) AS TitleASC
   FROM
      MyTable
   ) foo
ORDER BY
    Case foo.RankTypeID
      When 0 Then foo.RankAsc
      When 1 Then foo.DateStartAsc
      When 2 Then -1 * foo.DateStartAsc
      When 3 Then foo.TitleAsc
      --else null needed?
    End

SELECT
   col1, col2, ...
FROM
   (
   SELECT
      col1, col2, ...,
      ROW_NUMBER() OVER (ORDER BY Rank) AS RankOrder,
      ROW_NUMBER() OVER (ORDER BY DateStart) AS DateStartOrder,
      ROW_NUMBER() OVER (ORDER BY Title) AS TitleOrder
   FROM
      MyTable
   ) foo
ORDER BY
    @SortOrder *
      Case foo.RankTypeID
        When 0 Then foo.RankOrder
        When 1 Then foo.DateStartOrder
        When 3 Then foo.TitleOrder
      End

LCJ 11 年前

下面给出了另一个完整的工作解决方案示例

--TEST DATA
DECLARE @MYTable TABLE (EmpID INT, EmpName VARCHAR(10) , JoinDate DATETIME)

INSERT INTO @MYTable VALUES (1,'E1','1/1/2001');
INSERT INTO @MYTable VALUES (2,'E2','2/2/2002');
INSERT INTO @MYTable VALUES (3,'E3','5/5/2001');

--INPUT Parameters
DECLARE @SortParam VARCHAR(MAX)
SET @SortParam = 'JoinDate'

DECLARE @SortDirection VARCHAR(MAX)
SET @SortDirection = 'DESC'


--@RankTypeID Variable
DECLARE @RankTypeID  INT

--EMPNAME
IF (@SortParam = 'EmpName' AND @SortDirection = 'ASC')
BEGIN 
    SET @RankTypeID = 1 
END

IF (@SortParam = 'EmpName' AND @SortDirection = 'DESC')
BEGIN
    SET @RankTypeID = -1    
END

--EmpID
IF (@SortParam = 'EmpID' AND @SortDirection = 'ASC')
BEGIN 
    SET @RankTypeID = 2 
END

IF (@SortParam = 'EmpID' AND @SortDirection = 'DESC')
BEGIN
    SET @RankTypeID = -2
END

--JoinDate
IF (@SortParam = 'JoinDate' AND @SortDirection = 'ASC')
BEGIN 
    SET @RankTypeID = 3 
END

IF (@SortParam = 'JoinDate' AND @SortDirection = 'DESC')
BEGIN
    SET @RankTypeID = -3
END


-- SELECT
SELECT * 
FROM @MYTable M
ORDER BY 
        CASE @RankTypeID WHEN 1 then EmpName ELSE null end ASC,
        CASE @RankTypeID WHEN -1 then EmpName ELSE null end DESC,

        CASE @RankTypeID WHEN 2 then [EmpID] else null end ASC ,
        CASE @RankTypeID WHEN -2 then [EmpID] else null end DESC ,

        CASE @RankTypeID WHEN 3 then JoinDate else null end  ASC,
        CASE @RankTypeID WHEN -3 then JoinDate else null end  DESC


--END