代码之家 › 专栏 › 技术社区 › priyanka.sarkar

如何按日、时字段排序?

tsql sql-server

priyanka.sarkar · 技术社区 · 7 年前

字符串值为 07052018080504623

它代表 MM DD YYYY HH MM SS MS

方法

MM = 07, DD = 05, YYYY=2018, HH = 08, MM=05 , SS= 04, MS = 623

现在我有一个表定义为

declare @t table (WorkRequestId varchar(100))
insert into @t values
('07052018080504623'),('07062018012756663'),('07062018020148130'),('07062018095201231'),
('07062018102203805'),('07062018103718059'),('07062018110304836'),('07062018115356135'),
('07062018120624983'),('07062018124035480'),('07062018080504623'),('07062018070504623')

select 
    *   
from @t

记录应按升序排序,以便

WorkRequestId

07052018080504623
07062018095201231
07062018102203805
07062018103718059
07062018110304836
07062018115356135
07062018120624983
07062018124035480
07062018012756663
07062018020148130

截止时间为前一天晚上8点至次日晚上19:59:59。在我们的例子中,05是前一天,06是第二天。

另外,在第二天凌晨1点到7点59分59秒之间没有交易发生。从第二天早上8点开始,一直持续到 19:59:59下午。

所以当我们遇到 07062018012756663, the DD=06 and HH = 01 . 意思是 13HRs (i.e. 1PM) of 6th . 同样适用于 07062018020148130 where DD=06 and HH = 02 (i.e. 2PM or 14Hrs).

但是 07062018095201231 where DD=06 and HH = 09 means 9AM of 6th.

这就是为什么

07062018095201231 comes before 07062018012756663 and 07062018020148130

订货时

到目前为止我的尝试(还不正确)

select 
    *       
    ,DY=SUBSTRING([WorkRequestId],3,2)
    ,HH = SUBSTRING([WorkRequestId],9,2)
    ,CurrentDY=CONVERT(varchar(2), getdate(), 103)
from @t

order by left([WorkRequestId],8) + 
    cast(iif( 
            SUBSTRING([WorkRequestId],3,2) = '6',--CONVERT(varchar(2), getdate(), 103), 
            iif(cast(SUBSTRING([WorkRequestId],9,2) as int) between 1 and 7,
            cast(SUBSTRING([WorkRequestId],9,2) as int)+12,SUBSTRING([WorkRequestId],9,2)),
            cast(SUBSTRING([WorkRequestId],9,2)as varchar(4)))as varchar(20))
    +right([WorkRequestId],7)

2 回复 | 直到 7 年前

sacse 7 年前

您可以尝试以下操作:

select 
    *
from @t 
order by left (WorkRequestId, 8) + (case when SUBSTRING(WorkRequestId, 9,2) between '01' and '07' then CAST(SUBSTRING(WorkRequestId, 9,2) + 12 AS CHAR(2)) else SUBSTRING(WorkRequestId, 9,2) end) +  SUBSTRING(WorkRequestId, 11,7)

Zohar Peled 7 年前

所以,我想您现在应该意识到,将日期存储为字符串(实际上,将任何内容存储为错误的数据类型)是一种不好的做法。
正确的解决方案是更改数据库结构以将该数据保存为 DateTime2 而不是一根绳子。但是,假设由于某种原因无法执行此操作,则可以通过将字符串值转换为 datetime2 ,如果小时在凌晨1点到8点之间,则添加12小时,并按该日期排序。
我以一种繁琐的方式写下了我的建议,因为我想展示流程的每一个部分——我使用了3个公共表表达式,不过如果可以在一个查询中完成的话——同样,这只是为了说明解决方案的每一步:

;WITH CTEDateParts AS -- break down the string to it's parts
(
    SELECT  WorkRequestId, 
            SUBSTRING(WorkRequestId, 5, 4) As Year,
            SUBSTRING(WorkRequestId, 1, 2) As Month,
            SUBSTRING(WorkRequestId, 3, 2) As Day,
            SUBSTRING(WorkRequestId, 9, 2) As Hour,
            SUBSTRING(WorkRequestId, 11, 2) As Minute,
            SUBSTRING(WorkRequestId, 13, 2) As Second,
            SUBSTRING(WorkRequestId, 15, 3) As Millisecond
    FROM @t
), CTEDates AS -- create datetime values from the string parts
(
    SELECT  WorkRequestId, 
            CAST(Year +'-'+ Month +'-'+ Day +'T'+ 
                 Hour +':'+ Minute +':'+ Second +'.'+ Millisecond As DateTime2(7)) As DateValue
    FROM CTEDateParts
), CTEFixedDates AS -- add 12 hours for hours between 1 and 8 a.m.
(
    SELECT  WorkRequestId, 
            DateValue,
            CASE WHEN DATEPART(HOUR, DateValue) >= 1 AND DATEPART(HOUR, DateValue) <= 8 THEN 
                DATEADD(Hour, 12, DateValue)
            ELSE
                DateValue
            END As FixedDate 
    FROM CTEDates
)

-- finally, select order by the FixedDate column
SELECT WorkRequestId
FROM CTEFixedDates
ORDER BY FixedDate

结果:

WorkRequestId
07052018080504623
07062018095201231
07062018102203805
07062018103718059
07062018110304836
07062018115356135
07062018120624983
07062018124035480
07062018012756663
07062018020148130
07062018070504623
07062018080504623