具有混合数据类型的 SQL Server 动态 ORDER BY:这是个好主意吗? [英] SQL Server dynamic ORDER BY with mixed datatypes: is this a good idea?

问题描述

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

I have stored procedure that returns results sorted dynamically. The parent folder (this is for content management) has a RankTypeID field that allows sorting by Rank (0), Start Date in ascending order (1), Start Date in descending order (2), and document title (3)

Rank 是一个整数，date 是 smalldatetime，title 是一个 nvarchar.

Rank is an integer, date is smalldatetime, and title is a nvarchar.

...

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、: 和 -

I set up the SortableDateStart as a computed column to take a DateStart smalldatetime column and convert it into a bigit for sorting. It takes an ISO8601 date (designed for xml usage, and also handy for sorting) and replaces the T, :, and -

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

这有点丑.有一个更好的方法吗?我也愿意接受更好的方法来处理这种动态排序.

This is kind of ugly. Is there a better way to do this? I'm also open to better ways of handling this dynamic sorting.

测试数据设置

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

推荐答案

尝试这样的事情

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

更新.

不，您不需要投射任何内容.这是您的测试数据的完整解决方案.

No, you don't need to cast anything. Here's a full solution for your test data.

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 

这篇关于具有混合数据类型的 SQL Server 动态 ORDER BY:这是个好主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！